|
1. v$active_session_history 기준, Session 조회
set pagesize 300
set lines 200
set echo off
set feedback off
set timing off
set verify off
col spid format a8
col no format 99
col sid format 99999
col sql_id format a13
col stime format a15
col serial# format 9999999
col event format a25
col module format a12
col command format a6
col waiting_on format a50
col block format 99999
col TIME for 9999.99
col s for a1
prompt *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
prompt * < Active Session History > *
ACCEPT begin_time CHAR PROMPT "Enter the begin_time(YYYYMMDD/HH24/MI, default : 1 hour before) : "
ACCEPT end_time CHAR PROMPT "Enter the end_time (YYYYMMDD/HH24/MI, default : current time) : "
select session_id sid,
to_char(SAMPLE_TIME, 'YYMMDD HH24:MI:SS') STIME,
SQL_ID,
decode(SQL_OPCODE, '0', 'NO', '1', 'Create Tab', '2', 'Insert', '3', 'Select', '6', 'Update', '7'
, 'Delete', '9', 'Create Idx', '10', 'Drop Idx', '15', 'Alter Tbl', '24', 'Create Proc', '32', 'Create Link', '33'
, 'Drop Link', '36', 'Create RBS', '37', 'Alter RBS', '38', 'Drop RBS', '40', 'Alter TBS', '41', 'Drop TBS'
, '42', 'Alter Sess', '45', 'Rollback', '47', 'PL/SQL', '62', 'Anal Table', '63', 'Anal Index', '85', 'Truncate') Command,
substr(module, 1, 12) module,
decode(WAIT_TIME, 0, 'W', 'R') s,
TIME_WAITED/10000 TIME,
blocking_session block,
rtrim(event)||': '||(SELECT OBJECT_NAME
FROM DBA_OBJECTS
WHERE OBJECT_ID = CURRENT_OBJ#) as waiting_on
from v$active_session_history
where sample_time between nvl(to_timestamp('&begin_time', 'YYYYMMDD/HH24/MI'), sysdate-2/24) and nvl(to_timestamp('&end_time', 'YYYYMMDD/HH24/MI'), sysdate)
and event not like '%pmon timer%'
and event not like '%rdbms ipc%'
and event not like '%smon timer%'
and event not like '%SQL*Net message%'
and event not like '%lock manager wait for%'
and event not like '%slave wait%'
and event not like '%io done%'
and event not like '%queue messages%'
and event not like '%wakeup time%'
order by STIME, sid;
prompt *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
exit
2. v$session기준, Session 조회
set lines 300
set pages 700
set echo off
set feedback off
set verify off
set pause off
set timing off
col username format a10
col sid format 9999
col serial# format 999999
col spid format a8
col osuser format a8
col status format a8
col machine format a9
col module format a15
col command format a6
col program format a9
col event format a23
col l_read format 999999999
col s_ratio format 999
col time for 9999
col sql_id for a13
col hash_value for 9999999999
select /*+ ordered */ s.username, s.sid, s.serial#, p.spid, substr(s.osuser,1,8) osuser,
case
when s.command=1 then 'CREATE TABLE'
when s.command=2 then 'INSERT'
when s.command=3 then 'SELECT'
when s.command=6 then 'UPDATE'
when s.command=7 then 'DELETE'
when s.command=9 then 'CREATE INDEX'
when s.command=10 then 'DROP INDEX'
when s.command=11 then 'ALTER INDEX'
when s.command=12 then 'DROP TABLE'
when s.command=13 then 'CREATE SEQUENCE'
when s.command=14 then 'ALTER SEQUENCE'
when s.command=15 then 'ALTER TABLE'
when s.command=16 then 'DROP SEQUENCE'
when s.command=22 then 'DROP VIEW'
when s.command=24 then 'CREATE PROCEDURE'
when s.command=25 then 'ALTER PROCEDURE'
when s.command=28 then 'RENAME'
when s.command=39 then 'CREATE TABLESPACE'
when s.command=40 then 'ALTER TABLESPACE'
when s.command=41 then 'DROP TABLESPACE'
when s.command=42 then 'ALTER SESSION'
when s.command=44 then 'COMMIT'
when s.command=45 then 'ROLLBACK'
when s.command=46 then 'SAVEPOINT'
when s.command=47 then 'PL/SQL'
when s.command=48 then 'SET TRANSACTION'
when s.command=49 then 'ALTER SYSTEM'
when s.command=50 then 'EXPLAIN'
when s.command=62 then 'ANALYZE TABLE'
when s.command=63 then 'ANALYZE INDEX'
when s.command=64 then 'ANALYZE CLUSTER'
when s.command=71 then 'CREATE MATERIALIZED VIEW LOG'
when s.command=72 then 'ALTER MATERIALIZED VIEW LOG'
when s.command=73 then 'DROP MATERIALIZED VIEW LOG'
when s.command=74 then 'CREATE MATERIALIZED VIEW'
when s.command=75 then 'ALTER MATERIALIZED VIEW'
when s.command=76 then 'DROP MATERIALIZED VIEW'
when s.command=85 then 'TRUNCATE TABLE'
when s.command=86 then 'TRUNCATE CLUSTER'
when s.command=91 then 'CREATE FUNCTION'
when s.command=92 then 'ALTER FUNCTION'
when s.command=93 then 'DROP FUNCTION'
when s.command=94 then 'CREATE PACKAGE'
when s.command=95 then 'ALTER PACKAGE'
when s.command=96 then 'DROP PACKAGE'
when s.command=97 then 'CREATE PACKAGE BODY'
when s.command=98 then 'ALTER PACKAGE BODY'
when s.command=99 then 'DROP PACKAGE BODY'
ELSE 'UNKWON'
end command
,s.status, substr(s.event,1,23) event,
si.consistent_gets + si.block_gets L_READ,
-- substr(s.machine,1,8) machine,
substr(s.module,1,9) module,
s.last_call_et time,
s.sql_id
from v$session s, v$process p, v$sess_io si
where s.paddr= p.addr and s.status <> 'INACTIVE'
and s.sid=si.sid(+)
and s.type <> 'BACKGROUND'
and s.event not like 'jobq slave wait%'
and s.event not like 'single-task message%'
and s.event not like 'Streams AQ%'
and s.event not like 'pipe get%'
and s.event not like 'wakeup time manager%'
and s.event not like 'rdbms ipc message%'
and s.event not like 'queue messages%'
order by s.username asc, s.sid, s.serial# ;
|
