oracle DBA 常用监控脚本
一、数据库构架体系
1、表空间的监控是一个重要的任务，我们必须时刻关心表空间的设置，是否满足现在应用的需求，以下的语句可以查询到表空间的详细信息
SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,
MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,
CONTENTS,LOGGING,
EXTENT_MANAGEMENT,--Columnsnotavailableinv8.0.x
ALLOCATION_TYPE,--Removethesecolumnsifrunning
PLUGGED_IN,--againstav8.0.xdatabase
SEGMENT_SPACE_MANAGEMENT--useonlyinv9.2.xorlater
FROMDBA_TABLESPACES
ORDER BY TABLESPACE_NAME;2、对于某些数据文件没有设置为自动扩展的表空间来说，如果表空间满了，就将意味着数据库可能会因为没有空间而停止下来。监控表空间，最主要的就是监控剩余空间的大小或者是使用率。以下是监控表空间使用率与剩余空间大小的语句
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)"USED_RATE(%)",FREE_SPACE"FREE_SPACE(M)"
FROM
(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKS
FROMDBA_DATA_FILES
GROUPBYTABLESPACE_NAME)D,
(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)FREE_SPACE
FROMDBA_FREE_SPACE
GROUPBYTABLESPACE_NAME)F
WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)
UNIONALL--ifhavetempfile
SELECTD.TABLESPACE_NAME,SPACE"SUM_SPACE(M)",BLOCKSSUM_BLOCKS,
USED_SPACE"USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2)"USED_RATE(%)",
NVL(FREE_SPACE,0)"FREE_SPACE(M)"
FROM
(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKS
FROMDBA_TEMP_FILES
GROUPBYTABLESPACE_NAME)D,
(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2)USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2)FREE_SPACE
FROMV$TEMP_SPACE_HEADER
GROUPBYTABLESPACE_NAME)F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)3、除了监控表空间的剩余空间，有时候我们也有必要了解一下该表空间是否具有自动扩展空间的能力，虽然我们建议在生产系统中预先分配空间。以下语句将完成这一功能
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROMDBA_TABLESPACEST,
DBA_DATA_FILESD
WHERET.TABLESPACE_NAME=D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME4、我相信使用字典管理的表空间的也不少吧，因为字典管理的表空间中，每个表的下一个区间的大小是不可以预料的，所以我们必须监控那些表在字典管理的表空间中的下一个区间的分配将会引起性能问题或由于是非扩展的表空间而导致系统停止。以下语句检查那些表的扩展将引起表空间的扩展。
SELECT A.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAME
FROMALL_TABLESA,
(SELECTTABLESPACE_NAME,MAX(BYTES)BIG_CHUNK
FROMDBA_FREE_SPACE
GROUPBYTABLESPACE_NAME)F
WHEREF.TABLESPACE_NAME=A.TABLESPACE_NAME
AND A.NEXT_EXTENT > F.BIG_CHUNK5、段的占用空间与区间数也是很需要注意的一个问题，如果一个段的占用空间太大，或者跨越太多的区间（在字典管理的表空间中，将有严重的性能影响），如果段没有可以再分配的区间，将导致数据库错误。所以，段的大小与区间监控也是一个很重要的工作
SELECT S.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME,
ROUND(BYTES/(1024*1024),2)"USED_SPACE(M)",
EXTENTSUSED_EXTENTS,S.MAX_EXTENTS,S.BLOCKSALLOCATED_BLOCKS,
S.BLOCKSUSED_BOLCKS,S.PCT_INCREASE,S.NEXT_EXTENT/1024"NEXT_EXTENT(K)"
FROMDBA_SEGMENTSS
WHERES.OWNERNOTIN('SYS','SYSTEM')
ORDER BY Used_Extents DESC6、对象的空间分配与空间利用情况，除了从各个方面的分析，如分析表，查询rowid等方法外，其实oracle提供了一个查询空间的包dbms_space，如果我们稍封装一下，将是非常好用的一个东西。
CREATEORREPLACEPROCEDUREshow_space
(p_segnameinvarchar2,
p_typeinvarchar2default'TABLE',
p_ownerinvarchar2defaultuser)
AS
v_segnamevarchar2(100);
v_typevarchar2(10);
l_free_blksnumber;
l_total_blocksnumber;
l_total_bytesnumber;
l_unused_blocksnumber;
l_unused_bytesnumber;
l_LastUsedExtFileIdnumber;
l_LastUsedExtBlockIdnumber;
l_LAST_USED_BLOCKnumber;
PROCEDUREp(p_labelinvarchar2,p_numinnumber)
IS
BEGIN
dbms_output.put_line(rpad(p_label,40,'.')||p_num);
END;
BEGIN
v_segname:=upper(p_segname);
v_type:=p_type;
if(p_type='i'orp_type='I')then
v_type:='INDEX';
endif;
if(p_type='t'orp_type='T')then
v_type:='TABLE';
endif;
if(p_type='c'orp_type='C')then
v_type:='CLUSTER';
endif;
--以下部分不能用于ASSM
dbms_space.free_blocks
(segment_owner=>p_owner,
segment_name=>v_segname,
segment_type=>v_type,
freelist_group_id=>0,
free_blks=>l_free_blks);
--以上部分不能用于ASSM
dbms_space.unused_space
(segment_owner=>p_owner,
segment_name=>v_segname,
segment_type=>v_type,
total_blocks=>l_total_blocks,
total_bytes=>l_total_bytes,
unused_blocks=>l_unused_blocks,
unused_bytes=>l_unused_bytes,
LAST_USED_EXTENT_FILE_ID=>l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID=>l_LastUsedExtBlockId,
LAST_USED_BLOCK=>l_LAST_USED_BLOCK);
--显示结果
p('FreeBlocks',l_free_blks);
p('TotalBlocks',l_total_blocks);
p('TotalBytes',l_total_bytes);
p('UnusedBlocks',l_unused_blocks);
p('UnusedBytes',l_unused_bytes);
p('LastUsedExtFileId',l_LastUsedExtFileId);
p('LastUsedExtBlockId',l_LastUsedExtBlockId);
p('LastUsedBlock',l_LAST_USED_BLOCK);
END;执行结果将如下所示
SQL>setserveroutputon;
SQL>execshow_space('test');
FreeBlocks.............................1
TotalBlocks............................8
TotalBytes.............................65536
UnusedBlocks...........................6
UnusedBytes............................49152
LastUsedExtFileId....................1
LastUsedExtBlockId...................48521
LastUsedBlock.........................2
PL/SQL procedure successfully completed8、数据库的索引如果有比较频繁的Delete操作，将可能导致索引产生很多碎片，所以，在有的时候，需要对所有的索引重新REBUILD，以便合并索引块，减少碎片，提高查询速度。
SQL>setheadingoff
SQL>setfeedbackoff
SQL>spoold:index.sql
SQL>SELECT'alterindex'||index_name||'rebuild'
||'tablespaceINDEXESstorage(initial256Knext256Kpctincrease0);'
FROMall_indexes
WHERE(tablespace_name!='INDEXES'
ORnext_extent!=(256*1024)
)
ANDwner=USER
SQL>spool off这个时候，我们打开spool出来的文件，就可以直接运行了。
9、表的主键是必要的，没有主键的表可以说是不符合设计规范的，所以我们需要监控表是否有主键
SELECT table_name
FROMall_tables
WHEREwner=USER
MINUS
SELECTtable_name
FROMall_constraints
WHEREwner=USER
AND constraint_type = 'P'二、性能监控
1、数据缓冲区的命中率已经不是性能调整中的主要问题了，但是，过低的命中率肯定是不可以的，在任何情况下，我们必须保证有一个大的data buffer和一个高的命中率。这个语句可以获得整体的数据缓冲命中率，越高越好
SELECTa.VALUE+b.VALUElogical_reads,
c.VALUEphys_reads,
round(100*(1-c.value/(a.value+b.value)),4)hit_ratio
FROMv$sysstata,v$sysstatb,v$sysstatc
WHEREa.NAME='dbblockgets'
ANDb.NAME='consistentgets'
AND c.NAME='physical reads'2、库缓冲说明了SQL语句的重载率，当然，一个SQL语句应当被执行的越多越好，如果重载率比较高，就考虑增加共享池大小或者是提高Bind变量的使用以下语句查询了Sql语句的重载率，越低越好
SELECT SUM(pins) total_pins,SUM(reloads) total_reloads,
SUM(reloads)/SUM(pins)*100libcache_reload_ratio
FROM v$librarycache3、用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
可以通过alter system kill session ‘sid,serial#’来杀掉会话SELECT/*+rule*/s.username,
decode(l.type,'TM','TABLELOCK',
'TX','ROWLOCK',
NULL)LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROMv$sessions,v$lockl,dba_objectso
WHEREl.sid=s.sid
ANDl.id1=o.object_id(+)
AND s.username is NOT NULL4、锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待以下的语句可以查询到谁锁了表，而谁在等待。
SELECT/*+rule*/lpad('',decode(l.xidusn,0,3,0))||l.oracle_usernameUser_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROMv$locked_objectl,dba_objectso,v$sessions
WHEREl.object_id=o.object_id
ANDl.session_id=s.sid
ORDER BY o.object_id,xidusn DESC以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
5、如果发生了事务或锁，想知道哪些回滚段正在被使用吗？其实通过事务表，我们可以详细的查询到事务与回滚段之间的关系。同时，如果关联会话表，我们则可以知道是哪个会话发动了这个事务。
SELECTs.USERNAME,s.SID,s.SERIAL#,t.UBAFIL"UBAfilenum",
t.UBABLK"UBABlocknumber",t.USED_UBLK"NumberosundoBlocksUsed",
t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSNRollID,r.NAMERollName
FROMv$sessions,v$transactiont,v$rollnamer
WHEREs.SADDR=t.SES_ADDR
AND t.XIDUSN=r.usn7、如果利用会话跟踪或者是想查看某个会话的跟踪文件，那么查询到OS上的进程或线程号是非常重要的，因为文件的令名中，就包含这个信息，以下的语句可以查询到进程或线程号，由此就可以找到对应的文件。
SELECTp1.value||''||p2.value||'_ora_'||p.spidfilename
FROM
v$processp,
v$sessions,
v$parameterp1,
v$parameterp2
WHEREp1.name='user_dump_dest'
ANDp2.name='db_name'
ANDp.addr=s.paddr
AND s.audsid = USERENV ('SESSIONID');8、在ORACLE 9i中，可以监控索引的使用，如果没有使用到的索引，完全可以删除掉，减少DML操作时的操作。以下就是开始索引监控与停止索引监控的脚本
setheadingoff
setechooff
setfeedbackoff
setpages10000
spool start_index_monitor.sqlSELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
FROMdba_indexes
WHERE wner = USER;spool off
setheadingon
setechoon
setfeedbackon
------------------------------------------------
setheadingoff
setechooff
setfeedbackoff
setpages10000
spool stop_index_monitor.sqlSELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
FROMdba_indexes
WHERE wner = USER;spool off
setheadingon
setechoon
set feedback on如果需要监控更多的用户，可以将owner=User改写成别的
监控结果在视图v$object_usage中查询
感谢fenng，他提供了一个更新版的show_space脚本CREATE OR REPLACE PROCEDURE show_space
(p_segnameINVARCHAR2,
p_ownerINVARCHAR2DEFAULTUSER,
p_typeINVARCHAR2DEFAULT'TABLE',
p_partitionINVARCHAR2DEFAULTNULL)
--ThisprocedureusesAUTHIDCURRENTUSERsoitcanqueryDBA_*
--viewsusingprivilegesfromaROLEandsoitcanbeinstalled
--onceperdatabase,insteadofonceperuserwhowantedtouseit.
AUTHIDCURRENT_USER
as
l_free_blksnumber;
l_total_blocksnumber;
l_total_bytesnumber;
l_unused_blocksnumber;
l_unused_bytesnumber;
l_LastUsedExtFileIdnumber;
l_LastUsedExtBlockIdnumber;
l_LAST_USED_BLOCKnumber;
l_segment_space_mgmtvarchar2(255);
l_unformatted_blocksnumber;
l_unformatted_bytesnumber;
l_fs1_blocksnumber;l_fs1_bytesnumber;
l_fs2_blocksnumber;l_fs2_bytesnumber;
l_fs3_blocksnumber;l_fs3_bytesnumber;
l_fs4_blocksnumber;l_fs4_bytesnumber;
l_full_blocks number; l_full_bytes number;-- Inline procedure to print out numbers nicely formatted
--withasimplelabel.
PROCEDUREp(p_labelinvarchar2,p_numinnumber)
IS
BEGIN
dbms_output.put_line(rpad(p_label,40,'.')||
to_char(p_num,'999,999,999,999'));
END;
BEGIN
--Thisqueryisexecuteddynamicallyinordertoallowthisprocedure
--tobecreatedbyauserwhohasaccesstoDBA_SEGMENTS/TABLESPACES
--viaaroleasiscustomary.
--NOTE:atruntime,theinvokerMUSThaveaccesstothesetwo
--views!
--ThisquerydeterminesiftheobjectisanASSMobjectornot.
BEGIN
EXECUTEIMMEDIATE
'selectts.segment_space_management
FROMdba_segmentsseg,dba_tablespacests
WHEREseg.segment_name=:p_segname
AND(:p_partitionisnullor
seg.partition_name=:p_partition)
ANDseg.owner=:p_owner
ANDseg.tablespace_name=ts.tablespace_name'
INTOl_segment_space_mgmt
USINGp_segname,p_partition,p_partition,p_owner;
EXCEPTION
WHENtoo_many_rowsTHEN
dbms_output.put_line
('Thismustbeapartitionedtable,usep_partition=>');
RETURN;
END;-- If the object is in an ASSM tablespace, we must use this API
--calltogetspaceinformation;elseweusetheFREE_BLOCKS
--APIfortheusermanagedsegments.
IFl_segment_space_mgmt='AUTO'
THEN
dbms_space.space_usage
(p_owner,p_segname,p_type,l_unformatted_blocks,
l_unformatted_bytes,l_fs1_blocks,l_fs1_bytes,
l_fs2_blocks,l_fs2_bytes,l_fs3_blocks,l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);p( 'Unformatted Blocks ', l_unformatted_blocks );
p('FS1Blocks(0-25)',l_fs1_blocks);
p('FS2Blocks(25-50)',l_fs2_blocks);
p('FS3Blocks(50-75)',l_fs3_blocks);
p('FS4Blocks(75-100)',l_fs4_blocks);
p('FullBlocks',l_full_blocks);
ELSE
dbms_space.free_blocks(
segment_owner=>p_owner,
segment_name=>p_segname,
segment_type=>p_type,
freelist_group_id=>0,
free_blks => l_free_blks);p( 'Free Blocks', l_free_blks );END IF;
-- And then the unused space API call to get the rest of the
--information.
dbms_space.unused_space
(segment_owner=>p_owner,
segment_name=>p_segname,
segment_type=>p_type,
partition_name=>p_partition,
total_blocks=>l_total_blocks,
total_bytes=>l_total_bytes,
unused_blocks=>l_unused_blocks,
unused_bytes=>l_unused_bytes,
LAST_USED_EXTENT_FILE_ID=>l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID=>l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );p( 'Total Blocks', l_total_blocks );
p('TotalBytes',l_total_bytes);
p('TotalMBytes',trunc(l_total_bytes/1024/1024));
p('UnusedBlocks',l_unused_blocks);
p('UnusedBytes',l_unused_bytes);
p('LastUsedExtFileId',l_LastUsedExtFileId);
p('LastUsedExtBlockId',l_LastUsedExtBlockId);
p('LastUsedBlock',l_LAST_USED_BLOCK);
END;隐含参数：
selecta.ksppinm"parameter",a.ksppdesc"descriptoin"
fromx$ksppia,x$ksppcvb,x$ksppsvc
where a.indx=b.indx and a.indx=c.indx and a.ksppinm like '/_%' escape '/';Check OS process id from Oracle sid
select spid from v$processwhere addr in ( select paddr from v$session where sid=[$sid) ]
Check Oracle sid from OS process id
selectsidfromv$session
where paddr in ( select addr from v$process where spid=[$pid) ]Check current SQL in a session
selectSQL_TEXTfromV$SQLTEXT
whereHASH_VALUE=
(selectSQL_HASH_VALUEfromv$session
wheresid=&sid)
order by PIECEChecking v$session_wait
select*fromv$session_wait
whereeventnotlike'rdbms%'
andeventnotlike'SQL*N%'
and event not like '%timer';Dictionary Cache Hits
SELECTsum(getmisses)/sum(gets)FROMv$rowcache;
/*It should be < 15%, otherwise Add share_pool_size*/Check DB object name from file id and block#
selectowner,segment_name,segment_type
fromdba_extents
where file_id = [$fno and &dno between block_id and block_id + blocks – 1 ]#寻找hotblock
select/*+ordered*/
e.owner||'.'||e.segment_namesegment_name,
e.extent_idextent#,
x.dbablk-e.block_id+1block#,
x.tch,
l.child#
from
sys.v$latch_childrenl,
sys.x$bhx,
sys.dba_extentse
where
l.name='cachebufferschains'and
l.sleeps>&sleep_countand
x.hladdr=l.addrand
e.file_id=x.file#and
x.dbablk between e.block_id and e.block_id + e.blocks - 1;#找出每个文件上的等待事件select df.name,kf.count from v$datafile df,x$kcbfwait kf where (kf.indx+1)=df.file#;
#找出引起等待事件的SQL语句.select sql_text from v$sqlarea a,v$session b,v$session_wait c where a.address=b.sql_address and b.sid=c.sid and c.event=[$ll]
#监控共享池中哪个对象引起了大的内存分配SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;
判断你是从pfile启动还是spfile启动的简单方法！！！
判断你是从pfile启动还是spfile启动的简单方法！！！
select decode(count(*), 1, 'spfile', 'pfile' )
fromv$spparameter
whererownum=1
andisspecified='TRUE'
/DECODE
------
spfile
ORACLE常用技巧和脚本ORACLE常用技巧和脚本
1.如何查看ORACLE的隐含参数？
ORACLE的显式参数，除了在INIT.ORA文件中定义的外，在svrmgrl中用"showparameter*"，可以显示。但ORACLE还有一些参数是以“_”，开头的。如我们非常熟悉的“_offline_rollback_segments”等。
这些参数可在sys.x$ksppi表中查出。
语句：“select ksppinm from x$ksppi where substr(ksppinm,1,1)='_'; ”2.如何查看安装了哪些ORACLE组件？
进入${ORACLE_HOME}/orainst/，运行./inspdver，显示安装组件和版本号。
3.如何查看ORACLE所占用共享内存的大小？
可用UNIX命令“ipcs”查看共享内存的起始地址、信号量、消息队列。
在svrmgrl下，用“oradebugipc”，可看出ORACLE占用共享内存的分段和大小。
example:
SVRMGR>oradebugipc
--------------Sharedmemory--------------
SegIdAddressSize
11537fe000784
1154800000419430400
1155 19800000 671088644.如何查看当前SQL*PLUS用户的sid和serial#?
在SQL*PLUS下，运行：
“selectsid,serial#,statusfromv$session
whereaudsid=userenv('sessionid');”
5.如何查看当前数据库的字符集？
在SQL*PLUS下，运行：
“selectuserenv('language')fromdual;”
或：“selectuserenv('lang')fromdual;”
6.如何查看数据库中某用户，正在运行什么SQL语句？
根据MACHINE、USERNAME或SID、SERIAL#，连接表V$SESSION和V$SQLTEXT，可查出。
SQL*PLUS语句：
“SELECTSQL_TEXTFROMV$SQL_TEXTT,V$SESSIONSWHERET.ADDRESS=S.SQL_ADDRESS
ANDT.HASH_VALUE=S.SQL_HASH_VALUE
ANDS.MACHINE='XXXXX'ORUSERNAME='XXXXX'--查看某主机名，或用户名
/”
7.如何删除表中的重复记录？
例句：
DELETE
FROMtable_namea
WHERErowid>(SELECTmin(rowid)
FROMtable_nameb
WHEREb.pk_column_1=a.pk_column_1
and b.pk_column_2 = a.pk_column_2 );8.手工临时强制改变服务器字符集
以sys或system登录系统，sql*plus运行：“createdatabasecharactersetus7ascii;".
有以下错误提示：
*createdatabasecharactersetUS7ASCII
ERRORatline1:
ORA-01031:insufficientprivileges
实际上，看v$nls_parameters，字符集已更改成功。但重启数据库后，数据库字符集又变回原来的了。
该命令可用于临时的不同字符集服务器之间数据倒换之用。
9.怎样查询每个instance分配的PCM锁的数目
用以下命令：
selectcount(*)"NumberofhashedPCMlocks"fromv$lock_elementwherebitand(flags,4)<>0
/
selectcount(*)"NumberoffinegrainPCMlocks"fromv$lock_element
wherebitand(flags,4)=0
/
10.怎么判断当前正在使用何种SQL优化方式？
用explainplan产生EXPLAINPLAN，检查PLAN_TABLE中ID=0的POSITION列的值。
e.g.
selectdecode(nvl(position,-1),-1,'RBO',1,'CBO')fromplan_tablewhereid=0
/
11.做EXPORT时，能否将DUMP文件分成多个？
ORACLE8I中EXP增加了一个参数FILESIZE，可将一个文件分成多个：
EXPSCOTT/TIGERFILE=（ORDER_1.DMP,ORDER_2.DMP,ORDER_3.DMP)FILESIZE=1GTABLES=ORDER；
其他版本的ORACLE在UNIX下可利用管道和split分割：
mknodpipep
split-b2048mpipeorder&#将文件分割成，每个2GB大小的，以order为前缀的文件：
#orderaa,orderab,orderac,...并将该进程放在后台。
EXPSCOTT/TIGERFILE=pipetables=order
户如何有效地利用数据字典用户如何有效地利用数据字典
ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库的产生而产生, 随着数据库的变化而变化,体现为sys用户下的一些表和视图。数据字典名称是大写的英文字符。
数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。我们不能手工修改数据字典里的信息。
很多时候，一般的ORACLE用户不知道如何有效地利用它。
dictionary 全部数据字典表的名称和解释，它有一个同义词dictdict_column 全部数据字典表里字段名称和解释
如果我们想查询跟索引有关的数据字典时，可以用下面这条SQL语句:
SQL>select * from dictionary where instr(comments,'index')>0;
如果我们想知道user_indexes表各字段名称的详细含义，可以用下面这条SQL语句:
SQL>select column_name,comments from dict_columns where table_name='USER_INDEXES';
依此类推，就可以轻松知道数据字典的详细名称和解释，不用查看ORACLE的其它文档资料了。
下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。一、用户
查看当前用户的缺省表空间SQL>select username,default_tablespace from user_users;
查看当前用户的角色SQL>select * from user_role_privs;
查看当前用户的系统权限和表级权限
SQL>select*fromuser_sys_privs;
SQL>select * from user_tab_privs;二、表
查看用户下所有的表SQL>select * from user_tables;
查看名称包含log字符的表
SQL>selectobject_name,object_idfromuser_objects
where instr(object_name,'LOG')>0;查看某表的创建时间SQL>select object_name,created from user_objects where object_name=upper('&table_name');
查看某表的大小
SQL>selectsum(bytes)/(1024*1024)as"size(M)"fromuser_segments
where segment_name=upper('&table_name');查看放在ORACLE的内存区里的表SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;
三、索引
查看索引个数和类别SQL>select index_name,index_type,table_name from user_indexes order by table_name;
查看索引被索引的字段SQL>select * from user_ind_columns where index_name=upper('&index_name');
查看索引的大小
SQL>selectsum(bytes)/(1024*1024)as"size(M)"fromuser_segments
where segment_name=upper('&index_name');四、序列号
查看序列号，last_number是当前值SQL>select * from user_sequences;
五、视图
查看视图的名称SQL>select view_name from user_views;
查看创建视图的select语句
SQL>setview_name,text_lengthfromuser_views;
SQL>setlong2000;说明：可以根据视图的text_length值设定setlong的大小
SQL>select text from user_views where view_name=upper('&view_name');六、同义词
查看同义词的名称SQL>select * from user_synonyms;
七、约束条件
查看某表的约束条件
SQL>selectconstraint_name,constraint_type,search_condition,r_constraint_name
from user_constraints where table_name = upper('&table_name');SQL>select c.constraint_name,c.constraint_type,cc.column_name
fromuser_constraintsc,user_cons_columnscc
wherec.owner=upper('&table_owner')andc.table_name=upper('&table_name')
andc.owner=cc.ownerandc.constraint_name=cc.constraint_name
order by cc.position;八、存储函数和过程
查看函数和过程的状态
SQL>selectobject_name,statusfromuser_objectswhereobject_type='FUNCTION';
SQL>select object_name,status from user_objects where object_type='PROCEDURE';查看函数和过程的源代码SQL>select text from all_source where wner=user and name=upper('&plsql_name');
九、触发器
查看触发器
set long 50000;
setheadingoff;
set pagesize 2000;select
'createorreplacetrigger"'||
trigger_name||'"'||chr(10)||
decode(substr(trigger_type,1,1),
'A','AFTER','B','BEFORE','I','INSTEADOF')||
chr(10)||
triggering_event||chr(10)||
'ON"'||table_owner||'"."'||
table_name||'"'||chr(10)||
decode(instr(trigger_type,'EACHROW'),0,null,
'FOREACHROW')||chr(10),
trigger_body
from user_triggers;--分析数据库性能的SQL
--用于查看哪些实例的哪些操作使用了大量的临时段
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_typeOPERATION,trunc(EXPECTED_SIZE/1024)ESIZE,
trunc(ACTUAL_MEM_USED/1024)MEM,trunc(MAX_MEM_USED/1024)"MAXMEM",
NUMBER_PASSESPASS,trunc(TEMPSEG_SIZE/1024)TSIZE
FROMV$SQL_WORKAREA_ACTIVE
ORDERBY1,2;
---查询有热块查询的SQL语句
selecthash_value
fromv$sqltexta,
(selectdistincta.owner,a.segment_name,a.segment_typefrom
dba_extentsa,
(selectdbarfil,dbablk
from(selectdbarfil,dbablk
fromx$bhorderbytchdesc)whererownum<11)b
wherea.RELATIVE_FNO=b.dbarfil
anda.BLOCK_ID<=b.dbablkanda.block_id+a.blocks>b.dbablk)b
wherea.sql_textlike'%'||b.segment_name||'%'andb.segment_type='TABLE'
orderbya.hash_value,a.address,a.piece;
--全表扫描
selectopname,target,b.num_rows,b.tablespace_name,count(target)fromv$session_longopsa,all_all_tablesb
wherea.TARGET=b.owner||'.'||b.table_name
havingcount(target)>10groupbyopname,target,b.num_rows,b.tablespace_name
--查看磁盘排序和缓存排序次数
selectto_char(sn.snap_time,'yyyy-mm-ddhh24')time_,
avg(newmen.value-oldmen.value)sorts_memeory,
avg(newdsk.value-olddsk.value)disk_sort
fromstats$sysstatoldmen,
stats$sysstatnewmen,
stats$sysstatnewdsk,
stats$sysstatolddsk,
stats$snapshotsn
wherenewdsk.snap_id=sn.snap_id
andolddsk.snap_id=sn.snap_id-1
andnewmen.snap_id=sn.snap_id
andnewdsk.snap_id=sn.snap_id-1
andoldmen.name='sorts(memory)'
andnewmen.name='sorts(memory)'
andolddsk.name='sorts(disk)'
andnewdsk.name='sorts(disk)'
groupbyto_char(sn.snap_time,'yyyy-mm-ddhh24')
--执行最慢的前10个SQL？？？
select*from(
select
to_char(snap_time,'ddMonHH24:mi:ss')mydate,
executionsexec,
loadsloads,
parse_callsparse,
disk_readsreads,
buffer_getsgets,
rows_processedrows_proc,
sortssorts,
sql_text,
hash_value
from
perfstat.stats$sql_summarysql,
perfstat.stats$snapshotsn
where
sql.snap_id>
(selectmin(snap_id)min_snap
fromstats$snapshotwheresnap_time>sysdate-$days_back)
and
sql.snap_id=sn.snap_id
orderby$sortskeydesc)ttwhererownum<11;
--SQL缓存池的命中率查询（pinhitratio，gethitratio应该大于90%以上）select namespace,gethitratio,pinhitratio,reloads,invalidations
fromv$librarycache
where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER')--数据库的常规参数我就不说了，除了V$parameter中的常规参数外，ORACLE还有大量的隐含参数，下面的语句就可以查询到数据库的所有隐含参数以及其值与参数的描述。
SELECTNAME
,VALUE
,decode(isdefault,'TRUE','Y','N')as"Default"
,decode(ISEM,'TRUE','Y','N')asSesMod
,decode(ISYM,'IMMEDIATE','I',
'DEFERRED','D',
'FALSE','N')asSysMod
,decode(IMOD,'MODIFIED','U',
'SYS_MODIFIED','S','N')asModified
,decode(IADJ,'TRUE','Y','N')asAdjusted
,description
FROM(--GV$SYSTEM_PARAMETER
SELECTx.inst_idasinstance
,x.indx+1
,ksppinmasNAME
,ksppity
,ksppstvlasVALUE
,ksppstdfasisdefault
,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE')asISEM
,decode(bitand(ksppiflg/65536,3),
1,'IMMEDIATE',2,'DEFERRED','FALSE')asISYM
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE')asIMOD
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE')asIADJ
,ksppdescasDESCRIPTION
FROMx$ksppix
,x$ksppsvy
WHEREx.indx=y.indx
ANDsubstr(ksppinm,1,1)='_'
ANDx.inst_id=USERENV('Instance')
)
ORDERBYNAME
--想知道现在哪个用户正在利用临时段吗？这个语句将告诉你哪个用户正在利用临时段。SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username,a.osuser,a.status,c.sql_text
FROMv$sessiona,v$sort_usageb,v$sqlc
WHEREa.saddr=b.session_addr
ANDa.sql_address=c.address(+)
ORDERBYb.tablespace,b.segfile#,b.segblk#,b.blocks;
--查看磁盘碎片
selecttablespace_name,sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks))))FSFI
fromdba_free_space
groupbytablespace_nameorderby1
1.查看表空间的名称及大小
selectt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_size
fromdba_tablespacest,dba_data_filesd
wheret.tablespace_name=d.tablespace_name
group by t.tablespace_name;2.查看表空间物理文件的名称及大小
selecttablespace_name,file_id,file_name,
round(bytes/(1024*1024),0)total_space
fromdba_data_files
order by tablespace_name;3.查看回滚段名称及大小
selectsegment_name,tablespace_name,r.status,
(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent,
max_extents,v.curextCurExtent
Fromdba_rollback_segsr,v$rollstatv
Wherer.segment_id=v.usn(+)
orderbysegment_name
15。耗资源的进程（topsession)
selects.schemanameschema_name,decode(sign(48-command),1,
to_char(command),'ActionCode#'||to_char(command))action,status
session_status,s.osuseros_user_name,s.sid,p.spid,s.serial#serial_num,
nvl(s.username,'Oracleprocess')user_name,s.terminalterminal,
s.programprogram,st.valuecriteria_valuefromv$sesstatst,v$sessions,v$processp
wherest.sid=s.sidandst.statistic#=to_number('38')and('ALL'='ALL'
or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc16。查看锁（lock）情况
select/*+RULE*/ls.osuseros_user_name,ls.usernameuser_name,
decode(ls.type,'RW','Rowwaitenqueuelock','TM','DMLenqueuelock','TX',
'Transactionenqueuelock','UL','Usersuppliedlock')lock_type,
o.object_nameobject,decode(ls.lmode,1,null,2,'RowShare',3,
'RowExclusive',4,'Share',5,'ShareRowExclusive',6,'Exclusive',null)
lock_mode,o.owner,ls.sid,ls.serial#serial_num,ls.id1,ls.id2
fromsys.dba_objectso,(selects.osuser,s.username,l.type,
l.lmode,s.sid,s.serial#,l.id1,l.id2fromv$sessions,
v$locklwheres.sid=l.sid)lswhereo.object_id=ls.id1ando.owner
<>'SYS'orderbyo.owner,o.object_name
--查看低效率的SQL语句
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,
SQL_TEXT
FROMV$SQLAREA
WHEREEXECUTIONS>0
ANDBUFFER_GETS>0
AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8
ORDERBY4DESC