一、数据库构架体系
1、表空间的监控是一个重要的任务，我们必须时刻关心表空间的设置，是否满足现在应用的需求，以下的语句可以查询到表空间的详细信息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、对于某些数据文件没有设置为自动扩展的表空间来说，如果表空间满了，就将意味着数据库可能会因为没有空间而停止下来。监控表空间，最主要的就是监控剩余空间的大小或者是使用率。以下是监控表空间使用率与剩余空间大小的语句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、除了监控表空间的剩余空间，有时候我们也有必要了解一下该表空间是否具有自动扩展空间的能力，虽然我们建议在生产系统中预先分配空间。以下语句将完成这一功能
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_NAME4、我相信使用字典管理的表空间的也不少吧，因为字典管理的表空间中，每个表的下一个区间的大小是不可以预料的，所以我们必须监控那些表在字典管理的表空间中的下一个区间的分配将会引起性能问题或由于是非扩展的表空间而导致系统停止。以下语句检查那些表的扩展将引起表空间的扩展。 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、段的占用空间与区间数也是很需要注意的一个问题，如果一个段的占用空间太大，或者跨越太多的区间（在字典管理的表空间中，将有严重的性能影响），如果段没有可以再分配的区间，将导致数据库错误。所以，段的大小与区间监控也是一个很重要的工作 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，如果我们稍封装一下，将是非常好用的一个东西。
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;执行结果将如下所示
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
8、数据库的索引如果有比较频繁的Delete操作，将可能导致索引产生很多碎片，所以，在有的时候，需要对所有的索引重新REBUILD，以便合并索引块，减少碎片，提高查询速度。
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出来的文件，就可以直接运行了。 9、表的主键是必要的，没有主键的表可以说是不符合设计规范的，所以我们需要监控表是否有主键SELECT table_name FROM all_tables WHERE owner = USER MINUS SELECT table_name FROM all_constraints WHERE owner = USER AND constraint_type = 'P'