欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

好用的sql语句合集

程序员文章站 2022-10-18 11:40:51
  1. /* 得到trace文件路径和名称 */   select d.value   || '/'   || lower (rtrim (i.instan...

  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.statisti = 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;