我综合oracle技术中国用户讨论组的一些知识，及自己掌握的一些知识，
把DBA人员应该掌握的一些SQL语句罗列了下，希望能对大家有所帮助。
下面罗列的这些SQL语句，我想，对于DBA人员来说是应该掌握的。
1.关于数据库构架体系
①表空间的监控是一个重要的任务，我们必须时刻关心表空间的设置，是否满足现在应用的需求，以下的语句可以查询到表空间的详细信息。
-
SQL code
-
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;
②对于某些数据文件没有设置为自动扩展的表空间来说，如果表空间满了，就将意味着数据库可能会因为没有空间而停止下来。监控表空间，最主要的就是监控剩余空间的大小或者是使用率。以下是监控表空间使用率与剩余空间大小的语句。
-
SQL code
-
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( + )
③除了监控表空间的剩余空间，有时候我们也有必要了解一下该表空间是否具有自动扩展空间的能力，虽然我们建议在生产系统中预先分配空间。以下语句将完成这一功能。
-
SQL code
-
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
④我相信使用字典管理的表空间的也不少吧，因为字典管理的表空间中，每个表的下一个区间的大小是不可以预料的，所以我们必须监控那些表在字典管理的表空间中的下一个区间的分配将会引起性能问题或由于是非扩展的表空间而导致系统停止。以下语句检查那些表的扩展将引起表空间的扩展。
-
SQL code
-
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
⑤段的占用空间与区间数也是很需要注意的一个问题，如果一个段的占用空间太大，或者跨越太多的区间（在字典管理的表空间中，将有严重的性能影响），如果段没有可以再分配的区间，将导致数据库错误。所以，段的大小与区间监控也是一个很重要的工作。
-
SQL code
-
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
⑥对象的空间分配与空间利用情况，除了从各个方面的分析，如分析表，查询rowid等方法外，其实oracle提供了一个查询空间的包dbms_space，如果我们稍封装一下，将是非常好用的一个东西。
-
SQL code
-
CREATE OR REPLACE PROCEDURE show_space(p_segname IN VARCHAR2 , p_owner IN VARCHAR2 DEFAULT USER , p_type IN VARCHAR2 DEFAULT ' TABLE ' , p_partition IN VARCHAR2 DEFAULT NULL ) -- This procedure uses AUTHID CURRENT USER so it can query DBA_* -- views using privileges from a ROLE and so it can be installed -- once per database, instead of once per user who wanted to use it. AUTHID CURRENT_USER as 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 ; l_segment_space_mgmt varchar2 ( 255 ); l_unformatted_blocks number ; l_unformatted_bytes number ; l_fs1_blocks number ; l_fs1_bytes number ; l_fs2_blocks number ; l_fs2_bytes number ; l_fs3_blocks number ; l_fs3_bytes number ; l_fs4_blocks number ; l_fs4_bytes number ; l_full_blocks number ; l_full_bytes number ; -- Inline procedure to print out numbers nicely formatted -- with a simple label. PROCEDURE p(p_label in varchar2 , p_num in number ) IS BEGIN dbms_output.put_line(rpad(p_label, 40 , ' . ' ) || to_char(p_num, ' 999,999,999,999 ' )); END ; BEGIN -- This query is executed dynamically in order to allow this procedure -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES -- via a role as is customary. -- NOTE: at runtime, the invoker MUST have access to these two -- views! -- This query determines if the object is an ASSM object or not. BEGIN EXECUTE IMMEDIATE ' select ts.segment_space_management FROM dba_segments seg, dba_tablespaces ts WHERE seg.segment_name = :p_segname AND (:p_partition is null or seg.partition_name = :p_partition) AND seg.owner = :p_owner AND seg.tablespace_name = ts.tablespace_name ' INTO l_segment_space_mgmt USING p_segname, p_partition, p_partition, p_owner; EXCEPTION WHEN too_many_rows THEN dbms_output.put_line( ' This must be a partitioned table, use p_partition => ' ); RETURN ; END ; -- If the object is in an ASSM tablespace, we must use this API -- call to get space information; else we use the FREE_BLOCKS -- API for the user managed segments. IF l_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( ' FS1 Blocks (0-25) ' , l_fs1_blocks); p( ' FS2 Blocks (25-50) ' , l_fs2_blocks); p( ' FS3 Blocks (50-75) ' , l_fs3_blocks); p( ' FS4 Blocks (75-100) ' , l_fs4_blocks); p( ' Full Blocks ' , 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( ' Total Bytes ' , l_total_bytes); p( ' Total MBytes ' , trunc(l_total_bytes / 1024 / 1024 )); 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 code
-
SQL > exec show_space2( ' test_stevie ' ); Free Blocks............................. 3 Total Blocks............................ 32 Total Bytes............................. 262 , 144 Total MBytes............................ 0 Unused Blocks........................... 0 Unused Bytes............................ 0 Last Used Ext FileId.................... 27 Last Used Ext BlockId................... 41 , 617 Last Used Block......................... 8 PL / SQL procedure successfully completed
-
⑦数据库的常规参数我就不说了，除了V$parameter中的常规参数外，ORACLE还有大量的隐含参数，下面的语句就可以查询到数据库的所有隐含参数以及其值与参数的描述。
-
SQL code
-
SELECT NAME, VALUE, decode(isdefault, ' TRUE ' , ' Y ' , ' N ' ) as " Default ", decode(ISEM, ' TRUE ' , ' Y ' , ' N ' ) as SesMod, decode(ISYM, ' IMMEDIATE ' , ' I ' , ' DEFERRED ' , ' D ' , ' FALSE ' , ' N ' ) as SysMod, decode(IMOD, ' MODIFIED ' , ' U ' , ' SYS_MODIFIED ' , ' S ' , ' N ' ) as Modified, decode(IADJ, ' TRUE ' , ' Y ' , ' N ' ) as Adjusted, description FROM ( -- GV$SYSTEM_PARAMETER SELECT x.inst_id as instance, x.indx + 1 , ksppinm as NAME, ksppity, ksppstvl as VALUE, ksppstdf as isdefault, decode(bitand(ksppiflg / 256 , 1 ), 1 , ' TRUE ' , ' FALSE ' ) as ISEM, decode(bitand(ksppiflg / 65536 , 3 ), 1 , ' IMMEDIATE ' , 2 , ' DEFERRED ' , ' FALSE ' ) as ISYM, decode(bitand(ksppstvf, 7 ), 1 , ' MODIFIED ' , ' FALSE ' ) as IMOD, decode(bitand(ksppstvf, 2 ), 2 , ' TRUE ' , ' FALSE ' ) as IADJ, ksppdesc as DESCRIPTION FROM x$ksppi x, x$ksppsv y WHERE x.indx = y.indx AND substr(ksppinm, 1 , 1 ) = ' _ ' AND x.inst_id = USERENV( ' Instance ' )) ORDER BY NAME
⑧数据库的索引如果有比较频繁的Delete操作，将可能导致索引产生很多碎片，所以，在有的时候，需要对所有的索引重新REBUILD，以便合并索引块，减少碎片，提高查询速度。
-
SQL code
-
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出来的文件，就可以直接运行了。
⑨表的主键是必要的，没有主键的表可以说是不符合设计规范的，所以我们需要监控表是否有主键。
-
SQL code
-
SELECT table_name FROM all_tables WHERE owner = USER MINUS SELECT table_name FROM all_constraints WHERE owner = USER AND constraint_type = ' P '
-
486
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
