DBA人员应该掌握的一些SQL语句

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.............................               3Total Blocks............................              32Total Bytes.............................         262,144Total MBytes............................               0Unused Blocks...........................               0Unused Bytes............................               0Last Used Ext FileId....................              27Last Used Ext BlockId...................          41,617Last Used Block.........................               8PL/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 = USERSQL>spool off 

这个时候，我们打开spool出来的文件，就可以直接运行了。  
⑨表的主键是必要的，没有主键的表可以说是不符合设计规范的，所以我们需要监控表是否有主键。
SQL code 
SELECT table_name  FROM all_tables WHERE owner = USERMINUSSELECT table_name  FROM all_constraints WHERE owner = USER   AND constraint_type = 'P'
2.关于性能监控
①数据缓冲区的命中率已经不是性能调整中的主要问题了，但是，过低的命中率肯定是不可以的，在任何情况下，我们必须保证有一个大的data buffer和一个高的命中率。  
这个语句可以获得整体的数据缓冲命中率，越高越好。
SQL code 
SELECT a.VALUE + b.VALUE logical_reads,       c.VALUE phys_reads,       round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio  FROM v$sysstat a, v$sysstat b, v$sysstat c WHERE a.NAME = 'db block gets'   AND b.NAME = 'consistent gets'   AND c.NAME = 'physical reads'

②库缓冲说明了SQL语句的重载率，当然，一个SQL语句应当被执行的越多越好，如果重载率比较高，就考虑增加共享池大小或者是提高Bind变量的使用。
以下语句查询了Sql语句的重载率，越低越好。
SQL code 
SELECT SUM(pins) total_pins,       SUM(reloads) total_reloads,       SUM(reloads) / SUM(pins) * 100 libcache_reload_ratio  FROM v$librarycache

③用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。  
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。  
可以通过alter system kill session ‘sid,serial#’来杀掉会话。
SQL code 
SELECT /*+ rule */        s.username,       decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,       o.owner,       o.object_name,       o.object_type,       s.sid,       s.serial#,       s.terminal,       s.machine,       s.program,       s.osuser  FROM v$session s, v$lock l, dba_objects o WHERE l.sid = s.sid   AND l.id1 = o.object_id(+)   AND s.username is NOT NULL

④锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待  
以下的语句可以查询到谁锁了表，而谁在等待。
SQL code 
SELECT /*+ rule */       lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,       sysdate,       o.owner,       o.object_name,       o.object_type,       s.sid,       s.serial#  FROM v$locked_object l, dba_objects o, v$session s WHERE l.object_id = o.object_id   AND l.session_id = s.sid ORDER BY o.object_id, xidusn DESC

以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN。
⑤如果发生了事务或锁，想知道哪些回滚段正在被使用吗？其实通过事务表，我们可以详细的查询到事务与回滚段之间的关系。同时，如果关联会话表，我们则可以知道是哪个会话发动了这个事务。
SQL code 
SELECT s.USERNAME,       s.SID,       s.SERIAL#,       t.UBAFIL "UBA filenum",       t.UBABLK "UBA Block number",       t.USED_UBLK "Number os undo Blocks Used",       t.START_TIME,       t.STATUS,       t.START_SCNB,       t.XIDUSN RollID,       r.NAME RollName  FROM v$session s, v$transaction t, v$rollname r WHERE s.SADDR = t.SES_ADDR   AND t.XIDUSN = r.usn

⑥想知道现在哪个用户正在利用临时段吗？这个语句将告诉你哪个用户正在利用临时段。
SQL code 
SELECT b.tablespace,       b.segfile#,       b.segblk#,       b.blocks,       a.sid,       a.serial#,       a.username,       a.osuser,       a.status,       c.sql_text  FROM v$session a, v$sort_usage b, v$sql c WHERE a.saddr = b.session_addr   AND a.sql_address = c.address(+) ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

⑦如果利用会话跟踪或者是想查看某个会话的跟踪文件，那么查询到OS上的进程或线程号是非常重要的，因为文件的令名中，就包含这个信息，以下的语句可以查询到进程或线程号，由此就可以找到对应的文件。
SQL code 
SELECT p1.value || '/' || p2.value || '_ora_' || p.spid filename  FROM v$process p, v$session s, v$parameter p1, v$parameter p2 WHERE p1.name = 'user_dump_dest'   AND p2.name = 'db_name'   AND p.addr = s.paddr   AND s.audsid = USERENV('SESSIONID');

⑧在ORACLE 9i中，可以监控索引的使用，如果没有使用到的索引，完全可以删除掉，减少DML操作时的操作。  
以下就是开始索引监控与停止索引监控的脚本。
SQL code 
SQL>set heading off SQL>set echo off SQL>set feedback off SQL>set pages 10000 SQL>spool start_index_monitor.sql   SQL>SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;' SQL>FROM dba_indexes SQL>WHERE owner = USER;    SQL>spool off  set heading on SQL>set echo on SQL>set feedback on ----------------------------SQL>set heading off SQL>set echo off SQL>set feedback off SQL>set pages 10000 SQL>spool stop_index_monitor.sql   SQL>SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;' SQL>FROM dba_indexes SQL>WHERE owner = USER;    SQL>spool off  SQL>set heading on SQL>set echo on SQL>set feedback on 

如果需要监控更多的用户，可以将owner=User改写成别的  
          监控结果在视图v$object_usage中查询。
3.其他方面 
①根据实例来查看进程id。
SQL code 
select spid  from v$process where addr in (select paddr from v$session where sid = $sid)

②根据进程id来查看实例。
SQL code 
select sid  from v$session where paddr in (select addr from v$process where spid = $pid)

③查看当前在session中的sql文。
SQL code 
select SQL_TEXT  from V$SQLTEXT where HASH_VALUE = (select SQL_HASH_VALUE from v$session where sid = &sid) order by PIECE

④查看v$session_wait。
SQL code 
select *  from v$session_wait where event not like 'rdbms%'   and event not like 'SQL*N%'   and event not like '%timer';

⑤Dictionary缓存的命中率。
SQL code 
/*It should be about 15%, otherwise add share_pool_size*/SELECT sum(getmisses) / sum(gets) FROM v$rowcache;

⑥利用文件号和数据块来查看DB中的各个对象。
SQL code 
select owner,segment_name,segment_type    from dba_extents where file_id = [$fno and &dno between block_id and block_id + blocks - 1 ]

⑦寻找hot block。
SQL code 
select /*+ ordered */        e.owner || '.' || e.segment_name segment_name,       e.extent_id extent#,       x.dbablk - e.block_id + 1 block#,       x.tch,       l.child#  from sys.v$latch_children l, sys.x$bh x, sys.dba_extents e where l.name = 'cache buffers chains'   and l.sleeps > &sleep_count   and x.hladdr = l.addr   and e.file_id = x.file#   and x.dbablk between e.block_id and e.block_id + e.blocks - 1;

⑧找出每个文件上的等待事件。
SQL code 
select df.name, kf.count  from v$datafile df, x$kcbfwait kf where (kf.indx + 1) = df.file#;

⑨找出引起等待事件的SQL语句。
SQL code 
select sql_text, c.event  from v$sqlarea a, v$session b, v$session_wait c where a.address = b.sql_address   and b.sid = c.sid;

⑩判断你是从pfile启动还是spfile启动。
SQL code 
SQL> select decode(count(*), 1, 'spfile', 'pfile' ) as DECODE  2  from v$spparameter  3  where rownum=1  4  and isspecified='TRUE';DECODE------spfileSQL>