GV$ACTIVE_SESSION_HISTORY

SELECT
/*+ no_merge ordered use_nl (s, a) */ 
a.inst_id,               -- INST_ID NUMBER
s.sample_id,             -- SAMPLE_ID NUMBER
s.sample_time,           -- SAMPLE_TIME TIMESTAMP(3)
a.session_id,            -- SESSION_ID NUMBER
a.session_serial#,       -- SESSION_SERIAL# NUMBER
a.user_id,               -- USER_ID NUMBER
a.sql_id,                -- SQL_ID VARCHAR2(13)
a.sql_child_number,      -- SQL_CHILD_NUMBER NUMBER
a.sql_plan_hash_value,   -- SQL_PLAN_HASH_VALUE NUMBER
a.force_matching_signature, -- FORCE_MATCHING_SIGNATURE NUMBER
a.sql_opcode,            -- SQL_OPCODE NUMBER
a.service_hash,          -- SERVICE_HASH NUMBER
decode                   -- SESSION_TYPE VARCHAR2(10)
  (a.session_type, 1, 'FOREGROUND', 2, 'BACKGROUND', 'UNKNOWN'),
decode                   -- SESSION_STATE VARCHAR2(7)
  (a.wait_time, 0, 'WAITING', 'ON CPU'),
decode                   -- QC_SESSION_ID NUMBER
  (a.qc_session_id, 0, to_number (NULL), a.qc_session_id),
decode                   -- QC_INSTANCE_ID NUMBER
  (a.qc_session_id, 0, to_number (NULL), a.qc_instance_id),
(case                    -- BLOCKING_SESSION NUMBER
  when a.blocking_session between 4294967291 and 4294967295 then to_number (NULL) else a.blocking_session end),
(case                    -- BLOCKING_SESSION_STATUS VARCHAR2(11)
  when a.blocking_session = 4294967295 then 'UNKNOWN' when a.blocking_session = 4294967294 then 'GLOBAL' when a.blocking_session = 4294967293 then 'UNKNOWN' when a.blocking_session = 4294967292 then 'NO HOLDER' when a.blocking_session = 4294967291 then 'NOT IN WAIT' else 'VALID' end),
(case                    -- BLOCKING_SESSION_SERIAL# NUMBER
  when a.blocking_session between 4294967291 and 4294967295 then to_number (NULL) else a.blocking_session_serial# end),
decode                   -- EVENT VARCHAR2(64)
  (a.wait_time, 0, a.event, NULL),
decode                   -- EVENT_ID NUMBER
  (a.wait_time, 0, a.event_id, NULL),
decode                   -- EVENT# NUMBER
  (a.wait_time, 0, a.event#, NULL),
a.seq#,                  -- SEQ# NUMBER
a.p1text,                -- P1TEXT VARCHAR2(64)
a.p1,                    -- P1 NUMBER
a.p2text,                -- P2TEXT VARCHAR2(64)
a.p2,                    -- P2 NUMBER
a.p3text,                -- P3TEXT VARCHAR2(64)
a.p3,                    -- P3 NUMBER
decode                   -- WAIT_CLASS VARCHAR2(64)
  (a.wait_time, 0, a.wait_class, NULL),
decode                   -- WAIT_CLASS_ID NUMBER
  (a.wait_time, 0, a.wait_class_id, NULL),
a.wait_time,             -- WAIT_TIME NUMBER
a.time_waited,           -- TIME_WAITED NUMBER
a.xid,                   -- XID RAW(8)
a.current_obj#,          -- CURRENT_OBJ# NUMBER
a.current_file#,         -- CURRENT_FILE# NUMBER
a.current_block#,        -- CURRENT_BLOCK# NUMBER
a.program,               -- PROGRAM VARCHAR2(48)
a.module,                -- MODULE VARCHAR2(48)
a.action,                -- ACTION VARCHAR2(32)
a.client_id              -- CLIENT_ID VARCHAR2(64)
FROM
x$kewash s,
x$ash a 
WHERE
s.sample_addr = a.sample_addr and s.sample_id = a.sample_id and s.sample_time = a.sample_time
home |  up  |  mailbox