Very useful SQL statements (daily collation) _mssql

1./* Get trace file path and name * *

SELECT D.value
    | | '/'
    || LOWER (RTRIM (i.instance, CHR (0))
    | | ' _ora_ ' | | | p.spid | |
    '. TRC ' Trace_file_name from
 (SELECT p.spid
     to V$mystat m, V$session S, v$process p
     WHERE m.statistic# = 1 An D 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./* displays the SQL statement that generated the lock.

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 are NOT null and a.lockwait = B.KADDR and C.hash _value =a.sql_hash_value;

3./* View Oracle Hidden parameters */

Select name, value, decode (IsDefault, ' true ', ' y ', ' n ') as "Default", Decode (Isem, ' TRUE ', ' Y ', ' n ') as Sesmo D, Decode (Isym, ' IMMEDIATE ', ' I ', ' DEFERRED ', ' D ', ' FALSE ', ' N ') as Sysmod, decode (IMOD, ' MODIFIED ', ' U ', ' Sys_mod Ified ', S ', ' N ') as Modified, decode (Iadj, ' TRUE ', ' Y ', ' n ') as adjusted, description from (--gv$system_paramet ER Select x.inst_id as instance, X.indx + 1, ksppinm as name, Ksppity, KSPPSTVL as VA Lue, KSPPSTDF as IsDefault, decode (Bitand (ksppiflg/256, 1), 1, ' TRUE ', ' FALSE ') as Isem, decode ( Bitand (ksppiflg/65536, 3), 1, ' IMMEDIATE ', 2, ' DEFERRED ', ' F Alse ') 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./* to view SQL according to the Oracle PID in the system

Select/*+ ORDERED * * Sql_text from V$sqltext a Where (a.hash_value,a.address) in (Select Decode (sql_hash_value,0,prev_ha Sh_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 ') an order by piece ASC;

The above is this article to share a few more easy to use SQL statements, I hope you like.