Dump ASH Data.

These are few ways you can look at the ASH data.

1) Generating ASH Report from Enterprise Manager
2) Querying  v$active_session_history
3) Generating ASH Report from sql ( ashrpt.sql or ashrpti.sql  in ORACLE_HOME\admin )
4) Dump ASH to a file .


The first three steps are pretty straightforward.
I would opt for first method , as it is available right there in OEM and then I can navigate to appropriate SQLs.

But , for others , we do have some options . ...

Let us see how method 4 works.

The following example dumps the ASH data for the past 5 minutes.


C:>sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump ashdump 5
Statement processed.

You can use the file to load into the table via SQL Loader or External Table and analyze the ASH data.

Contents of the trace file
--------------------------

Trace file D:\APP\ORACLE\diag\rdbms\dbdev\dbdev\trace\dbdev_ora_5592.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 
CPU                 : 2 - type 8664, 2 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:1835M/3839M, Ph+PgF:5607M/7677M
Instance name: dbdev
Redo thread mounted by this instance: 1
Oracle process number: 28
Windows thread id: 5592, image: ORACLE.EXE (SHAD)


*** 2011-12-02 09:53:33.615
*** SESSION ID:(24.6023) 2011-12-02 09:53:33.615
*** CLIENT ID:() 2011-12-02 09:53:33.615
*** SERVICE NAME:(dbdev.test.edu) 2011-12-02 09:53:33.615
*** MODULE NAME:(sqlplus.exe) 2011-12-02 09:53:33.615
*** ACTION NAME:() 2011-12-02 09:53:33.615
 
Processing Oradebug command 'setmypid'

*** 2011-12-02 09:53:33.615
Oradebug command 'setmypid' console output:

*** 2011-12-02 09:53:57.881
Processing Oradebug command 'dump ashdump 5'
ASH dump
<<>>
****************
SCRIPT TO IMPORT
****************
------------------------------------------
Step 1: Create destination table
------------------------------------------
CREATE TABLE ashdump AS
SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0
----------------------------------------------------------------
Step 2: Create the SQL*Loader control file as below
----------------------------------------------------------------
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                           
)                              
---------------------------------------------------
Step 3: Load the ash rows dumped in this trace file
---------------------------------------------------
sqlldr userid/password control=ashldr.ctl data= errors=1000000
---------------------------------------------------
<<>>
<<>>
####
2627103816,1,27387994,"12-02-2011 09:53:47.800000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59482,100,0,0,998719,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387993,"12-02-2011 09:53:46.801000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3213517201,58536,0,1,1,0,12920,4294967295,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387990,"12-02-2011 09:53:43.802000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,4078387448,58530,2,3,2,0,651,4294967295,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387977,"12-02-2011 09:53:30.771000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59466,100,0,0,999755,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387959,"12-02-2011 09:53:12.731000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59447,100,0,0,999253,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387943,"12-02-2011 09:52:56.717000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59431,100,0,0,1000095,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387942,"12-02-2011 09:52:55.716000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59430,100,0,0,998594,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387926,"12-02-2011 09:52:39.717000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59414,100,0,0,997838,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387909,"12-02-2011 09:52:22.719000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59393,100,0,0,999836,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387892,"12-02-2011 09:52:05.705000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59376,100,0,0,1001355,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387875,"12-02-2011 09:51:48.707000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59359,100,0,0,1000171,0,4294967291,0,3937105842,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387858,"12-02-2011 09:51:31.708000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59343,100,0,0,999867,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387841,"12-02-2011 09:51:14.697000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59325,100,0,0,999688,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387825,"12-02-2011 09:50:58.698000000",4,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3176176482,54366,5,1,1000,999949,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (DIA0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387824,"12-02-2011 09:50:57.698000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59308,100,0,0,1000193,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387814,"12-02-2011 09:50:47.683000000",4,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3176176482,54355,5,1,1000,999911,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (DIA0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387812,"12-02-2011 09:50:45.683000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3213517201,58069,0,1,1,0,11257,4294967295,0,3306949592,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387809,"12-02-2011 09:50:42.683000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3213517201,58065,0,15,1,0,428,4294967295,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387807,"12-02-2011 09:50:40.683000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59292,100,0,0,1000681,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387790,"12-02-2011 09:50:23.685000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59274,100,0,0,999428,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387773,"12-02-2011 09:50:06.686000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59258,100,0,0,999501,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387756,"12-02-2011 09:49:49.672000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59241,100,0,0,999596,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387750,"12-02-2011 09:49:43.672000000",4,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3176176482,54291,5,1,1000,1001875,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (DIA0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387739,"12-02-2011 09:49:32.659000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59225,100,0,0,1000266,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387722,"12-02-2011 09:49:15.641000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59207,100,0,0,999939,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387706,"12-02-2011 09:48:59.624000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59189,100,0,0,999878,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
<<>>

*** 2011-12-02 09:53:57.896
Oradebug command 'dump ashdump 5' console output:

Display Trace File's Name and Location using ORADEBUG

There are few ways to determine the trace file name and its location.

ORADEBUG is one of them.  See below for example.

SQL> oradebug setmypid

Statement processed.

SQL> alter database backup controlfile to trace;
Database altered.

SQL> oradebug tracefile_name
C:\APP\MOHIDEEN\diag\rdbms\zdb11g\zdb11g\trace\zdb11g_ora_8856.trc