vash.sql
ASHから時間指定でCSVファイルを出力する
code:SQL
-- @vash hhmm1 hhmm2
-- 例:@vash 1302 1325
-- v_ash_mmdd_1302-1325.csv が出力される(mmddは本日日付)
alter session set nls_timestamp_format='yyyy/mm/dd hh24:mi:ss';
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
set linesize 30000
set pagesize 50000
set colsep ','
set echo off
set feedback off
set verify off
set timing off
col fname new_value spool_file format a100
col tday1 new_value today1 format a20
col tday2 new_value today2 format a20
select 'v_ash_'||to_char(sysdate,'mmdd')||'_'||'&1'||'-'||'&2'||'.csv' fname from dual;
select to_char(to_date(to_char(sysdate,'yyyy/mm/dd')||' '||'&1'||':00','yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss') tday1 from dual;
select to_char(to_date(to_char(sysdate,'yyyy/mm/dd')||' '||'&2'||':00','yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss') tday2 from dual;
spool &spool_file
select * from gv$active_session_history where
SAMPLE_TIME >= to_timestamp('&today1') and
SAMPLE_TIME <= to_timestamp('&today2')
order by SAMPLE_TIME;
spool off
set colsep ' '
set echo on
set verify on
set timing on