非常好用的sql语句(日常整理)

前端之家收集整理的这篇文章主要介绍了非常好用的sql语句(日常整理)前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

1. /* 得到trace文件路径和名称 */


sql;"> 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语句 */

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隐藏参数 */

sql;"> select name,value,decode(isdefault,'TRUE','Y','N') as "Default",decode(ISEM,'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,'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,'FALSE') as ISEM,decode(bitand(ksppiflg / 65536,3),2,'FALSE') as ISYM,decode(bitand(ksppstvf,7),'FALSE') as IMOD,2),'FALSE') as IADJ,ksppdesc as description from x$ksppi x,x$ksppsv y where x.indx = y.indx and substr(ksppinm,1) = '_' and x.inst_id = USERENV('Instance')) order by name;

4./* 根据系统中oracle的pid来查看sql */

sql;"> select /*+ ORDERED */ sql_text from v$sqltext a where (a.hash_value,a.address) IN (select decode (sql_hash_value,prev_hash_value,sql_hash_value),decode (sql_hash_value,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语句,希望大家喜欢。

原文链接:https://www.f2er.com/mssql/63003.html

猜你在找的MsSQL相关文章