频道栏目
首页 > 数据库 > SQL教程 > 经典SQL语句大全 > 正文
好用的sql语句合集
2016-05-12 16:03:53         来源:赵海华博客  
收藏   我要投稿

  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;

点击复制链接 与好友分享!回本站首页
相关TAG标签 合集 好用 语句
上一篇:高效SQL优化 非常好用的SQL语句优化34条
下一篇:非常实用的MSSQL语句
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站