UNDEFINE ashdump_user

GRANT SELECT ON sys.v_$event_name TO &&ashdump_user;
GRANT SELECT ON sys.v_$sqlcommand TO &&ashdump_user;
GRANT SELECT ON sys.dba_hist_toplevelcall_name TO &&ashdump_user;
GRANT SELECT ON sys.dba_hist_plan_operation_name TO &&ashdump_user;
GRANT SELECT ON sys.dba_hist_plan_option_name TO &&ashdump_user;

DROP TABLE &&ashdump_user..ashdump PURGE;

CREATE TABLE &&ashdump_user..ashdump 
AS 
SELECT * FROM sys.wrh$_active_session_history WHERE rownum < 0;

CREATE OR REPLACE VIEW &&ashdump_user..ashdump_v 
AS
SELECT to_number(NULL) inst_id, 
       a.sample_id, 
       a.sample_time, 
       to_char(NULL) is_awr_sample, 
       a.session_id, 
       a.session_serial#, 
       decode(a.session_type, 1, 'FOREGROUND', 'BACKGROUND') session_type, 
       a.flags, 
       a.user_id, 
       a.sql_id, 
       decode(bitand(a.flags, 16), NULL, NULL, 0, 'N', 'Y') is_sqlid_current, 
       decode(a.sql_child_number, 65535, -1, a.sql_child_number) sql_child_number,
       a.sql_opcode, 
       a.force_matching_signature, 
       decode(a.top_level_sql_id, NULL, a.sql_id, a.top_level_sql_id) top_level_sql_id, 
       decode(a.top_level_sql_id, NULL, a.sql_opcode, a.top_level_sql_opcode) top_level_sql_opcode, 
       c.command_name sql_opname,
       a.sql_plan_hash_value, 
       decode(a.sql_plan_operation#, 0, to_number(NULL), a.sql_plan_line_id) sql_plan_line_id, 
       p.operation_name sql_plan_operation,
       o.option_name sql_plan_options,
       decode(a.sql_exec_id, 0, to_number(NULL), a.sql_exec_id) sql_exec_id, 
       a.sql_exec_start, 
       decode(a.plsql_entry_object_id, 0, to_number(NULL), a.plsql_entry_object_id) plsql_entry_object_id,
       decode(a.plsql_entry_object_id, 0, to_number(NULL), a.plsql_entry_subprogram_id) plsql_entry_subprogram_id, 
       decode(a.plsql_object_id, 0, to_number(NULL), a.plsql_object_id) plsql_object_id, 
       decode(a.plsql_object_id, 0, to_number(NULL), a.plsql_subprogram_id) plsql_subprogram_id, 
       decode(a.qc_session_id, 0, to_number(NULL), a.qc_instance_id) qc_instance_id, 
       decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_id) qc_session_id, 
       decode(a.qc_session_id, 0, to_number(NULL), a.qc_session_serial#) qc_session_serial#, 
       decode(a.px_flags, 0, to_number(NULL), a.px_flags) px_flags, 
       decode(a.wait_time, 0, e.name,    NULL) event, 
       decode(a.wait_time, 0, a.event_id, NULL) event_id, 
       decode(a.wait_time, 0, e.event#,   NULL) event#, 
       a.seq#, 
       e.parameter1 p1text, 
       a.p1, 
       e.parameter2 p2text, 
       a.p2, 
       e.parameter3 p3text, 
       a.p3, 
       decode(a.wait_time, 0, e.wait_class, NULL) wait_class, 
       decode(a.wait_time, 0, e.wait_class_id, NULL) wait_class_id, 
       a.wait_time,
       decode(a.wait_time, 0, 'WAITING', 'ON CPU') session_state, 
       a.time_waited, 
       (case when a.blocking_session = 4294967295 then 'UNKNOWN' when a.blocking_session = 4294967294 then 'GLOBAL' when a.blocking_session = 4294967293 then 'UNKNOWN' when a.blocking_session = 4294967292 then 'NO HOLDER' when a.blocking_session = 4294967291 then 'NOT IN WAIT' else 'VALID' end) blocking_session_status, 
       (case when a.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else a.blocking_session end) blocking_session, 
       (case when a.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else a.blocking_session_serial# end) blocking_session_serial#, 
       (case when a.blocking_session between 4294967291 and 4294967295 then to_number(NULL) else a.blocking_inst_id  end) blocking_inst_id, 
       (case when a.blocking_session between 4294967291 and 4294967295 then NULL else decode(bitand(a.flags, 8), NULL, 'N', 0, 'N', 'Y') end) blocking_hangchain_info, 
       decode(a.current_obj#, 4294967295, -1, a.current_obj#) current_obj#, 
       a.current_file#, 
       a.current_block#,
       a.current_row#,
       a.top_level_call#, 
       t.top_level_call_name,
       decode(a.consumer_group_id, 0, to_number(NULL), a.consumer_group_id) consumer_group_id, 
       a.xid, 
       decode(a.remote_instance#, 0, to_number(NULL), a.remote_instance#) remote_instance#, 
       a.time_model, 
       decode(bitand(a.time_model, 8), 8, 'Y', 'N') in_connection_mgmt, 
       decode(bitand(a.time_model, 16), 16, 'Y', 'N') in_parse, 
       decode(bitand(a.time_model, 128), 128, 'Y', 'N') in_hard_parse,
       decode(bitand(a.time_model, 1024), 1024, 'Y', 'N') in_sql_execution, 
       decode(bitand(a.time_model, 2048), 2048, 'Y', 'N') in_plsql_execution,
       decode(bitand(a.time_model, 4096), 4096, 'Y', 'N') in_plsql_rpc, 
       decode(bitand(a.time_model, 8192), 8192, 'Y', 'N') in_plsql_compilation,
       decode(bitand(a.time_model, 16384), 16384, 'Y', 'N') in_java_execution,
       decode(bitand(a.time_model, 32768), 32768, 'Y', 'N') in_bind,
       decode(bitand(a.time_model, 65536), 65536, 'Y', 'N') in_cursor_close,
       decode(bitand(a.time_model, 131072), 131072, 'Y', 'N') in_sequence_load,
       decode(bitand(a.flags, 32), NULL, 'N', 0, 'N', 'Y') capture_overhead, 
       decode(bitand(a.flags, 64), NULL, 'N', 0, 'N', 'Y') replay_overhead, 
       decode(bitand(a.flags, 1), NULL, 'N', 0, 'N', 'Y') is_captured, 
       decode(bitand(a.flags, 4), NULL, 'N', 0, 'N', 'Y') is_replayed, 
       a.service_hash, 
       a.program, 
       a.module, 
       a.action, 
       a.client_id, 
       a.machine, 
       a.port, 
       a.ecid, 
       a.dbreplay_file_id, 
       a.dbreplay_call_counter, 
       decode(a.tm_delta_time, 0, to_number(NULL), a.tm_delta_time) tm_delta_time, 
       decode(a.tm_delta_time, 0, to_number(NULL), a.tm_delta_cpu_time) tm_delta_cpu_time, 
       decode(a.tm_delta_time, 0, to_number(NULL), a.tm_delta_db_time) tm_delta_db_time, 
       decode(a.delta_time, 0, to_number(NULL), a.delta_time) delta_time, 
       decode(a.delta_time, 0, to_number(NULL), a.delta_read_io_requests) delta_read_io_requests, 
       decode(a.delta_time, 0, to_number(NULL), a.delta_write_io_requests) delta_write_io_requests, 
       decode(a.delta_time, 0, to_number(NULL), a.delta_read_io_bytes) delta_read_io_bytes, 
       decode(a.delta_time, 0, to_number(NULL), a.delta_write_io_bytes) delta_write_io_bytes, 
       decode(a.delta_time, 0, to_number(NULL), a.delta_interconnect_io_bytes) delta_interconnect_io_bytes, 
       to_number(NULL) delta_read_mem_bytes, 
       decode(a.pga_allocated, 0, to_number(NULL), a.pga_allocated) pga_allocated, 
       decode(a.pga_allocated, 0, to_number(NULL), a.temp_space_allocated) temp_space_allocated, 
       a.con_dbid, 
       to_number(NULL) con_id, 
       a.dbop_name, 
       a.dbop_exec_id 
FROM &&ashdump_user..ashdump a, 
     sys.v_$event_name e, 
     sys.v_$sqlcommand c, 
     (SELECT DISTINCT /* AWR can contain data for several DBID */ top_level_call#, top_level_call_name FROM sys.dba_hist_toplevelcall_name) t,
     (SELECT DISTINCT /* AWR can contain data for several DBID */ operation_id, operation_name FROM sys.dba_hist_plan_operation_name) p,
     (SELECT DISTINCT /* AWR can contain data for several DBID */ option_id, option_name FROM sys.dba_hist_plan_option_name) o
WHERE a.event_id = e.event_id
AND a.sql_opcode = c.command_type(+)
AND a.top_level_call# = t.top_level_call#(+)
AND a.sql_plan_operation# = p.operation_id(+)
AND a.sql_plan_options# = o.option_id(+);

UNDEFINE ashdump_user

