ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Undocumented internal package supporting Active Session History (ASH).
As creating ASH reports requires licensing of the appropriate OEM pack any testing demos on this page should assume that this licensing may also required for running code referenced these demos.
dbms_ash_internal.ash_rollup_sql_1d(
select_list IN VARCHAR2,
dimension IN VARCHAR2,
xtra_select_list IN VARCHAR2,
predicate IN VARCHAR2,
join_with IN VARCHAR2,
join_predicate IN VARCHAR2,
min_perc IN NUMBER,
max_rank IN NUMBER,
is_awr_selection IN BOOLEAN,
is_global_awr IN BOOLEAN)
RETURN VARCHAR2;
dbms_ash_internal.ash_rollup_sql_2d(
select_list IN VARCHAR2,
dimension1 IN VARCHAR2,
dimension2 IN VARCHAR2,
xtra_select_list IN VARCHAR2,
predicate IN VARCHAR2,
join_with IN VARCHAR2,
join_predicate IN VARCHAR2,
min_d1_perc IN NUMBER,
max_d1_rank IN NUMBER
min_d12_perc IN NUMBER,
max_d12_rank IN NUMBER,
d12_min_cnt IN NUMBER,
is_awr_selection IN BOOLEAN,
is_global_awr IN BOOLEAN)
RETURN VARCHAR2;
dbms_ash_internal.ash_rollup_sql_3d(
select_list IN VARCHAR2,
dimension1 IN VARCHAR2,
dimension2 IN VARCHAR2,
dimension3 IN VARCHAR2,
xtra_select_list IN VARCHAR2,
predicate IN VARCHAR2,
join_with IN VARCHAR2,
join_predicate IN VARCHAR2,
min_d1_perc IN NUMBER,
max_d1_rank IN NUMBER
min_d12_perc IN NUMBER,
max_d12_rank IN NUMBER,
min_d123_perc IN NUMBER,
max_d123_rank IN NUMBER,
d123_min_cnt IN NUMBER,
is_awr_selection IN BOOLEAN,
is_global_awr IN BOOLEAN)
RETURN VARCHAR2;
Output, at right, partially reformatted for legibility
dbms_ash_internal.ash_sql_for_awr(is_global IN BOOLEAN) RETURN VARCHAR2;
DECLARE
b BOOLEAN := FALSE;
retVal VARCHAR2(4000);
BEGIN
retVal := dbms_ash_internal.ash_sql_for_awr(b);
dbms_output.put_line(retVal);
END;
/
SELECT ash.* FROM (SELECT a.dbid, a.instance_number, a.snap_id, nvl(a.con_id,-1) as con_id, a.con_dbid, nvl(p.pdb_name, 'UNKNOWN DBID ' || a.con_dbid) as
pdb_name, a.sample_id, a.sample_time, a.session_id, a.session_serial#, a.session_type, a.session_state, a.flags, a.user_id, a.sql_id, a.sql_opcode, a.sql_opname, a.top_level_sql_id, a.sql_child_number, a.sql_plan_hash_value, nvl(a.sql_full_plan_hash_value,a.sql_plan_hash_value) as
full_plan_hash_value, a.sql_plan_line_id, a.sql_plan_operation, a.sql_plan_options, a.sql_exec_id, a.force_matching_signature, a.plsql_entry_object_id, a.plsql_entry_subprogram_id, a.plsql_object_id, a.plsql_subprogram_id, a.service_hash, a.qc_session_id, a.qc_instance_id,a.qc_session_serial#,
nvl(a.event, CASE WHEN bitand(a.time_model, power(2,19)) > 0 THEN 'CPU: IM Populate'
WHEN bitand(a.time_model, power(2,20)) > 0 THEN 'CPU: IM Prepopulate'
WHEN bitand(a.time_model, power(2,21)) > 0 THEN 'CPU: IM Repopulate'
WHEN bitand(a.time_model, power(2,22)) > 0 THEN 'CPU: IM Trickle Repop'
WHEN bitand(a.time_model, power(2,18)) > 0 THEN 'CPU: IM Query'
ELSE 'CPU + Wait for CPU' END) as event,
nvl(a.event_id, 1) as event_id,
nvl(a.wait_class, 'CPU') as wait_class,
nvl(a.wait_class_id, 9999) as wait_class_id,
a.seq#, a.p1, a.p1text, a.p2, a.p2text, a.p3, a.p3text, a.wait_time, a.time_waited, a.xid, a.blocking_session, a.blocking_session_serial#, a.blocking_session_status, a.blocking_inst_id, a.current_obj#, a.current_file#, a.current_block#, a.top_level_call#, a.top_level_call_name, a.program, a.module, a.action, a.client_id, a.remote_instance#, a.in_connection_mgmt, a.in_parse, a.in_hard_parse, a.in_sql_execution, a.in_plsql_execution, a.in_plsql_rpc, a.in_plsql_compilation, a.in_java_execution, a.in_bind, a.in_cursor_close, a.in_sequence_load
FROM $#CDB#$VND$ACTIVE_SESS_HISTORY a, (
SELECT /*+ NO_MERGE */ dbid, con_dbid, substr(x,20) as pdb_name, instance_number
FROM (
SELECT i.dbid, i.con_dbid, i.instance_number, max(to_char(i.OPEN_TIME, 'YYYY:MM:DD
HH24:MI:SS') || i.PDB_NAME) as x
FROM $#CDB#$VND$pdb_instance i
WHERE i.con_dbid <> 0
GROUP BY i.dbid, i.con_dbid, i.instance_number)) p
WHERE 1=1
AND a.con_dbid = p.con_dbid(+)
AND a.dbid = p.dbid(+)
AND a.instance_number = p.instance_number(+)) ash
WHERE ash.dbid = :dbid
AND ash.snap_id > :bid
AND ash.snap_id <= :eid
AND ash.instance_number = :inst_num
SELECT dbms_ash_internal.ash_view_sql
FROM dual;
SELECT dbms_ash_internal.ash_view_sql
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_ASH_INTERNAL", line 460
DECLARE
retVal VARCHAR2(32767);
BEGIN
retVal := dbms_ash_internal.ash_view_sql;
dbms_output.put_line(retVal);
END;
/ SELECT unified_ash.* FROM ( ( SELECT :local_awrdbid as dbid, inst.instance_number, 0 as snap_id, a.con_id, a.con_dbid,
nvl(p.name, 'UNKNOWN DBID ' || a.con_dbid) as pdb_name, a.sample_id, a.sample_time, a.session_id, a.session_serial#, a.session_type, a.session_state, a.flags, a.user_id, a.sql_id, a.sql_opcode,a.sql_opname, a.top_level_sql_id, a.sql_child_number, a.sql_plan_hash_value, a.sql_full_plan_hash_value, a.sql_plan_line_id, a.sql_plan_operation, a.sql_plan_options, a.sql_exec_id, a.force_matching_signature, a.plsql_entry_object_id, a.plsql_entry_subprogram_id, a.plsql_object_id, a.plsql_subprogram_id, a.service_hash, a.qc_session_id,a.qc_instance_id,a.qc_session_serial#, nvl(a.event, CASE WHEN
bitand(a.time_model, power(2,19)) > 0 THEN 'CPU: IM Populate' WHEN bitand(a.time_model, power(2,20)) > 0 THEN 'CPU: IM Prepopulate' WHEN bitand(a.time_model, power(2,21)) > 0 THEN 'CPU: IM Repopulate' WHEN bitand(a.time_model, power(2,22)) > 0
THEN 'CPU: IM Trickle Repop' WHEN bitand(a.time_model, power(2,18)) > 0 THEN 'CPU: IM Query' ELSE 'CPU + Wait for CPU' END) as event, nvl(a.event_id, 1) as event_id, nvl(a.wait_class, 'CPU') as wait_class, nvl(a.wait_class_id, 9999) as wait_class_id, a.seq#, a.p1, a.p1text, a.p2, a.p2text, a.p3, a.p3text, a.wait_time, a.time_waited, a.xid, a.blocking_session, a.blocking_session_serial#, a.blocking_session_status, a.blocking_inst_id, a.current_obj#, a.current_file#, a.current_block#, a.top_level_call#, a.top_level_call_name, a.program, a.module, a.action, a.client_id, a.remote_instance#, a.in_connection_mgmt, a.in_parse, a.in_hard_parse, a.in_sql_execution, a.in_plsql_execution, a.in_plsql_rpc, a.in_plsql_compilation, a.in_java_execution, a.in_bind, a.in_cursor_close, a.in_sequence_load FROM SYS.V_$ACTIVE_SESSION_HISTORY
a, SYS.V_$INSTANCE inst, SYS.V_$CONTAINERS p WHERE 1=1 AND a.con_dbid =
p.dbid(+) and :ash_enable_mem_view = 1 and :ash_enable_disk_view = 0 and
a.sample_time between :ash_mem_btime and :ash_mem_etime ) UNION ALL (SELECT :local_awrdbid as dbid,
inst.instance_number, 0 as snap_id, a.con_id, a.con_dbid, nvl(p.name, 'UNKNOWN DBID ' || a.con_dbid) as pdb_name, a.sample_id, a.sample_time, a.session_id, a.session_serial#, a.session_type, a.session_state, a.flags, a.user_id, a.sql_id, a.sql_opcode,a.sql_opname, a.top_level_sql_id, a.sql_child_number, a.sql_plan_hash_value, a.sql_full_plan_hash_value, a.sql_plan_line_id, a.sql_plan_operation, a.sql_plan_options, a.sql_exec_id, a.force_matching_signature, a.plsql_entry_object_id, a.plsql_entry_subprogram_id, a.plsql_object_id, a.plsql_subprogram_id, a.service_hash,
a.qc_session_id,a.qc_instance_id,a.qc_session_serial#, nvl(a.event, CASE
WHEN bitand(a.time_model, power(2,19)) > 0 THEN 'CPU: IM Populate' WHEN
bitand(a.time_model, power(2,20)) > 0 THEN 'CPU: IM Prepopulate' WHEN
bitand(a.time_model, power(2,21)) > 0 THEN 'CPU: IM Repopulate' WHEN
bitand(a.time_model, power(2,22)) > 0 THEN 'CPU: IM Trickle Repop' WHEN
bitand(a.time_model, power(2,18)) > 0 THEN 'CPU: IM Query' ELSE 'CPU +
Wait for CPU' END) as event, nvl(a.event_id, 1) as event_id,
nvl(a.wait_class, 'CPU') as wait_class, nvl(a.wait_class_id, 9999) as
wait_class_id, a.seq#, a.p1, a.p1text, a.p2, a.p2text, a.p3, a.p3text,
a.wait_time, a.time_waited, a.xid, a.blocking_session,
a.blocking_session_serial#, a.blocking_session_status, a.blocking_inst_id,
a.current_obj#, a.current_file#, a.current_block#, a.top_level_call#, a.top_level_call_name, a.program, a.module, a.action, a.client_id, a.remote_instance#, a.in_connection_mgmt, a.in_parse, a.in_hard_parse, a.in_sql_execution, a.in_plsql_execution, a.in_plsql_rpc, a.in_plsql_compilation, a.in_java_execution, a.in_bind, a.in_cursor_close, a.in_sequence_load FROM SYS.V_$ACTIVE_SESSION_HISTORY a, SYS.V_$INSTANCE inst, SYS.V_$CONTAINERS p WHERE 1=1 AND a.con_dbid = p.dbid(+)
and :ash_enable_mem_view = 1 and :ash_enable_disk_view = 1 and a.is_awr_sample = 'Y' and a.sample_time between :ash_mem_btime and :ash_mem_etime ) UNION ALL ( SELECT a.dbid, a.instance_number, a.snap_id, nvl(a.con_id,-1) as con_id, a.con_dbid, nvl(p.pdb_name, 'UNKNOWN DBID ' || a.con_dbid) as pdb_name, a.sample_id, a.sample_time, a.session_id, a.session_serial#, a.session_type, a.session_state, a.flags,
a.user_id, a.sql_id, a.sql_opcode, a.sql_opname, a.top_level_sql_id, a.sql_child_number, a.sql_plan_hash_value, nvl(a.sql_full_plan_hash_value,a.sql_plan_hash_value) as full_plan_hash_value, a.sql_plan_line_id, a.sql_plan_operation, a.sql_plan_options,
a.sql_exec_id, a.force_matching_signature, a.plsql_entry_object_id, a.plsql_entry_subprogram_id, a.plsql_object_id, a.plsql_subprogram_id, a.service_hash, a.qc_session_id,a.qc_instance_id,a.qc_session_serial#, nvl(a.event, CASE WHEN bitand(a.time_model, power(2,19)) > 0 THEN 'CPU: IM Populate' WHEN bitand(a.time_model, power(2,20)) > 0 THEN 'CPU: IM Prepopulate' WHEN bitand(a.time_model,
power(2,21)) > 0 THEN 'CPU: IM Repopulate' WHEN bitand(a.time_model, power(2,22)) > 0 THEN 'CPU: IM Trickle Repop' WHEN bitand(a.time_model, power(2,18)) > 0 THEN 'CPU: IM Query' ELSE 'CPU + Wait for CPU' END) as event, nvl(a.event_id, 1) as event_id,
nvl(a.wait_class, 'CPU') as wait_class, nvl(a.wait_class_id, 9999) as wait_class_id, a.seq#, a.p1, a.p1text, a.p2, a.p2text, a.p3, a.p3text, a.wait_time, a.time_waited, a.xid, a.blocking_session, a.blocking_session_serial#, a.blocking_session_status, a.blocking_inst_id, a.current_obj#, a.current_file#, a.current_block#, a.top_level_call#, a.top_level_call_name, a.program, a.module, a.action, a.client_id, a.remote_instance#,
a.in_connection_mgmt, a.in_parse, a.in_hard_parse, a.in_sql_execution,
a.in_plsql_execution, a.in_plsql_rpc, a.in_plsql_compilation,
a.in_java_execution, a.in_bind, a.in_cursor_close, a.in_sequence_load FROM
$#CDB#$VND$ACTIVE_SESS_HISTORY a, (SELECT /*+ NO_MERGE */ dbid, con_dbid,
substr(x,20) as pdb_name, instance_number FROM ( SELECT i.dbid,
i.con_dbid, i.instance_number, max(to_char(i.OPEN_TIME, 'YYYY:MM:DD
HH24:MI:SS') || i.PDB_NAME) as x FROM $#CDB#$VND$pdb_instance i WHERE
i.con_dbid <> 0 GROUP BY i.dbid, i.con_dbid, i.instance_number)) p WHERE
1=1 AND a.con_dbid = p.con_dbid(+) AND a.dbid = p.dbid(+) AND
a.instance_number = p.instance_number(+) and :ash_enable_disk_view = 1 and
a.sample_time between :ash_disk_btime and
:ash_disk_etime ) ) unified_ash WHERE dbid = :dbid AND (instance_number
MEMBER OF :inst_num) AND ((snap_id = 0) OR (snap_id between :bid and
:eid)) AND sample_time between :ash_begin_time and :ash_end_time AND (:ash_num_samples
= :ash_num_samples) AND (:ash_sid IS NULL OR
session_id = :ash_sid OR (qc_session_id = :ash_sid AND qc_instance_id
MEMBER OF :inst_num) ) AND (:ash_sql_id IS NULL OR sql_id like
:ash_sql_id) AND (:ash_wait_class IS NULL OR wait_class like :ash_wait_class) AND (:ash_service_hash IS NULL OR service_hash = :ash_service_hash) AND
(:ash_module IS NULL OR module like :ash_module) AND (:ash_action IS NULL OR action like :ash_action) AND (:ash_client_id IS NULL OR client_id
like :ash_client_id) AND (:ash_plsql_entry IS NULL OR ( (unified_ash.con_id, unified_ash.dbid, unified_ash.plsql_entry_object_id,
unified_ash.plsql_entry_subprogram_id) IN (select plsname1.con_id,wr.local_awrdbid, object_id, subprogram_id from ( select con_id, object_id,
subprogram_id, object_type, owner, object_name, procedure_name, overload from sys.cdb_procedures ) plsname1, $#CDB#$VND$wr_settings wr where
sys.dbms_ash_internal.format_plsql(plsname1.owner, plsname1.object_name, plsname1.object_type,
plsname1.procedure_name, plsname1.overload) like :ash_plsql_entry and unified_ash.con_id =
plsname1.con_id))) AND (:ash_container IS NULL OR pdb_name like :ash_container)
dbms_ash_internal.build_database_header_tag(
dbid IN NUMBER,
dbname IN VARCHAR2,
dbversion IN VARCHAR2,
dbparallel IN VARCHAR2,
instid IN NUMBER,
instname IN VARCHAR2,
hostname IN VARCHAR2,
xtra IN XMLTYPE)
RETURN XMLTYPE;
dbms_ash_internal.build_service_module_tag(
service_hash IN NUMBER,
service_name IN VARCHAR2,
module_name IN VARCHAR2,
ash_count IN NUMBER,
xtra IN XMLTYPE);
RETURN XMLTYPE;
CREATE TABLE xtab(
xval XMLTYPE);
SELECT name_hash, name
FROM v$active_services;
ORDER BY 1;
DECLARE
xOut XMLTYPE;
BEGIN
xOut := dbms_ash_internal.build_service_module_tag(2477889254, 'pdbdev', 'TEST_MOD',42, NULL);
INSERT INTO xtab VALUES (xOut);
END;
/
dbms_ash_internal.build_session_tag(
session_id IN NUMBER,
session_serial IN NUMBER,
user_name IN VARCHAR2,
program_name IN VARCHAR2,
ash_num_transactions IN NUMBER,
ash_count IN NUMBER,
xtra IN XMLTYPE)
RETURN XMLTYPE;
CREATE TABLE xtab(
xval XMLTYPE);
SELECT sid, serial#
FROM v$session
WHERE sid IN (
SELECT sid
FROM v$mystat
WHERE rownum = 1);
SID SERIAL#
----- ----------
368 131
DECLARE
xOut XMLTYPE;
BEGIN
xOut := dbms_ash_internal.build_session_tag(368, 131, 'DAMORGAN', 'MLIB', 1, 1, NULL);
INSERT INTO xtab VALUES (xOut);
END;
/
dbms_ash_internal.build_sql_tag(
sql_id IN VARCHAR2,
planhash IN NUMBER,
ash_num_executions IN NUMBER,
ash_count IN NUMBER,
xtra IN XMLTYPE)
RETURN XMLTYPE;
CREATE TABLE xtab(xval XMLTYPE);
SELECT sql_id, plan_hash_value
FROM v$sqlarea
WHERE rownum = 1;
dbms_ash_internal.format_plsql
owner IN VARCHAR2,
object_name IN VARCHAR2,
object_type IN VARCHAR2,
subprogram_name IN VARCHAR2,
overload IN VARCHAR2)
RETURN VARCHAR2;
dbms_ash_internal.get_slot_width(usedisk IN NUMBER) RETURN NUMBER;
DECLARE
vNum NUMBER;
BEGIN
FOR i IN -1000 .. 10000 LOOP
vNum := dbms_ash_internal.get_slot_width(i);
IF vNum IS NOT NULL THEN
dbms_output.put_line(TO_CHAR(vNum));
END IF;
END LOOP;
END;
/
-- runs but no output
dbms_ash_internal.get_sqltext(
sqlid IN VARCHAR2,
chnum IN NUMBER)
RETURN CLOB;
conn sys@pdbdev as sysdba
SELECT /* DEMO1 */ COUNT(*)
FROM dba_all_tables;
SELECT sa.sql_id, substr(sa.sql_text,1,20) SQLDML, sp.child_number
FROM v$sqlarea sa, v$sql_plan sp
WHERE sa.sql_id = sp.sql_id
AND sa.sql_text LIKE '%DEMO1%';
-- take a SQL_ID from the above SELECT and plug it in
DECLARE
cOut CLOB;
BEGIN
cOut := dbms_ash_internal.get_sqltext('1kqp9m8y4vjv6', 0);
dbms_output.put_line(cOut);
END;
/
dbms_ash_internal.initialize(
dbid IN NUMBER,
instnum IN awrrpt_instance_list_type,
bsnap IN NUMBER,
esnap IN NUMBER,
btime IN DATE,
etime IN DATE,
nsmpls IN NUMBER,
dfltr IN NUMBER,
swidth IN NUMBER,
m_en IN NUMBER,
m_btime IN DATE,
m_etime IN DATE,
d_en IN NUMBER
d_btime IN DATE,
d_etime IN DATE,
data_src IN NUMBER);
SELECT dbms_ash_internal.on_disk_ash_view_sql
FROM dual;
ON_DISK_ASH_VIEW_SQL
--------------------------------------------------------------------------------
SELECT a.dbid, a.instance_number, a.snap_id, nvl(a.con_id,-1) as con_id,
a.con_dbid, nvl(p.pdb_name, 'UNKNOWN DBID ' || a.con_dbid) as
pdb_name, a.sample_id, a.sample_time, a.session_id, a.session_seri
al#, a.session_type, a.session_state, a.flags, a.user_id,
a.sql_id, a.sql_opcode, a.sql_opname, a.top_level_sql_id, a.
sql_child_number, a.sql_plan_hash_value, nvl(a.sql_full_plan_hash_value,a
.sql_plan_hash_value) as full_plan_hash_value, a.sql_plan_line_id,
a.sql_plan_operation, a.sql_plan_options, a.sql_exec_id, a.
force_matching_signature, a.plsql_entry_object_id, a.plsql_entry_subprogr
am_id, a.plsql_object_id, a.plsql_subprogram_id, a.service_hash,
a.qc_session_id,a.qc_instance_id,a.qc_session_serial#, nvl(a.event,
CASE WHEN bitand(a.time_model, power(2,19)) > 0 THEN
'CPU: IM Populate' WHEN bitand(a.time_model, power(2,20))
> 0 THEN 'CPU: IM Prepopulate' WHEN bitand(
a.time_model, power(2,21)) > 0 THEN 'CPU: IM Repopulate'
WHEN bitand(a.time_model, power(2,22)) > 0 THEN 'C
PU: IM Trickle Repop' WHEN bitand(a.time_model, power(2,18)) >
0 THEN 'CPU: IM Query' ELSE 'CPU + Wait fo
r CPU' END) as event, nvl(a.event_id, 1) as event_id, nvl(a.wait_c
lass, 'CPU') as wait_class, nvl(a.wait_class_id, 9999) as wait_class_id,
a.seq#, a.p1, a.p1text, a.p2, a.p2text, a.p3, a.p3te
xt, a.wait_time, a.time_waited, a.xid, a.blocking_session,
a.blocking_session_serial#, a.blocking_session_status, a.blocking_inst_id
, a.current_obj#, a.current_file#, a.current_block#, a.top_level_c
all#, a.top_level_call_name, a.program, a.module, a.action, a.client_id,
a.remote_instance#, a.in_connection_mgmt, a.in_parse,
a.in_hard_parse, a.in_sql_execution, a.in_plsql_execution,
a.in_plsql_rpc, a.in_plsql_compilation, a.in_java_execution,
a.in_bind, a.in_cursor_close, a.in_sequence_load FROM $#CDB#
$VND$ACTIVE_SESS_HISTORY a, (SELECT dbid, con_dbid, substr(x
,20) as pdb_name, instance_number FROM ( SELECT i.dbid,
i.con_dbid, i.instance_number, ma
x(to_char(i.OPEN_TIME, 'YYYY:MM:DD HH24:MI:SS')
|| i.PDB_NAME) as x FROM $#CDB#$VND$pdb_
instance i, $#CDB#$VND$snapshot s WHERE s.dbid = i.dbid
AND s.instance_number = i.instance_number AND i.startup
_time >= s.startup_time AND i.con_dbid <> 0 GROUP
BY i.dbid, i.con_dbid, i.instance_number)) p WHERE 1=1
AND a.con_dbid = p.con_dbid(+) AND a.dbid = p.dbid(+) AND a.instance_numb
er = p.instance_number(+);
dbms_ash_internal.report_ashviewer_xml(
report_reference IN VARCHAR2,
dbid IN NUMBER,
inst_id IN NUMBER,
begin_time IN VARCHAR2,
end_time IN VARCHAR2,
report_level IN VARCHAR2,
filter_list IN VARCHAR2,
time_since_sec IN VARCHAR2,
show_sql IN VARCHAR2,
parent_report IN VARCHAR2)
RETURN XMLTYPE;
dbms_ash_internal.runreport(
dbid IN NUMBER,
inst_id IN NUMBER,
begin_time IN VARCHAR2,
end_time IN VARCHAR2,
report_level IN VARCHAR2,
filter_list IN VARCHAR2,
out_xml OUT CLOB,
report_reference IN VARCHAR2,
time_since_sec IN VARCHAR2,
show_sql IN VARCHAR2,
parent_report IN VARCHAR2);
SELECT dbms_ash_internal.unified_dba_files
FROM dual;
UNIFIED_DBA_FILES
-------------------------------------------------------------------------
SELECT file# as file_id, max(filename) as file_name, max(tsname) as
tablespace_name
FROM $#CDB#$VND$datafile WHERE dbid = :dbid GROUP BY file#
SELECT dbms_ash_internal.unified_dba_objects
FROM dual;
UNIFIED_DBA_OBJECTS
------------------------------------------------------------
SELECT o.con_id, o.owner, o.object_id,
o.object_name, o.subobject_name,
o.object_type,
'N/A' as tablespace_name
FROM cdb_objects o, $#CDB#$VND$wr_settings d
WHERE d.local_awrdbid = :dbid
UNION ALL
SELECT nvl(o.con_id,-1) as con_id,
max(owner) as owner,
obj# as object_id,
max(object_name) as object_name,
max(subobject_name) as subobject_name,
max(object_type) as object_type,
max(tablespace_name) as tablespace_name
FROM $#CDB#$VND$seg_stat_obj o, $#CDB#$VND$wr_settings d
WHERE o.dbid = :dbid and d.local_awrdbid <> :dbid
GROUP BY obj#, o.con_id
SELECT dbms_ash_internal.unified_service_names
FROM dual;
UNIFIED_SERVICE_NAMES
--------------------------------------------------------------------------------
SELECT name_hash as service_hash, name as service FROM SYS.V_$ACTIVE_SERVICES
UNION ALL SELECT service_name_hash as service_hash, service_name as service FROM
$#CDB#$VND$SERVICE_NAME WHERE dbid = :dbid AND service_name_hash not in (
select name_hash from SYS.V_$ACTIVE_SERVICES)
SELECT dbms_ash_internal.unified_sqlstat
FROM dual;
UNIFIED_SQLSTAT
----------------------------------------------------------------------------
(SELECT s.sql_id, s.force_matching_signature FROM $#CDB#$VND$sqlstat s WHERE
dbid = :dbid )
SELECT sql_id, force_matching_signature
FROM cdb_hist_sqlstat;
-- the query, after conversion to valid syntax
returns 92,562 rows
-- from a non-production laptop.
