实用SQL语句

23decxf

　　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;