tshizuku03の日記

主にoracleやLinuxコマンドについての記事を載せていきたいと思っています。

V$ACTIVE_SESSION_HISTORY取得

set linesize 10000
set pagesize 1000
set trimspool on
set long 75
col sample_time for a25
col event for a60
col user_name for a20
col sql_text for a80
select
dhash.inst_id inst_id
, dhash.sample_time sample_time
, dhash.session_id
, dhash.time_waited time_waited
, dhash.event event
, dhash.user_id user_id
, (select username from dba_users where user_id = dhash.user_id) user_name
, dhash.sql_id sql_id
, (select sql_text from gv$sqltext where inst_id = dhash.inst_id and sql_id = dhash.sql_id and piece = 0 and rownum = 1) sql_text
--, dhash.p1 p1
--, dhash.p2 p2
--, dhash.p3 p3
from gv$active_session_history dhash
where to_date('yyyymmddhh24mi', 'yyyymmddhh24mi') <= sample_time
and sample_time < to_date('yyyymmddhh24mi', 'yyyymmddhh24mi')

 Oracleマニュアル

V$SESSION

V$ACTIVE_SESSION_HISTORY