oracle会话控制和存储状态查询

 --查找执行中的会话

SELECT s.sid, s.serial#, s.username, s.program, q.SQL_FULLTEXT
FROM v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.status = 'ACTIVE'; 

--死锁查询

 SELECT OBJECT_NAME, MACHINE, S.SID, S.SERIAL#, 'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# || ''';'
FROM GV$LOCKED_OBJECT L,
     DBA_OBJECTS O,
     GV$SESSION S
WHERE L.OBJECT_ID = O.OBJECT_ID
  AND L.SESSION_ID = S.SID;

--找到持有表资源的锁

SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, l.id1, l.id2, l.lmode
FROM v$session s, v$lock l
WHERE s.sid = l.sid
  AND l.id1 = (SELECT object_id FROM dba_objects WHERE owner = '用户名' AND object_name = '表名');

  
-- 替换以下值为你想要杀死的会话的SID和SERIAL#

ALTER SYSTEM KILL SESSION '81,60284';

--查看存储过程执行状态

SELECT 
    sql_id AS "SQL编号",
    ROUND(elapsed_time/1e6, 3) AS "执行耗时秒数",
    ROUND(cpu_time/1e6, 3) AS "CPU耗时秒数",
    ROUND(user_io_wait_time/1e6, 3) AS "IO等待秒数",
    buffer_gets AS "缓冲区读取次数",
    disk_reads AS "物理磁盘读取",
    TO_CHAR(sql_exec_start, 'yyyy-mm-dd hh24:mi:ss') AS "执行开始时间",
    sql_text AS "SQL文本内容"
FROM v$sql_monitor 
WHERE sql_text LIKE '%JX_A_ZDS%';  -- 请替换实际存储过程名