非常好用的sql语句(日常整理)

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

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;

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;

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;