oracle常用查询

Oracle检测死锁的Sql：

SELECT SID, DECODE (BLOCK, 0, 'NO', 'YES') blocker, DECODE (request, 0, 'NO', 'YES') waiter

FROM v$lock

WHERE request > 0 OR BLOCK > 0

ORDER BY BLOCK DESC

 

SELECT bs.username "Blocking User", bs.username "DB User",
 ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
 bs.serial# "Serial#", bs.sql_address "address",
 bs.sql_hash_value "Sql hash", bs.program "Blocking App",
 ws.program "Waiting App", bs.machine "Blocking Machine",
 ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
 ws.osuser "Waiting OS User", bs.serial# "Serial#", ws.serial# "WSerial#",
 DECODE (wk.TYPE, 'MR', 'Media Recovery', 'RT', 'Redo Thread', 'UN', 'USER Name', 'TX',
         'Transaction', 'TM', 'DML', 'UL', 'PL/SQL USER LOCK', 'DX', 'Distributed Xaction',
         'CF', 'Control FILE', 'IS', 'Instance State', 'FS', 'FILE SET', 'IR', 'Instance Recovery',
         'ST', 'Disk SPACE Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation',
         'LS', 'LOG START OR Switch', 'RW', 'ROW Wait', 'SQ', 'Sequence Number', 'TE', 'Extend TABLE',
         'TT', 'Temp TABLE', wk.TYPE ) lock_type, DECODE (hk.lmode, 0, 'None', 1, 'NULL', 2,
         'ROW-S (SS)', 3, 'ROW-X (SX)', 4, 'SHARE', 5, 'S/ROW-X (SSX)', 6, 'EXCLUSIVE', TO_CHAR (hk.lmode) ) mode_held,
 DECODE (wk.request, 0, 'None', 1, 'NULL', 2, 'ROW-S (SS)', 3, 'ROW-X (SX)', 4, 'SHARE', 5, 'S/ROW-X (SSX)', 6,
         'EXCLUSIVE', TO_CHAR (wk.request) ) mode_requested, TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
 DECODE (hk.BLOCK, 0, 'NOT Blocking', /**//* Not blocking any other processes */ 1, 'Blocking',
  /**//* This lock blocks other processes */ 2, 'Global', /**//* This lock is global, so we can't tell */ TO_CHAR (hk.BLOCK) ) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1

/

检查应用系统中无效对象情况（产生无效对象编译脚本情况）：

SELECT 'PROMPT Compile VIEW '||OBJECT_NAME||' ...' || CHR(10)||

       'ALTER VIEW '||OBJECT_NAME || ' COMPILE;'||CHR(10)

       ||'SHOW ERRORS;'||CHR(10)  "--COMPILE VIEW "

FROM DBA_OBJECTS

WHERE STATUS <> 'VALID'

    AND OBJECT_TYPE = 'VIEW'

ORDER BY OBJECT_NAME

 

SELECT 'PROMPT Compile TRIGGER '||OBJECT_NAME||' ...'||CHR(10)||

       'ALTER TRIGGER '||OBJECT_NAME||' COMPILE;'||CHR(10)

       ||'SHOW ERRORS;'||CHR(10) "--COMPILE TRIGGER "

FROM DBA_OBJECTS

WHERE STATUS <> 'VALID'

     AND OBJECT_TYPE =  'TRIGGER'

ORDER BY OBJECT_NAME

/

查询Oracle隐藏参数：

select x.ksppinm, y.ksppstvl, x.ksppdesc

from x$ksppi x , x$ksppcv y

where x.indx = y.indx

      and y.inst_id = userenv('Instance')

      and x.inst_id = userenv('Instance')

      and x.ksppinm like '\_%' escape '\'

/

或：

SELECT   ksppinm, ksppstvl, ksppdesc  FROM   x$ksppi x, x$ksppcv y WHERE   x.indx = y.indx AND  ksppinm = '_serial_direct_read'
/

查询数据库SCN的headroom（是指Maximum Reasonable SCN 与当前数据库SCN的差值）是多少：

select
     ((((
      ((to_number(to_char(cur_date,'YYYY'))-1988)*12*31*24*60*60) +
      ((to_number(to_char(cur_date,'MM'))-1)*31*24*60*60) +
      (((to_number(to_char(cur_date,'DD'))-1))*24*60*60) +
      (to_number(to_char(cur_date,'HH24'))*60*60) +
      (to_number(to_char(cur_date,'MI'))*60) +
      (to_number(to_char(cur_date,'SS')))
      ) * (16*1024)) - 12821569053984)
     / (16*1024*60*60*24)
     ) headroom
     from (select to_date('2012-05-30 15:09:57','yyyy-mm-dd hh24:mi:ss') cur_date from dual)

/

 

查询数据库当前最大的可能SCN“最大合理SCN”：

col for 999,999,999,999,999,999

select
(
    (
        (
            (
                (
                    (
                        to_char(sysdate,'YYYY')-1988
                    )*12+
                to_char(sysdate,'mm')-1
                )*31+to_char(sysdate,'dd')-1
            )*24+to_char(sysdate,'hh24')
        )*60+to_char(sysdate,'mi')
    )*60+to_char(sysdate,'ss')
) * to_number('ffff','XXXXXXXX')/4 scn
from dual
/

 

根据SCN查询表T更多的历史记录：

Select versions_xid,versions_startscn,versions_endscn,
   DECODE(versions_operation,'I','Insert','U','Update','D','Delete', 'Original') "Operation",owner
from t versions between scn minvalue and maxvalue
/

查看正在执行的SQL语句的执行时间和SQL语句的等待事件的信息：

select a.sql_text,b.status,b.last_call_et,b.event
from v$sql a,v$session b
where a.sql_id=b.sql_id and b.sid=41
/

或

select event,p1,p1text,p2,p2text,p3,p3text,wait_time,seconds_in_wait,state

from v$session_wait

where sid=41

/

查看session自运行以来的各种资源消耗统计数据：

select a.sid,b.name,a.value

from v$sesstat a,v$statname b

where a.sid=41

    and a.statistic#=b.statistic#

    and b.name in ('consistent gets','physical reads','parse count(total)','parse count(hard)')

/

fenng提供了一个sql去判断碎片的程度 如下：

SELECT free_space, avg_free_size,used_space, avg_used_size, request_failures, last_failure_size

       FROM v$shared_pool_reserved;

如果：REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

或者  REQUEST_FAILURES 等于0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

这时候就需要去调整了；

 

从V$PROCESS中查看PGA总的分配情况：

select spid,program,pga_max_mem max,pga_alloc_mem alloc,pga_used_mem used,pga_freeable_mem free

from v$process

where spid in (select spid from v$process where addr in

                 (select paddr from v$session where sid in

                    (select distinct sid from v$mystat)))

/

从v$PROCESS_MEMORY试图中，可以进一步查看PGA各个区域分配的大小：

select p.program,p.spid,pm.category,pm.allocated,pm.used,pm.max_allocated

from v$process p,v$process_memory pm

where p.pid=pm.pid

   and p.spid in (select spid from v$process where addr in

                    (select paddr from v$session where sid in

                       (select distinct sid from v$mystat)))

/

 

 

11g，检查表空间使用情况：

select df.tablespace_name "表空间名",

       totalspace - freespace "used_mb",

       totalspace "总空间M",

       freespace "剩余空间M",

       round((1 - freespace / totalspace) * 100, 2) "使用率%"

  from (select tablespace_name, round(sum(bytes) / 1024 / 1024) totalspace

          from dba_data_files

         group by tablespace_name) df,

       (select tablespace_name, round(sum(bytes) / 1024 / 1024) freespace

          from dba_free_space    

         group by tablespace_name) fs

 where df.tablespace_name = fs.tablespace_name(+) order by round((1 - freespace / totalspace) * 100, 2) desc

/

表空间FSFI（自由空间碎片索引）值查询，FSFI最大值为100即为一个理想的单文件表空间：

select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI
from dba_free_space
group by tablespace_name order by 1

/

检查数据库表空间碎片量（表空间管理模式分为LOCAL和DICTIONARY）：

select a.tablespace_name ,count(1) 碎片量

from dba_free_space a,dba_tablespaces b
where a.tablespace_name =b.tablespace_name
and b.extent_management = 'LOCAL'
group by a.tablespace_name
having count(1) >20
order by 2

/

表空间碎片整理：

alter database users coalesce;

查询表空间的自由空间：

select a.file_id "FileNo",a.tablespace_name "Tablespace_name",

       a.bytes "Bytes",a.bytes-sum(nvl(b.bytes,0)) "Used",

       sum(nvl(b.bytes,0)) "Free",sum(nvl(b.bytes,0))/a.bytes*100 "%free"

from dba_data_files a, dba_free_space b

where a.file_id=b.file_id(+)

group by a.tablespace_name ,a.file_id,a.bytes order by a.tablespace_name

/

检查Oracle系统撤销表空间使用情况（可了解UNDO TABLESPACE使用情况）：

select TO_CHAR(MIN(Begin_Time),'DD HH24:MI:SS')  "开始时间",
    TO_CHAR(MAX(End_Time),'DD HH24:MI:SS') "结束时间",
    SUM(Undoblks)  "用撤消块数",
    SUM(Txncount)   "事务执行块数",
    MAX(Maxquerylen)  "查询最长秒",
    MAX(Maxconcurrency) "最高事务数",
    SUM(Ssolderrcnt) "ORA-01555次数",
    SUM(Nospaceerrcnt) "无可用空间数"
from V$UNDOSTAT

/

检查回收站内的相关对象：

select *

from (

    select a.owner,a.OBJECT_NAME,a.ORIGINAL_NAME,b.bytes/1024/1024 MB

    from dba_recyclebin a,dba_segments b

    where a.object_name=b.segment_name

    order by MB desc)

where rownum <11

/

检查Oracle系统数据文件物理读情况（可了解目前数据文件的平衡情况）：

Select sum(Decode(Name,'physical reads',value,0)) Dsk_Rds,

       sum(Decode(Name,'db block gets',value,0)) Blk_Gts,

       sum(Decode(Name,'consistent gets',value,0)) Con_Gts,

       ((1-(sum(Decode(Name,'physical reads',value,0))/

       (sum(Decode(Name,'db block gets',value,0))+

        sum(Decode(Name,'consistent gets',value,0)))))*100) Hit_Rate

From V$sysstat

/

用SQL查出当前的trace文件名：

SELECT      d.VALUE

         || '/'

         || LOWER (RTRIM (i.INSTANCE, CHR (0)))

         || '_ora_'

         || p.spid

         || '.trc'

            AS "trace_file_name"

  FROM   (SELECT   p.spid

            FROM   v$mystat m, v$session s, v$process p

           WHERE   m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

         (SELECT   t.INSTANCE

            FROM   v$thread t, v$parameter v

           WHERE   v.NAME = 'thread'

                   AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

         (SELECT   VALUE

            FROM   v$parameter

           WHERE   NAME = 'user_dump_dest') d

/

或：

select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace

from v$process a,v$session b,v$parameter c,v$instance d

where a.addr=b.paddr

  and b.audsid=userenv('sessionid')

  and c.name='user_dump_dest'

/

查看block情况（所在文件、块、行；bbed用）

select
rowid,
dbms_rowid.rowid_relative_fno(rowid) rel_fno,
dbms_rowid.rowid_block_number(rowid) blockno,
dbms_rowid.rowid_row_number(rowid) rowno
from d

/

或（对于大表）：

select owner,segment_name,header_file,header_block,blocks

from dba_segments

where owner='SYS'

    and segment_name='D'

/