变更支持

一、查看当前会话

SELECT sid
FROM   v$mystat
WHERE  rownum = 1;

二、查看活动会话

set time on timing on
SET LONG 1045876 LONGCHUNKSIZE 400 LINESIZE 5120 PAGESIZE 1003
COL INST_ID FORMAT 9 HEADING IN
COL USERNAME FORMAT A14 TRUNCATED
COL SID# FORMAT A12
COL SERIAL# FORMAT 999999 HEADING SERIAL
COL WAIT_CLASS FORMAT A10 TRUNCATED
COL WAIT_TIME FORMAT 999
COL BL_SESS FORMAT A8
COL FI_BL_SESS FORMAT A8
COL FINAL_BLOCKING_SESSION_STATUS HEADING FI_BL_S_S FORMAT A10 TRUNC
COL USERNAME FORMAT A12 TRUNCATED
COL EVENT FORMAT A28 TRUNCATED
COL SQL_CHILD_NUMBER FORMAT 99 HEADING SCH
COL PREV_CHILD_NUMBER FORMAT 99 HEADING PCH
COL MACHINE FORMAT A18 TRUNCATED
COL STATE FORMAT A10 TRUNCATED
COL STATUS FORMAT A6 TRUNCATED
COL TIME_SINCE_LAST_WAIT_MICRO NOPRINT
COL TIME_REMAINING_MICRO NOPRINT
COL WAIT_TIME_MICRO FORMAT 999999999999 HEADING WT_MILLI
COL P1 FORMAT 999999999999999999
COL P2 FORMAT 999999999999999999
COL P3 FORMAT 999999999999999999
COL PROGRAM FORMAT A32 TRUNCATE
COL COMMAND FORMAT 9999
COL PROCESS FORMAT A12
COL CLIENT_IDENTIFIER FORMAT A20 TRUNCATED
COL OSUSER FORMAT A10 TRUNC
COL SERVICE_NAME FORMAT A10 TRUNC
COL RESOURCE_CONSUMER_GROUP FORMAT A32 TRUNC
COL COMMAND_NAME FORMAT A10 TRUNC
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

SELECT INST_ID,
       SID
       || ','
       || SERIAL#                SID#,
       MACHINE,
       USERNAME,
       TYPE
       EVENT,
       STATE,
       WAIT_TIME_MICRO,
       BLOCKING_INSTANCE
       || ( CASE
              WHEN BLOCKING_INSTANCE IS NULL THEN NULL
              ELSE ','
            END )
       || BLOCKING_SESSION       BL_SESS,
       FINAL_BLOCKING_INSTANCE
       || ( CASE
              WHEN FINAL_BLOCKING_INSTANCE IS NULL THEN NULL
              ELSE ','
            END )
       || FINAL_BLOCKING_SESSION FI_BL_SESS,
       FINAL_BLOCKING_SESSION_STATUS,
       STATUS,
       COMMAND,
       SQL_ID,
       SQL_CHILD_NUMBER,
       PREV_SQL_ID,
       PREV_CHILD_NUMBER,
       WAIT_CLASS,
       WAIT_TIME,
       SECONDS_IN_WAIT,
       TIME_REMAINING_MICRO,
       TIME_SINCE_LAST_WAIT_MICRO,
       P1,
       P1RAW,
       P2,
       P2RAW,
       P3,
       P3RAW,
       PADDR,
       TADDR,
       SADDR,
       OSUSER,
       PROCESS,
       CREATOR_ADDR,
       AUDSID,
       ROW_WAIT_OBJ#,
       ROW_WAIT_FILE#,
       ROW_WAIT_BLOCK#,
       ROW_WAIT_ROW#,
       PROGRAM,
       COMMAND,
       PLSQL_ENTRY_OBJECT_ID,
       PLSQL_ENTRY_SUBPROGRAM_ID,
       PLSQL_OBJECT_ID,
       PLSQL_SUBPROGRAM_ID,
       SQL_EXEC_START,
       LOGON_TIME,
       CLIENT_IDENTIFIER,
       SERVICE_NAME,
       RESOURCE_CONSUMER_GROUP
FROM   GV$SESSION
WHERE  WAIT_CLASS# <> 6
ORDER  BY BLOCKING_INSTANCE NULLS FIRST,
          BLOCKING_SESSION,
          EVENT,
          INST_ID,
          SID;

三、查杀活动会话

1. 查杀锁住某个object的所有会话(gv$locked_object)

SELECT 'alter system kill session  '''
      || t2.sid
      || ','
      || t2.serial#
      || ',@'
      || t2.inst_id
      || ''' immediate;'
FROM   gv$locked_object t1,
      gv$session t2,
      dba_objects t3
WHERE  t1.session_id = t2.sid
      AND t1.inst_id = t2.inst_id
      AND t1.object_id = t3.object_id
      AND t3.object_name = Upper('&obj_name');

2. 查杀锁住某个object的所有会话(gv$access)

SELECT 'alter system kill session  '''
       || sid
       || ','
       || serial#
       || ',@'
       || inst_id
       || ''' immediate;'
FROM   gv$session
WHERE  ( sid, inst_id ) IN (SELECT /*+rule*/ SID,
                                             INST_ID
                            FROM   gv$access
                            WHERE  object = Upper('&obj_name'));

3. 查杀锁住某个object的所有会话(gv$lock)

SELECT 'alter system kill session  '''
       || t2.sid
       || ','
       || t2.serial#
       || ',@'
       || t2.inst_id
       || ''' immediate;'
FROM   gv$lock t1,
       gv$session t2,
       dba_objects t3
WHERE  t3.object_name = Upper('&obj_name')
       AND t3.object_id = t1.id1
       AND t1.inst_id = t2.inst_id
       AND t1.sid = t2.sid;

4. 查杀锁住某个object的所有会话(gv$enqueue_lock)

SELECT 'alter system kill session  '''
       || t2.sid
       || ','
       || t2.serial#
       || ',@'
       || t2.inst_id
       || ''' immediate;'
FROM   gv$enqueue_lock t1,
       gv$session t2,
       dba_objects t3
WHERE  t3.object_name = Upper('&obj_name')
       AND t3.object_id = t1.id1
       AND t1.inst_id = t2.inst_id
       AND t1.sid = t2.sid;

5. 查杀特定sql_id的所有会话

SELECT 'alter system kill session  '''
       || sid
       || ','
       || serial#
       || ',@'
       || inst_id
       || ''' immediate;'
FROM gv$session 
WHERE sql_id='&sql_id';

6. 根据inst_id和sid杀session

SELECT 'alter system kill session  '''
       || sid
       || ','
       || serial#
       || ',@'
       || inst_id
       || ''' immediate;'
FROM   gv$session where inst_id=&inst_id and sid=&sid;

7. 查杀堵塞特定用户的活动会话

SELECT 'alter system kill session  '''
       || sid
       || ','
       || serial#
       || ',@'
       || inst_id
       || ''' immediate;'
FROM   gv$session
WHERE  ( inst_id, sid ) IN (SELECT BLOCKING_INSTANCE,
                                   BLOCKING_SESSION
                            FROM   gv$session
                            WHERE  WAIT_CLASS# <> 6
                                   AND machine = &machine
                                   AND username = &username);

四、编译失效对象

1. 编译非包体失效对象

SELECT 'alter '
       || object_type
       || ' '
       || owner
       || '.'
       || object_name
       || ' compile;'
FROM   dba_objects
WHERE  status = 'INVALID'
        OR status = 'UNUSABLE'
           AND object_type <> 'PACKAGE BODY';

2. 编译包体失效对象

SELECT 'alter '
       || object_type
       || ' '
       || owner
       || '.'
       || object_name
       || ' compile;'
FROM   dba_objects
WHERE  status = 'INVALID'
        OR status = 'UNUSABLE'
           AND object_type = 'PACKAGE BODY';