挺好用的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,
                       'D