實用SQL語句

1. /* 得到trace檔案路徑和名稱 */

SELECT d.VALUE

       || '/'

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

       || '_ora_'

       || p.spid

       || '.trc' 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

/

2./* 顯示産生鎖定的sql語句 */

select  /*+  NO_MERGE(a)  NO_MERGE(b)  NO_MERGE(c)  */  a.username,  a.machine,  a.sid,a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL" from v$session a, v$lock b,v$sqltext c where a.username is not null and a.lockwait = b.kaddr and c.hash_value =a.sql_hash_value;

3./* 檢視oracle隐藏參數 */

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;

4./* 根據系統中oracle的pid來檢視sql */

select /*+ ORDERED */ sql_text from v$sqltext a where (a.hash_value,a.address) IN (select decode (sql_hash_value,0,prev_hash_value,sql_hash_value),decode (sql_hash_value,0,prev_sql_addr,sql_address) from v$session b where b.paddr =( select addr from v$process c where c.spid = '&pid')) order by piece ASC;

本文轉自 SimplePoint 51CTO部落格，原文連結：http://blog.51cto.com/2226894115/1888352，如需轉載請自行聯系原作者