一、查看当前会话
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;
三、查杀活动会话
- 查杀锁住某个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');
- 查杀锁住某个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'));
- 查杀锁住某个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;
- 查杀锁住某个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;
- 查杀特定sql_id的所有会话
SELECT 'alter system kill session '''
|| sid
|| ','
|| serial#
|| ',@'
|| inst_id
|| ''' immediate;'
FROM gv$session
WHERE sql_id='&sql_id';
- 根据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;
- 查杀堵塞特定用户的活动会话
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);
四、编译失效对象
- 编译非包体失效对象
SELECT 'alter '
|| object_type
|| ' '
|| owner
|| '.'
|| object_name
|| ' compile;'
FROM dba_objects
WHERE status = 'INVALID'
OR status = 'UNUSABLE'
AND object_type <> 'PACKAGE BODY';
- 编译包体失效对象
SELECT 'alter '
|| object_type
|| ' '
|| owner
|| '.'
|| object_name
|| ' compile;'
FROM dba_objects
WHERE status = 'INVALID'
OR status = 'UNUSABLE'
AND object_type = 'PACKAGE BODY';
