非常好用的sql语句(日常整理)
程序员文章站
2023-11-18 19:38:22
1. /* 得到trace文件路径和名称 */
select d.value
|| '/'
|| lower (rtrim (i.inst...
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;
以上就是本文给大家分享几个比较好用sql语句,希望大家喜欢。