oracle DBA 常用监控脚本

oracleDBA常用监控脚本一、数据库构架体系1、表空间的监控是一个重要的任务，我们必须时刻关心表空间的设置，是否满足现在应用的需求，以下的语句可以查询到表空间的详细信息SELECTTABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,CONTENTS,LOGGING,EXTENT_MANAGEMENT,--Columnsnotavailableinv8.0.xALLOCATION_TYPE,--RemovethesecolumnsifrunningPLUGGED_IN,--againstav8.0.xdatabaseSEGMENT_SPACE_MANAGEMENT--useonlyinv9.2.xorlaterFROMDBA_TABLESPACESORDERBYTABLESPACE_NAME;2、对于某些数据文件没有设置为自动扩展的表空间来说，如果表空间满了，就将意味着数据库可能会因为没有空间而停止下来。监控表空间，最主要的就是监控剩余空间的大小或者是使用率。以下是监控表空间使用率与剩余空间大小的语句SELECTD.TABLESPACE_NAME,SPACESUM_SPACE(M),BLOCKSSUM_BLOCKS,SPACE-NVL(FREE_SPACE,0)USED_SPACE(M),ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)USED_RATE(%),FREE_SPACEFREE_SPACE(M)FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKSFROMDBA_DATA_FILESGROUPBYTABLESPACE_NAME)D,(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)FREE_SPACEFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME)FWHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)UNIONALL--ifhavetempfileSELECTD.TABLESPACE_NAME,SPACESUM_SPACE(M),BLOCKSSUM_BLOCKS,USED_SPACEUSED_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)BLOCKSFROMDBA_TEMP_FILESGROUPBYTABLESPACE_NAME)D,(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2)USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2)FREE_SPACEFROMV$TEMP_SPACE_HEADERGROUPBYTABLESPACE_NAME)FWHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)3、除了监控表空间的剩余空间，有时候我们也有必要了解一下该表空间是否具有自动扩展空间的能力，虽然我们建议在生产系统中预先分配空间。以下语句将完成这一功能SELECTT.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUSFROMDBA_TABLESPACEST,DBA_DATA_FILESDWHERET.TABLESPACE_NAME=D.TABLESPACE_NAMEORDERBYTABLESPACE_NAME,FILE_NAME4、我相信使用字典管理的表空间的也不少吧，因为字典管理的表空间中，每个表的下一个区间的大小是不可以预料的，所以我们必须监控那些表在字典管理的表空间中的下一个区间的分配将会引起性能问题或由于是非扩展的表空间而导致系统停止。以下语句检查那些表的扩展将引起表空间的扩展。SELECTA.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAMEFROMALL_TABLESA,(SELECTTABLESPACE_NAME,MAX(BYTES)BIG_CHUNKFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME)FWHEREF.TABLESPACE_NAME=A.TABLESPACE_NAMEANDA.NEXT_EXTENTF.BIG_CHUNK5、段的占用空间与区间数也是很需要注意的一个问题，如果一个段的占用空间太大，或者跨越太多的区间（在字典管理的表空间中，将有严重的性能影响），如果段没有可以再分配的区间，将导致数据库错误。所以，段的大小与区间监控也是一个很重要的工作SELECTS.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/1024NEXT_EXTENT(K)FROMDBA_SEGMENTSSWHERES.OWNERNOTIN('SYS','SYSTEM')ORDERBYUsed_ExtentsDESC6、对象的空间分配与空间利用情况，除了从各个方面的分析，如分析表，查询rowid等方法外，其实oracle提供了一个查询空间的包dbms_space，如果我们稍封装一下，将是非常好用的一个东西。CREATEORREPLACEPROCEDUREshow_space(p_segnameinvarchar2,p_typeinvarchar2default'TABLE',p_ownerinvarchar2defaultuser)ASv_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)ISBEGINdbms_output.put_line(rpad(p_label,40,'.')||p_num);END;BEGINv_segname:=upper(p_segname);v_type:=p_type;if(p_type='i'orp_type='I')thenv_type:='INDEX';endif;if(p_type='t'orp_type='T')thenv_type:='TABLE';endif;if(p_type='c'orp_type='C')thenv_type:='CLUSTER';endif;--以下部分不能用于ASSMdbms_space.free_blocks(segment_owner=p_owner,segment_name=v_segname,segment_type=v_type,freelist_group_id=0,free_blks=l_free_blks);--以上部分不能用于ASSMdbms_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;执行结果将如下所示SQLsetserveroutputon;SQLexecshow_space('test');FreeBlocks.............................1TotalBlocks............................8TotalBytes.............................65536UnusedBlocks...........................6UnusedBytes............................49152LastUsedExtFileId....................1LastUsedExtBlockId...................48521LastUsedBlock.........................2PL/SQLproceduresuccessfullycompleted8、数据库的索引如果有比较频繁的Delete操作，将可能导致索引产生很多碎片，所以，在有的时候，需要对所有的索引重新REBUILD，以便合并索引块，减少碎片，提高查询速度。SQLsetheadingoffSQLsetfeedbackoffSQLspoold:index.sqlSQLSELECT'alterindex'||index_name||'rebuild'||'tablespaceINDEXESstorage(initial256Knext256Kpctincrease0);'FROMall_indexesWHERE(tablespace_name!='INDEXES'ORnext_extent!=(256*1024))ANDwner=USERSQLspooloff这个时候，我们打开spool出来的文件，就可以直接运行了。9、表的主键是必要的，没有主键的表可以说是不符合设计规范的，所以我们需要监控表是否有主键SELECTtable_nameFROMall_tablesWHEREwner=USERMINUSSELECTtable_nameFROMall_constraintsWHEREwner=U