排查思路

1. 查看数据库active session 的等待事件

  SELECT s.inst_id,s.wait_class, s.event, COUNT (*)
    FROM gv$session s
   WHERE status = 'ACTIVE'
GROUP BY s.inst_id,s.wait_class, s.event
ORDER BY 4 DESC;

2.查看active session 正在执行的sql

SELECT q.sql_text,
       q.sql_fulltext,
       s.sql_exec_start,
       s.sql_exec_id,
       s.sql_id,
       s.*
  FROM gv$session s, v$sql q
 WHERE s.sql_id = q.sql_id AND s.status = 'ACTIVE' AND s.TYPE = 'USER';

3.查看是否有锁等待

col sql_id for a20
col status for a10
col event for a40
col wait_class for a20
col program for a30

WITH s AS
 (SELECT * FROM gv$session)
SELECT LEVEL,
       lpad(' ', (LEVEL - 1) * 5, ' ') || nvl(s.username, '(oracle)') AS username,
       s.inst_id,
       s.sid,
       s.serial#,
       s.sql_id,
       s.status,
       s.event,
       s.blocking_session,
       s.wait_class,
       s.seconds_in_wait,
       --s.machine,
       --s.module,
       s.program 
       --,to_char(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time
  FROM s
 WHERE LEVEL > 1
    OR EXISTS (SELECT 1 FROM s s2 WHERE s2.blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
 START WITH s.blocking_session IS NULL; 

4. 查看那些对象被锁住，被哪个session锁住，锁的类型，session正在执行的sql

  SELECT p.inst_id,
         p.spid,
         p.BACKGROUND,
         l.session_id sid,
         s.serial#,
         s.username,
         s.osuser,
         s.status,
         s.event,
         o.object_name,
         l.locked_mode,
         s.program,
         s.logon_time,
         s.sql_id
    FROM gv$process p,
         gv$locked_object l,
         dba_objects o,
         gv$session s
   WHERE     l.object_id = o.object_id
         AND l.session_id = s.sid
         AND p.addr = s.paddr
ORDER BY sid, s.serial#;

5. 查看访问某个对象（表、存储过程等）的Session:

select /*+rule+*/ s.inst_id,s.username,s.sid,s.serial#,s.event,s.sql_id,
 'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' ||s.inst_id || ''' immediate;' kill_script  from gv$session s
 where s.sid in (select /*+rule */ SID  from  gv$access where object =upper('tableA')) and type='USER'; 

SELECT /*+ rule */
       DISTINCT
       s.inst_id,
       s.username,
       s.sid,
       s.serial#,
       s.status,
       s.event,
       s.sql_id,
       s.sql_exec_id,
       s.sql_exec_start,
        s.blocking_session,
       s.wait_class,
       s.seconds_in_wait,
       s.machine,
       s.module,
       s.program 
       ,to_char(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time
       'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' ||s.inst_id || ''' immediate;' kill_session_script,
       'kill -9 ' || p.spid kill_process_script
  FROM gv$session s, gv$access a, gv$process p
 WHERE     s.inst_id = a.inst_id
       AND s.sid = a.sid
       AND p.addr = s.paddr
       AND s.TYPE = 'USER'
       and a.object =upper('tableA')
       --  AND s.status = 'KILLED'
       -- AND s.username = 'userA'; 
<!-- 获得表、索引、视图、存储过程、函数的DDL

select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual;
select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual;  -->

6. kill session

SELECT /*+ rule */
       DISTINCT
       s.inst_id,
       s.username,
       s.sid,
       s.serial#,
       s.status,
       s.event,
       s.sql_id,
       s.sql_exec_id,
       s.sql_exec_start,
       s.PLSQL_OBJECT_ID,
       s.PREV_EXEC_ID,
       s.PREV_EXEC_START,
       s.blocking_session,
       s.BLOCKING_INSTANCE,
       s.BLOCKING_SESSION_STATUS,    
       s.wait_class,
       s.seconds_in_wait,
       s.machine,
       s.module,
       s.program,
       p.spid,
       p.PGA_USED_MEM/1024/1024,
       p.PGA_ALLOC_MEM/1024/1024,
       p.PGA_MAX_MEM/1024/1024
       ,to_char(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time,
       'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' ||s.inst_id || ''' immediate;' kill_session_script,
       'kill -9 ' || p.spid kill_process_script
  FROM gv$session s,  gv$process p
 WHERE     s.inst_id = a.inst_id
       AND s.sid in (select /*+rule */ SID  from  gv$access where object =upper('tableA'))
       AND p.addr = s.paddr
       AND s.TYPE = 'USER'
       --  AND s.status = 'KILLED'
        AND s.username = 'userA';