Oracle Active Session dump and loader

Advanced ASH Usage
ASH Dump data

SQL> alter system set events 'immediate trace name ashdump level 10';
SQL> oradebug setmypid
SQL> oradebug dump ashdump 10;


SQL> oradebug unlimit

SQL> oradebug setmypid
Statement processed.

SQL> oradebug dump ashdump 10;
Statement processed.

or

SQL> alter session set events 'immediate trace name ashdump level 10';




SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc


it's structure is slightly different than that of the V$ACTIVE_SESSION_HISTORY view).
So, here is the DDL to create the ashdump table:



Drop table ashdump;


SQL> connect system/sys
Connected.

SQL>


========================


Release 11.2.0.1.0

SQL> CREATE TABLE ashdump AS SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0 ----<<<<<<<<<<<<<< Table created.




sqlldr userid=system/sys control='/home/oracle/ashldr.ctl' data='/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc'

Release 11.2.0.1.0





control file ashldr.ctl <<<<<<

load data
infile * "str '\n####\n'"
append
into table ashdump
fields terminated by ',' optionally enclosed by '"'
(
SNAP_ID CONSTANT 0 ,
DBID ,
INSTANCE_NUMBER ,
SAMPLE_ID ,
SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME ,'MM-DD-YYYY HH24:MI:SSXFF')" ,
SESSION_ID ,
SESSION_SERIAL# ,
SESSION_TYPE ,
USER_ID ,
SQL_ID ,
SQL_CHILD_NUMBER ,
SQL_OPCODE ,
FORCE_MATCHING_SIGNATURE ,
TOP_LEVEL_SQL_ID ,
TOP_LEVEL_SQL_OPCODE ,
SQL_PLAN_HASH_VALUE ,
SQL_PLAN_LINE_ID ,
SQL_PLAN_OPERATION# ,
SQL_PLAN_OPTIONS# ,
SQL_EXEC_ID ,
SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START" ,
PLSQL_ENTRY_OBJECT_ID ,
PLSQL_ENTRY_SUBPROGRAM_ID ,
PLSQL_OBJECT_ID ,
PLSQL_SUBPROGRAM_ID ,
QC_INSTANCE_ID ,
QC_SESSION_ID ,
QC_SESSION_SERIAL# ,
EVENT_ID ,
SEQ# ,
P1 ,
P2 ,
P3 ,
WAIT_TIME ,
TIME_WAITED ,
BLOCKING_SESSION ,
BLOCKING_SESSION_SERIAL# ,
BLOCKING_INST_ID ,
CURRENT_OBJ# ,
CURRENT_FILE# ,
CURRENT_BLOCK# ,
CURRENT_ROW# ,
TOP_LEVEL_CALL# ,
CONSUMER_GROUP_ID ,
XID ,
REMOTE_INSTANCE# ,
TIME_MODEL ,
SERVICE_HASH ,
PROGRAM ,
MODULE ,
ACTION ,
CLIENT_ID ,
MACHINE ,
PORT ,
ECID
)



SQL> oradebug tracefile_name
/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc





Having created the table, you will need to edit the trace file before you can actually load it, removing the header and footer records (or you can change the ashldr.ctl file, whichever you prefer). After removing the header, you then use SQL*Loader to load the trace file contents into the physical file as seen in this example:


edit this file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc

delete the line


oracle@apt-amd-02:~> sqlldr userid=system/sys control='/home/oracle/ashldr.ctl' data='/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_30527.trc'

SQL*Loader: Release 11.2.0.1.0 - Production on Mon Sep 12 15:15:24 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 18
Commit point reached - logical record count 27