DBA常用脚本1-数据库构架体系

1、表空间的监控是一个重要的任务，我们必须时刻关心表空间的设置，是否满足现在应用的需求，以下的语句可以查询到表空间的详细信息
　　
　　Code: [Copy to clipboard]
　　SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,
　　MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,
　　CONTENTS,LOGGING,
　　EXTENT_MANAGEMENT,　-- Columns not available in v8.0.x
　　ALLOCATION_TYPE,　　-- Remove these columns if running
　　PLUGGED_IN,　　　　　-- against a v8.0.x database
　　SEGMENT_SPACE_MANAGEMENT --use only in v9.2.x or later
　　FROM　　 DBA_TABLESPACES
　　ORDER BY TABLESPACE_NAME;
　　
　　2、对于某些数据文件没有设置为自动扩展的表空间来说，如果表空间满了，就将意味着数据库可能会因为没有空间而停止下来。监控表空间，最主要的就是监控剩余空间的大小或者是使用率。以下是监控表空间使用率与剩余空间大小的语句
　　
　　Code: [Copy to clipboard]
　　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
　　(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
　　FROM DBA_DATA_FILES
　　GROUP BY TABLESPACE_NAME) D,
　　(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
　　FROM DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F
　　WHERE　D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
　　UNION ALL　--if have tempfile
　　SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
　　USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
　　NVL(FREE_SPACE,0) "FREE_SPACE(M)"
　　FROM
　　(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
　　FROM DBA_TEMP_FILES
　　GROUP BY TABLESPACE_NAME) D,
　　(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
　　ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
　　FROM V$TEMP_SPACE_HEADER
　　GROUP BY TABLESPACE_NAME) F
　　WHERE　D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
　　
　　3、除了监控表空间的剩余空间，有时候我们也有必要了解一下该表空间是否具有自动扩展空间的能力，虽然我们建议在生产系统中预先分配空间。以下语句将完成这一功能
　　
　　Code: [Copy to clipboard]
　　SELECT T.TABLESPACE_NAME,D.FILE_NAME,
　　D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
　　FROM DBA_TABLESPACES T,
　　DBA_DATA_FILES　D
　　WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME
　　ORDER BY TABLESPACE_NAME,FILE_NAME
　　
　　4、我相信使用字典管理的表空间的也不少吧，因为字典管理的表空间中，每个表的下一个区间的大小是不可以预料的，所以我们必须监控那些表在字典管理的表空间中的下一个区间的分配将会引起性能问题或由于是非扩展的表空间而导致系统停止。以下语句检查那些表的扩展将引起表空间的扩展。
　　
　　Code: [Copy to clipboard]
　　SELECT A.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAME
　　FROM ALL_TABLES A,
　　(SELECT TABLESPACE_NAME, MAX(BYTES) BIG_CHUNK
　　FROM DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F
　　WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME
　　AND A.NEXT_EXTENT > F.BIG_CHUNK
　　
　　5、段的占用空间与区间数也是很需要注意的一个问题，如果一个段的占用空间太大，或者跨越太多的区间（在字典管理的表空间中，将有严重的性能影响），如果段没有可以再分配的区间，将导致数据库错误。所以，段的大小与区间监控也是一个很重要的工作
　　
　　Code: [Copy to clipboard]
　　SELECT S.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME,
　　ROUND(BYTES/(1024*1024),2) "USED_SPACE(M)",
　　EXTENTS USED_EXTENTS,S.MAX_EXTENTS,S.BLOCKS ALLOCATED_BLOCKS,
　　S.BLOCKS USED_BOLCKS,S.PCT_INCREASE,S.NEXT_EXTENT/1024 "NEXT_EXTENT(K)"
　　FROM DBA_SEGMENTS S
　　WHERE S.OWNER NOT IN (''SYS'',''SYSTEM'')
　　ORDER BY Used_Extents DESC
　　
　　6、对象的空间分配与空间利用情况，除了从各个方面的分析，如分析表，查询rowid等方法外，其实oracle提供了一个查询空间的包dbms_space，如果我们稍封装一下，将是非常好用的一个东西。
　　
　　Code: [Copy to clipboard]
　　
　　CREATE OR REPLACE PROCEDURE show_space
　　(p_segname in varchar2,
　　p_type in varchar2 default ''TABLE'' ,
　　p_owner in varchar2 default user)
　　AS
　　v_segname varchar2(100);
　　v_type varchar2(10);
　　l_free_blks number;
　　l_total_blocks number;
　　l_total_bytes number;
　　l_unused_blocks number;
　　l_unused_bytes number;
　　l_LastUsedExtFileId number;
　　l_LastUsedExtBlockId number;
　　l_LAST_USED_BLOCK number;
　　PROCEDURE　p( p_label in varchar2, p_num in number )
　　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'' or p_type = ''I'') then
　　v_type := ''INDEX'';
　　end if;
　　if (p_type = ''t'' or p_type = ''T'') then
　　v_type := ''TABLE'';
　　end if;
　　if (p_type = ''c'' or p_type = ''C'') then
　　v_type := ''CLUSTER'';
　　end if;
　　
　　--以下部分不能用于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( ''Free Blocks'', l_free_blks );
　　p( ''Total Blocks'', l_total_blocks );
　　p( ''Total Bytes'', l_total_bytes );
　　p( ''Unused Blocks'', l_unused_blocks );
　　p( ''Unused Bytes'', l_unused_bytes );
　　p( ''Last Used Ext FileId'', l_LastUsedExtFileId );
　　p( ''Last Used Ext BlockId'', l_LastUsedExtBlockId );
　　p( ''Last Used Block'', l_LAST_USED_BLOCK );
　　END;
　　
　　执行结果将如下所示
　　
　　Code: [Copy to clipboard]
　　
　　SQL> set serveroutput on;
　　SQL> exec show_space(''test'');
　　Free Blocks.............................1
　　Total Blocks............................8
　　Total Bytes.............................65536
　　Unused Blocks...........................6
　　Unused Bytes............................49152
　　Last Used Ext FileId....................1
　　Last Used Ext BlockId...................48521
　　Last Used Block.........................2
　　PL/SQL procedure successfully completed
　　
　　7、数据库的索引如果有比较频繁的Delete操作，将可能导致索引产生很多碎片，所以，在有的时候，需要对所有的索引重新REBUILD，以便合并索引块，减少碎片，提高查询速度。
　　
　　Code: [Copy to clipboard]
　　
　　SQL> set heading off
　　SQL> set feedback off
　　SQL> spool d:\index.sql
　　SQL> SELECT ''alter index '' || index_name || '' rebuild ''
　　||''tablespace INDEXES storage(initial 256K next 256K pctincrease 0);''
　　FROM all_indexes
　　WHERE ( tablespace_name != ''INDEXES''
　　OR next_extent != ( 256 * 1024 )
　　)
　　AND owner = USER
　　SQL>spool off
　　
　　这个时候，我们打开spool出来的文件，就可以直接运行了。
　　
　　8、表的主键是必要的，没有主键的表可以说是不符合设计规范的，所以我们需要监控表是否有主键
　　
　　Code: [Copy to clipboard]
　　SELECT table_name
　　FROM all_tables
　　WHERE owner = USER
　　MINUS
　　SELECT table_name
　　FROM all_constraints
　　WHERE owner = USER
　　AND constraint_type = ''P''