好用的sql语句 (转)


转自  http://baiying.blog.51cto.com/1068039/665327

 

 

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;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s