非常好用的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语句,希望大家喜欢。