iplaybit
  • 首页
  • 系统运维
  • IT新闻
  • 科技新闻
  • 关于我们
  1. 首页
  2. 系统运维
  3. 正文

oracle 常用状态sql

2019年12月26日 499点热度 0人点赞 0条评论
--当前数据库运行状况:
select * from gv$lock
where
(type,id1,id2) in (select type,id1,id2 from gv$lock where request>0)
order by ctime desc,id1,id2,block desc;
select * from gv$locked_object ORDER BY object_id,inst_id,session_id;
select inst_id,event,wait_class,count(*) from gv$session group by inst_id,event,wait_class order by 1,4 desc;
--select * from v$session where type <> 'BACKGROUND' and status = 'ACTIVE';
SELECT * FROM v$log order by thread#,sequence#;
SELECT * FROM gv$resource_limit order by inst_id;
--统计数据库当前前台会话的操作类型及数量
select s.inst_id,s.command,a.name,s.num from (select inst_id,command,count(*) num from gv$session where  type <> 'BACKGROUND' group by inst_id,command) s,(select action,name from audit_actions) a  where a.action=s.command order by inst_id,num desc;
select t.inst_id,s.sid,s.username,s.osuser,t.START_TIME,d.timenow from gv$transaction t,gv$session s,(select to_char(sysdate,'MM/DD/YY HH24:MI:SS') timenow from dual) d where s.inst_id=t.inst_id and s.taddr= t.addr order by t.START_TIME;
--除后台会话以外的所有会话信息
SELECT inst_id,SID,event,wait_class,username,type,status,osuser,SECONDS_IN_WAIT FROM gv$session where type <> 'BACKGROUND' order by status,event,seconds_in_wait desc;
--除后台会话以外的所有正在活动的会话信息
select inst_id,SID,event,wait_class,username,type,status,osuser,SECONDS_IN_WAIT from gv$session where type <> 'BACKGROUND' and status = 'ACTIVE' order by status,event,seconds_in_wait desc;
--查某些特定等待事件的会话信息
select inst_id,SID,event,wait_class,username,type,status,osuser,SECONDS_IN_WAIT from gv$session where event like '%dblink%' or event like '%db file%read%' or event like '%SQL*Net break/reset to client%' or event like '%read by other session%' order by status,event,seconds_in_wait desc;
--查某些特定会话号的会话信息
SELECT inst_id,SID,event,wait_class,username,type,osuser,status,SECONDS_IN_WAIT FROM gv$session WHERE SID IN (126,87) order by status,event,seconds_in_wait desc;
--查询正在执行的sql
select a.SID, a.USERNAME, s.SQL_TEXT
from gv$session a, gv$sqltext_with_newlines s
where a.INST_ID=s.INST_ID
and a.SQL_ADDRESS = s.ADDRESS
and a.SQL_HASH_VALUE = s.HASH_VALUE
and a.INST_ID=1
and a.SID in (126,87)
order by a.USERNAME, a.SID, s.PIECE;
--查询上一条执行过的sql
select a.SID, a.USERNAME, s.SQL_TEXT
from gv$session a, gv$sqltext_with_newlines s
where a.INST_ID=s.INST_ID
and a.PREV_SQL_ID = s.SQL_ID
and a.INST_ID=1
and a.SID in (126,87)
order by a.USERNAME, a.SID, s.PIECE;
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--查询引起cursor等待事件的会话sid
SELECT a.*, s.sql_text
  FROM v$sql s,
       (SELECT sid,
               event,
               wait_class,
               p1 cursor_hash_value,
               p2raw Mutex_value,
               TO_NUMBER (SUBSTR (p2raw, 1, 8), 'xxxxxxxx') hold_mutex_x_sid
          FROM v$session_wait
         WHERE event LIKE 'cursor%') a
WHERE s.HASH_VALUE = a.cursor_hash_value;
标签: oracle 数据库 运行状况
最后更新:2019年12月26日

iplaybit

点赞
< 上一篇
下一篇 >

文章评论

取消回复
最新 热点 随机
最新 热点 随机
Steam内存测试工具 SPDK详解 Hadoop之HDFS优缺点、设计原理、框架 tmpfs总结 当64核遇上PCIe 4.0 超级算力是这样建成的 Edge for Linux开发者预览将至 WSL子系统可运行带GUI的Linux应用程序
欧洲核子研究中心首次测量到 反物质中的量子效应 查询oracle中所有用户信息 UDP Packet Lost - packet reassembles failed Linux 查看CPU信息、机器型号等硬件信息 在redhat6 操作系统中如何修改emulex hba卡队列深度 首次公开:希捷大秀20TB HAMR硬盘、14TB双磁臂硬盘
一起来了解为双屏设备而生的Windows 10X系统
标签聚合
docker oracle linux 存储 intel 数据库 文件系统 3par netapp 操作系统 windows hp AMD redo san cpu

COPYRIGHT © 2020 iplaybit. ALL RIGHTS RESERVED.

京ICP备18020432号-1