ASH的DUMP-CSDN博客

v$active_session_history对于诊断系统HANG是有帮助的，但是系统HANG住后是无法查询的，而且，最后的最重要的这部分数据是在内存中的（内存中保存最近的30分钟的数据）
可以使用ASHDUMP来获得这部分内存中的数据

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump ashdump 10;
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/SOURCE/udump/source_ora_3585.trc

相关的文档：Doc ID: 243132.1 Doc ID: 555303.1
metalink上提供的是个sqlldr脚本，可以生成一个外部表的脚本用用

CREATE TABLE external_active_sess_history
(
"DBID" NUMBER,
"INSTANCE_NUMBER" NUMBER,
"SAMPLE_ID" NUMBER,
"SAMPLE_TIME" VARCHAR(255),
"SESSION_ID" NUMBER,
"SESSION_SERIAL#" NUMBER,
"USER_ID" NUMBER,
"SQL_ID" VARCHAR2(13),
"SQL_CHILD_NUMBER" NUMBER,
"SQL_PLAN_HASH_VALUE" NUMBER,
"SERVICE_HASH" NUMBER,
"SESSION_TYPE" VARCHAR2(10),
"SQL_OPCODE" NUMBER,
"PLSQL_ENTRY_OBJECT_ID" NUMBER,
"PLSQL_ENTRY_SUBPROGRAM_ID" NUMBER,
"PLSQL_OBJECT_ID" NUMBER,
"PLSQL_SUBPROGRAM_ID" NUMBER,
"BLOCKING_SESSION" NUMBER,
"BLOCKING_SESSION_SERIAL#" NUMBER,
"QC_SESSION_ID" NUMBER,
"QC_INSTANCE_ID" NUMBER,
"XID" RAW(8),
"CURRENT_OBJ#" NUMBER,
"CURRENT_FILE#" NUMBER,
"CURRENT_BLOCK#" NUMBER,
"EVENT_ID" NUMBER,
"SEQ#" NUMBER,
"P1" NUMBER,
"P2" NUMBER,
"P3" NUMBER,
"WAIT_TIME" NUMBER,
"TIME_WAITED" NUMBER,
"FORCE_MATCHING_SIGNATURE" NUMBER,
"PROGRAM" VARCHAR2(64),
"MODULE" VARCHAR2(48),
"ACTION" VARCHAR2(32),
"CLIENT_ID" VARCHAR2(64)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY DUMP_DIR
ACCESS PARAMETERS
(
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    READSIZE 1048576   nobadfile nologfile nodiscardfile
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
"DBID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"INSTANCE_NUMBER" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SAMPLE_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SAMPLE_TIME" CHAR(255) TERMINATED BY "," ENCLOSED BY '"' AND '"' DATE_FORMAT TIMESTAMP MASK "MM-DD-YYYYHH24:MI:SSXFF",
"SESSION_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SESSION_SERIAL#" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"USER_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SQL_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SQL_CHILD_NUMBER" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SQL_PLAN_HASH_VALUE" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SERVICE_HASH" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SESSION_TYPE" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SQL_OPCODE" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"PLSQL_ENTRY_OBJECT_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"PLSQL_ENTRY_SUBPROGRAM_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"PLSQL_OBJECT_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"PLSQL_SUBPROGRAM_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"BLOCKING_SESSION" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"BLOCKING_SESSION_SERIAL#" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"QC_SESSION_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"QC_INSTANCE_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"XID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CURRENT_OBJ#" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CURRENT_FILE#" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CURRENT_BLOCK#" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"EVENT_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"SEQ#" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"P1" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"P2" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"P3" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"WAIT_TIME" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"TIME_WAITED" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"FORCE_MATCHING_SIGNATURE" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"PROGRAM" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"MODULE" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"ACTION" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"CLIENT_ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
)
location ('source_ora_3585.trc')
)REJECT LIMIT UNLIMITED
/