Oracle经典解决之道汇总.docx
《Oracle经典解决之道汇总.docx》由会员分享，可在线阅读，更多相关《Oracle经典解决之道汇总.docx（45页珍藏版）》请在冰豆网上搜索。
Oracle经典解决之道汇总
Oracle经典解决之道汇总
oracle能否限制某个用户的访问的进程（/processes？
）数量？
事情是这样的：
我们几个项目都使用我机器上的同一个数据库，每个项目一个独立的用户用以区分不同表（一个项目一个数据库用户），但是由于连接池的bug，有时候就有用户的连接数超出设定的最大值一直达到数据库proccess的最大值。
所以我想能不能在数据库这边设定某个用户的最大访问进程数？
我在“例程”>“会话”里面可以看到所有用户的访问连接。
望高手出手指教。
使用PROFILE
先创建一个PROFILE
create profileses_connlimit
sessions_per_usern; n为最大连接数。
然后将该PROFILE付给需要限制的用户。
alteruserxxxprofileses_conn;
最后别忘了确认RESOURCE_LIMIT参数已设为TRUE。
如果没有，用
altersystemsetrecource_limit=TRUE;
OracleSQL
selectdistinct
p.spidunix_process,
s.terminal,
to_char（s.logon_time,'YYYY/MON/DDHH24:
MI'）Logon_Time,
s.username
fromv$processp,v$sessions
wherep.addr=s.paddrorderby2
常用监控SQL
1.监控事例的等待：
selectevent,sum（decode（wait_time,0,0,1））prev,sum（decode（wait_time,0,1,0））curr,count（*）
fromv$session_wait
groupbyeventorderby4;
2.回滚段的争用情况：
selectname,waits,gets,waits/getsratiofromv$rollstata,v$rollnamebwherea.usn=b.usn;
3.监控表空间的I/O比例：
selectdf.tablespace_namename,df.file_name"file",f.phyrdspyr,f.phyblkrdpbr,f.phywrtspyw,
f.phyblkwrtpbw
fromv$filestatf,dba_data_filesdf
wheref.file#=df.file_id
4.监空文件系统的I/O比例：
selectsubstr（a.file#,1,2）"#",substr（a.name,1,30）"name",a.status,a.bytes,
b.phyrds,b.phywrts
fromv$datafilea,v$filestatb
wherea.file#=b.file#
5.在某个用户下找所有的索引：
selectuser_indexes.table_name,user_indexes.index_name,uniqueness,column_name
fromuser_ind_columns,user_indexes
whereuser_ind_columns.index_name=user_indexes.index_name
anduser_ind_columns.table_name=user_indexes.table_name
orderbyuser_indexes.table_type,user_indexes.table_name,
user_indexes.index_name,column_position;
6.监控SGA的命中率
selecta.value+b.value"logical_reads",c.value"phys_reads",
round（100*（（a.value+b.value）-c.value）/（a.value+b.value））"BUFFERHITRATIO"
fromv$sysstata,v$sysstatb,v$sysstatc
wherea.statistic#=38andb.statistic#=39
andc.statistic#=40;
7.监控SGA中字典缓冲区的命中率
selectparameter,gets,Getmisses,getmisses/（gets+getmisses）*100"missratio",
（1-（sum（getmisses）/（sum（gets）+sum（getmisses））））*100"Hitratio"
fromv$rowcache
wheregets+getmisses<>0
groupbyparameter,gets,getmisses;
8.监控SGA中共享缓存区的命中率，应该小于1%
selectsum（pins）"TotalPins",sum（reloads）"TotalReloads",
sum（reloads）/sum（pins）*100libcache
fromv$librarycache;
selectsum（pinhits-reloads）/sum（pins）"hitradio",sum（reloads）/sum（pins）"reloadpercent"
fromv$librarycache;
9.显示所有数据库对象的类别和大小
selectcount（name）num_instances,type,sum（source_size）source_size,
sum（parsed_size）parsed_size,sum（code_size）code_size,sum（error_size）error_size,
sum（source_size）+sum（parsed_size）+sum（code_size）+sum（error_size）size_required
fromdba_object_size
groupbytypeorderby2;
10.监控SGA中重做日志缓存区的命中率，应该小于1%
SELECTname,gets,misses,immediate_gets,immediate_misses,
Decode（gets,0,0,misses/gets*100）ratio1,
Decode（immediate_gets+immediate_misses,0,0,
immediate_misses/（immediate_gets+immediate_misses）*100）ratio2
FROMv$latchWHEREnameIN（'redoallocation','redocopy'）;
11.监控内存和硬盘的排序比率，最好使它小于.10，增加sort_area_size
SELECTname,valueFROMv$sysstatWHEREnameIN（'sorts（memory）','sorts（disk）'）;
12.监控当前数据库谁在运行什么SQL语句
SELECTosuser,username,sql_textfromv$sessiona,v$sqltextb
wherea.sql_address=b.addressorderbyaddress,piece;
13.监控字典缓冲区
SELECT（SUM（PINS-RELOADS））/SUM（PINS）"LIBCACHE"FROMV$LIBRARYCACHE;
SELECT（SUM（GETS-GETMISSES-USAGE-FIXED））/SUM（GETS）"ROWCACHE"FROMV$ROWCACHE;
SELECTSUM（PINS）"EXECUTIONS",SUM（RELOADS）"CACHEMISSESWHILEEXECUTING"FROMV$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECTSUM（GETS）"DICTIONARYGETS",SUM（GETMISSES）"DICTIONARYCACHEGETMISSES"
FROMV$ROWCACHE
14.找ORACLE字符集
select*fromsys.props$wherename='NLS_CHARACTERSET';
15.监控MTS
selectbusy/（busy+idle）"sharedserversbusy"fromv$dispatcher;
此值大于0.5时，参数需加大
selectsum（wait）/sum（totalq）"dispatcherwaits"fromv$queuewheretype='dispatcher';
selectcount（*）fromv$dispatcher;
selectservers_highwaterfromv$mts;
servers_highwater接近mts_max_servers时，参数需加大
16.碎片程度
selecttablespace_name,count（tablespace_name）fromdba_free_spacegroupbytablespace_name
havingcount（tablespace_name）>10;
altertablespacenamecoalesce;
altertablenamedeallocateunused;
createorreplaceviewts_blocks_vas
selecttablespace_name,block_id,bytes,blocks,'freespace'segment_namefromdba_free_space
unionall
selecttablespace_name,block_id,bytes,blocks,segment_namefromdba_extents;
select*fromts_blocks_v;
selecttablespace_name,sum（bytes）,max（bytes）,count（block_id）fromdba_free_space
groupbytablespace_name;
查看碎片程度高的表
SELECTsegment_nametable_name,COUNT（*）extents
FROMdba_segmentsWHEREownerNOTIN（'SYS','SYSTEM'）GROUPBYsegment_name
HAVINGCOUNT（*）=（SELECTMAX（COUNT（*））FROMdba_segmentsGROUPBYsegment_name）;
17.表、索引的存储情况检查
selectsegment_name,sum（bytes）,count（*）ext_quanfromdba_extentswhere
tablespace_name='&tablespace_name'andsegment_type='TABLE'groupbytablespace_name,segment_name;
selectsegment_name,count（*）fromdba_extentswheresegment_type='INDEX'andowner='&owner'
groupbysegment_name;
18、找使用CPU多的用户session
12是cpuusedbythissession
selecta.sid,spid,status,substr（a.program,1,40）prog,a.terminal,osuser,value/60/100value
fromv$sessiona,v$processb,v$sesstatc
wherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;
20.监控log_buffer的使用情况：
（值最好小于1%，否则增加log_buffer的大小）
selectrbar.name,rbar.value,re.name,re.value,（rbar.value*100）/re.value||'%'"radio"
fromv$sysstatrbar,v$sysstatre
whererbar.name='redobufferallocationretries'
andre.name='redoentries';
19、查看运行过的SQL语句：
SELECTSQL_TEXT
FROMV$SQL
常用用户SQL
SQL语句：
表：
select*fromcat;
select*fromtab;
selecttable_namefromuser_tables;
视图：
selecttextfromuser_viewswhereview_name=upper（'&view_name'）;
索引：
selectindex_name,table_owner,table_name,tablespace_name,statusfromuser_indexesorderbytable_name;
触发器：
selecttrigger_name,trigger_type,table_owner,table_name,statusfromuser_triggers;
快照：
selectowner,name,master,table_name,last_refresh,nextfromuser_snapshotsorderbyowner,next;
同义词：
select*fromsyn;
序列：
select*fromseq;
数据库链路：
select*fromuser_db_links;
约束限制：
selectTABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS
fromuser_constraintsWHERETABLE_name=upper（'&TABLE_Name'）;
本用户读取其他用户对象的权限：
select*fromuser_tab_privs;
本用户所拥有的系统权限：
select*fromuser_sys_privs;
用户：
select*fromall_usersorderbyuser_id;
表空间剩余自由空间情况：
selecttablespace_name,sum（bytes）总字节数,max（bytes）,count（*）fromdba_free_spacegroupbytablespace_name；
数据字典：
selecttable_namefromdictorderbytable_name;
锁及资源信息：
select*fromv$lock;不包括DDL锁
数据库字符集：
selectname,value$fromprops$wherename='NLS_CHARACTERSET';
inin.ora参数：
selectname,valuefromv$parameterorderbyname;
SQL共享池：
selectsql_textfromv$sqlarea;
数据库：
select*fromv$database
控制文件：
select*fromV$controlfile;
重做日志文件信息：
select*fromV$logfile;
来自控制文件中的日志文件信息：
select*fromV$log;
来自控制文件中的数据文件信息：
select*fromV$datafile;
NLS参数当前值：
select*fromV$nls_parameters;
ORACLE版本信息：
select*fromv$version;
描述后台进程：
select*fromv$bgprocess;
查看版本信息：
select*fromproduct_component_version;
查询表结构
selectsubstr（table_name,1,20）tabname,
substr（column_name,1,20）column_name,
rtrim（data_type）||'（'||data_length||'）'fromsystem.dba_tab_columns
whereowner='username'
表空间使用状态
selecta.file_id"FileNo",a.tablespace_name"Tablespace_name",
round（a.bytes/1024/1024,4）"TotalMB",
round（（a.bytes-sum（nvl（b.bytes,0）））/1024/1024,4）"UsedMB",
round（sum（nvl（b.bytes,0））/1024/1024,4）"FreeMB",
round（sum（nvl（b.bytes,0））/a.bytes*100,4） "%Free"
fromdba_data_filesa,dba_free_spaceb
wherea.file_id=b.file_id（+）
groupbya.tablespace_name,
a.file_id,a.bytesorderbya.tablespace_name
查询某个模式下面数据不为空的表
declare
CursorcisselectTNAMEfromtab;
vCountNumber;
table_nmVarchar2（100）;
sqvarchar2（300）;
begin
forrincloop
table_nm:
=r.TNAME;
sq:
='select count（*） from'||table_nm;
executeimmediatesqintovCount;
ifvCount>0then
dbms_output.put_line（r.tname）;
endif;
endloop;
end;_
客户端主机信息
SELECT
SYS_CONTEXT（'USERENV','TERMINAL'）TERMINAL,
SYS_CONTEXT（'USERENV','HOST'）HOST,
SYS_CONTEXT（'USERENV','OS_USER'）OS_USER,
SYS_CONTEXT（'USERENV','IP_ADDRESS'）IP_ADDRESS
FROMDUAL
安装Oracle后，经常使用的修改表空间的SQL代码
配置：
WindowsNT4.0中文版
5块10.2GBSCSI硬盘
分：
C:
盘、D:
盘、E:
盘、F:
盘、G:
盘
Oracle8.0.4forWindowsNT
NT安装在C:
\WINNT，Oracle安装在C:
\ORANT
目标：
因系统的回滚段太小，现打算生成新的回滚段，
建立大的、新的表空间（数据表空间、索引表空间、回滚表空间、临时表空间、）
建两个数据表空间、两个索引表空间，这样建的目的是根据实际应用，
如：
现有10个应用用户，每个用户是一个独立子系统（如：
商业进销存MIS系统中的财务、收款、库存、人事、总经理等）
尤其大型商场中收款机众多，同时访问进程很多，经常达到50-100个进程同时访问，
这样，通过建立多个用户表空间、索引表空间，把各个用户分别建在不同的表空间里（多个用户表空间放在不同的物理磁盘上），
减少了用户之间的I/O竞争、读写数据与写读索引的竞争（用户表空间、索引表空间也分别放在不同的物理磁盘上）
规划：
C:
盘、NT系统，Oracle系统
D:
盘、数据表空间1（3GB、自动扩展）、回滚表空间1（1GB、自动扩展）
E:
盘、数据表空间2（3GB、自动扩展）、回滚表空间2（1GB、自动扩展）
F:
盘、索引表空间1（2GB、自动扩展）、临时表空间1（0.5GB、不自动扩展）
G:
盘、索引表空间2（2GB、自动扩展）、临时表空间2（0.5GB、不自动扩展）
注：
这只是一个简单的规划，实际规划要依系统需求来定，尽量减少I/O竞争
实现：
1、首先查看系统有哪些回滚段及其状态。
SQL>colownerformata20
SQL>colstatusformata10
SQL>colsegment_nameformata20
SQL>coltablespace_nameformata20
SQL>SELECTOWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM（BYTES）/1024/1024M
2 FROMDBA_SEGMENTS
3 WHERESEGMENT_TYPE='ROLLBACK'
4 GROUPBYOWNER,SEGMENT_NAME,TABLESPACE_NAME
5 /
OWNER SEGMENT_NAME TABLESPACE_NAME M
---------------------------------------------------------------------
SYS RB1 ROLLBAC
升级会员 