非常好用的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,"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.indxand 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;

以上就是本文给大家分享几个比较好用sql语句，希望大家喜欢。