非常好用的sql语句(日常整理)
1./*得到trace文件路径和名称*/
SELECTd.VALUE ||'/' ||LOWER(RTRIM(i.INSTANCE,CHR(0))) ||'_ora_' ||p.spid ||'.trc'trace_file_name FROM(SELECTp.spid FROMv$mystatm,v$sessions,v$processp WHEREm.statistic#=1ANDs.SID=m.SIDANDp.addr=s.paddr)p, (SELECTt.INSTANCE FROMv$threadt,v$parameterv WHEREv.NAME='thread' AND(v.VALUE=0ORt.thread#=TO_NUMBER(v.VALUE)))i, (SELECTVALUE FROMv$parameter WHERENAME='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"fromv$sessiona,v$lockb,v$sqltextcwherea.usernameisnotnullanda.lockwait=b.kaddrandc.hash_value=a.sql_hash_value;
3./*查看oracle隐藏参数*/
selectname,
value,
decode(isdefault,'TRUE','Y','N')as"Default",
decode(ISEM,'TRUE','Y','N')asSesMod,
decode(ISYM,'IMMEDIATE','I','DEFERRED','D','FALSE','N')asSysMod,
decode(IMOD,'MODIFIED','U','SYS_MODIFIED','S','N')asModified,
decode(IADJ,'TRUE','Y','N')asAdjusted,
description
from(--GV$SYSTEM_PARAMETER
selectx.inst_idasinstance,
x.indx+1,
ksppinmasname,
ksppity,
ksppstvlasvalue,
ksppstdfasisdefault,
decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE')asISEM,
decode(bitand(ksppiflg/65536,3),
1,
'IMMEDIATE',
2,
'DEFERRED',
'FALSE')asISYM,
decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE')asIMOD,
decode(bitand(ksppstvf,2),2,'TRUE','FALSE')asIADJ,
ksppdescasdescription
fromx$ksppix,x$ksppsvy
wherex.indx=y.indx
andsubstr(ksppinm,1,1)='_'
andx.inst_id=USERENV('Instance'))
orderbyname;
4./*根据系统中oracle的pid来查看sql*/
select/*+ORDERED*/sql_textfromv$sqltextawhere(a.hash_value,a.address)IN(selectdecode(sql_hash_value,0,prev_hash_value,sql_hash_value),decode(sql_hash_value,0,prev_sql_addr,sql_address)fromv$sessionbwhereb.paddr=(selectaddrfromv$processcwherec.spid='&pid'))orderbypieceASC;
以上就是本文给大家分享几个比较好用sql语句，希望大家喜欢。