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
Active Session History
AUTHID
CURRENT_USER
Dependencies
DBMS_ASSERT
DUAL
V_$SQLCOMMAND
DBMS_LOB
PLITBLM
XMLTYPE
DBMS_OUTPUT
PRVTEMX_DBHOME
XQSEQUENCE
DBMS_STANDARD
PRVT_ASH_OMX
Documented
No
First Available
12.2
Security Model
Owned by SYS with EXECUTE granted to PUBLIC
And PUBLIC needs access to active session history reason why?
Minimum privileges note to Oracle: Only a DBA or a Developer in a pre-production environment has any business looking at ASH data.
dbms_ash.fetch_obj_name_awr(
p_obj_id IN NUMBER,
p_dbid IN NUMBER,
p_con_dbid IN NUMBER,
p_is_local IN VARCHAR2,
p_local_type IN VARCHAR2,
p_is_old IN VARCHAR2,
p_time_limit IN VARCHAR2)
RETURN XMLTYPE;
dbms_ash.fetch_sqltext_awr(
p_sqlid IN VARCHAR2,
p_dbid IN NUMBER,
p_con_dbid IN NUMBER,
p_is_local IN VARCHAR2,
p_is_pdb IN VARCHAR2,
p_is_old IN VARCHAR2,
p_time_limit IN VARCHAR2)
RETURN XMLTYPE;
dbms_ash.getData(
data_type IN VARCHAR2,
time_type IN VARCHAR2,
filter_list IN VARCHAR2,
args IN VARCHAR2)
RETURN XMLTYPE;
SELECT dbms_ash.getData('', '', '', '') FROM dual;
DBMS_ASH.GETDATA('','','','')
-----------------------------------------------------------------------------------
<report end_time="01/29/2021 03:11:41" time_zone="0" xml_version="48" is_cdb_root="Y">
<sys_info sys_tz="0" min_time="01/20/2021 23:58:02" max_time="01/29/2021 03:11:41" con_id="0" auto_type="NONE" version="21.0.0.0.0">
</sys_info>
<timing start_time="01/29/2021 03:11:41" end_time="01/29/2021 03:11:41" total=".0497" add_info="0" add_info_budget="2.4418" context=".0082" time_picker=".0194" cpu_info=".0131" data="0">
</timing>
<context is_local="TRUE" is_cdb_root="TRUE" local_is_pdb="FALSE" local_is_autonomous="FALSE" local_dbid="2140826538" local_role="PRIMARY" local_version="21.0.0.0.0" local_comp_ver="2100000000" underscores="FALSE" sysTZ="0" minAvailTimeUTC="01/20/2021 23:58:02" maxAvailTimeUTC="01/29/2021 03:11:41" e ndTimeUTC="01/29/2021 03:11:41" include_awr_info="FALSE" resolution="MEDIUM" memEnable="TRUE" memSizeDays="3.213623" memTZ="0" diskEnable="FALSE" disk_comp_ver="2100000000" dbid="21 40826538" awrTablePrefix="DBA_HIST_" diskTZ="0" show_sql="FALSE" verbose_xml="FALSE" minimize_cost="FALSE" use_utc_binds="TRUE">
</context>
<report_parameters><type>histogram increment</type>
<show_sql>n</show_sql>
<verbose_xml>n</verbose_xml>
<include_bg>n</include_bg>
<minimize_cost>n</minimize_cost>
<resolution>medium</resolution>
</report_parameters>
<cpuinfo time="01/29/2021 03:11:41" cpus="2" cores="1" limit="2"></cpuinfo>
<error>ORA-00936: missing expression
<sqltext>WITH combined_source AS (SELECT /*+ NO_MERGE */ inst_id, bucket_id, filtered_cnt, total_sec, filtered_sec, min_sample_id FROM TABLE(GV$(CURSOR(SELECT TO_NUMBER(USERENV('INSTANCE')) as inst_id, bucket_id, SUM(is_filtered) as filtered_cnt, SUM(tpr) as total_sec, SUM(is_filtered * tpr) as filtered_sec, MIN(sample_id) as min_sample_id FROM (SELECT CASE WHEN (1=1 ) THEN 1 ELSE 0 END as is_filtered, a.usecs_per_row/1000000 as tpr, TRUNC(86400*((CAST(a.sample_time_utc AS DATE))-TO_DATE('','YYYYMMDDHH24MISS') ) / ) as bucket_id, a.sample_id FROM V$ACTIVE_SESSION_HISTORY a WHERE CAST(a.sample_time_utc AS DATE) >= TO_DATE('','YYYYMMDDHH24MISS') AND CAST(a.sample_time_utc AS DATE) < TO_DATE('20210129031141','YYYYMMDDHH24MISS') AND (a.session_state = 'ON CPU' OR a.wait_class <> 'Idle') AND a.session_type = 'FOREGROUND') GROUP BY bucket_id)))), rac_data AS (
SELECT bucket_id, sum(filtered_cnt) as filtered_cnt, sum(total_sec) as total_sec, sum(filtered_sec) as filtered_sec FROM combined_source GROUP BY bucket_id ORDER BY bucket_id), histogram_xml as (SELECT xmlelement("histogram",
xmlattributes( as "bucket_count", as "bucket_interval", as "last_bucket_interval"), xmlagg(xmlelement("bucket",xmlattributes(
bucket_id+1 as "number", (CASE WHEN (bucket_id= and > 0) THEN ROUND(total_sec /, 2) ELSE ROUND(total_sec / ,2) END) as "avg_active_sess",
(CASE WHEN (bucket_id= and > 0) THEN ROUND(filtered_sec / ,2) ELSE ROUND(filtered_sec / ,2) END) as "filtered_aas")) )) as x FROM (SELECT * FROM rac_data ORDER BY bucket_id)), est_count as (SELECT SUM(filtered_cnt) as cnt FROM rac_data) SELECT est_count.cnt, histogram_xml.x FROM est_count, histogram_xml
</sqltext>
</error>
</report>
WITH combined_source AS (
SELECT /*+ NO_MERGE */
inst_id, bucket_id,
filtered_cnt, total_sec, filtered_sec, min_sample_id
FROM TABLE(GV$(CURSOR(
SELECT TO_NUMBER(USERENV('INSTANCE')) as inst_id, bucket_id,
SUM(is_filtered) as filtered_cnt,
SUM(tpr) as total_sec,
SUM(is_filtered * tpr) as filtered_sec,
MIN(sample_id) as min_sample_id
FROM (
SELECT CASE WHEN (1=1 ) THEN 1 ELSE 0 END as is_filtered, a.usecs_per_row/1000000 as tpr,
TRUNC(86400*((CAST(a.sample_time_utc AS DATE))-TO_DATE('','YYYYMMDDHH24MISS') )
/ ) as bucket_id, a.sample_id
FROM V$ACTIVE_SESSION_HISTORY a
WHERE CAST(a.sample_time_utc AS DATE) >= TO_DATE('','YYYYMMDDHH24MISS')
AND CAST(a.sample_time_utc AS DATE) < TO_DATE('20210129031141','YYYYMMDDHH24MISS')
AND (a.session_state = 'ON CPU'
OR a.wait_class <> 'Idle') AND a.session_type = 'FOREGROUND')
GROUP BY bucket_id)))) ,rac_data AS (
SELECT bucket_id, sum(filtered_cnt) as filtered_cnt,
sum(total_sec) as total_sec, sum(filtered_sec) as filtered_sec
FROM combined_source GROUP BY bucket_id ORDER BY bucket_id ) ,histogram_xml as
(SELECT xmlelement("histogram",
xmlattributes( as "bucket_count",
as "bucket_interval", as "last_bucket_interval"),
xmlagg(xmlelement("bucket",xmlattributes(
bucket_id+1 as "number",
(CASE WHEN (bucket_id= and > 0)
THEN ROUND(total_sec / ,2)
ELSE ROUND(total_sec / ,2) END)
as "avg_active_sess",
(CASE WHEN (bucket_id= and > 0)
THEN ROUND(filtered_sec / ,2)
ELSE ROUND(filtered_sec / ,2) END)
as "filtered_aas"))
)) as x
FROM (SELECT * FROM rac_data ORDER BY bucket_id)
), est_count as (SELECT SUM(filtered_cnt) as cnt FROM rac_data)
SELECT est_count.cnt, histogram_xml.x FROM est_count, histogram_xml;
ORA-00936: missing expression
dbms_ash.getDataHistorical(
dbid IN NUMBER,
filter_list IN VARCHAR2,
begin_time_utc IN VARCHAR2,
end_time_utc IN VARCHAR2,
time_since_sec IN NUMBER,
show_sql IN VARCHAR2,
verbose_xml IN VARCHAR2,
include_bg IN VARCHAR2,
instance_number IN NUMBER,
minimize_cost IN VARCHAR2)
RETURN XMLTYPE;
SELECT dbms_ash.getDataHistorical(2140826538)
FROM dual;
dbms_ash.getDataRealTime(
filter_list IN VARCHAR2,
time_since_sec IN NUMBER,
show_sql IN VARCHAR2,
verbose_xml IN VARCHAR2,
include_bg IN VARCHAR2,
instance_number IN NUMBER,
minimize_cost IN VARCHAR2)
RETURN XMLTYPE;
dbms_ash.getHistogramHistorical(
dbid IN NUMBER,
filter_list IN VARCHAR2,
begin_time_utc IN VARCHAR2,
end_time_utc IN VARCHAR2,
time_since_sec IN NUMBER,
show_sql IN VARCHAR2,
verbose_xml IN VARCHAR2,
include_bg IN VARCHAR2,
instance_number IN NUMBER)
RETURN XMLTYPE;
SELECT dbms_ash.getHistogramHistorical(2140826538)
FROM dual;
dbms_ash.getHistoricalRealTime(
filter_list IN VARCHAR2,
time_since_sec IN NUMBER,
show_sql IN VARCHAR2,
verbose_xml IN VARCHAR2,
include_bg IN VARCHAR2,
instance_number IN NUMBER)
RETURN XMLTYPE;
dbms_ash.getTimePickerHistorical(
DBID IN NUMBER,
begin_time_utc IN VARCHAR2,
end_time_utc IN VARCHAR2,
time_since_sec IN NUMBER,
show_sql IN VARCHAR2,
verbose_xml IN VARCHAR2,
instance_number IN NUMBER,
awr_info IN VARCHAR2)
RETURN XMLTYPE;
dbms_ash.getTimePickerRealTime(
time_since_sec IN NUMBER,
show_sql IN VARCHAR2,
verbose_xml IN VARCHAR2,
instance_number IN NUMBER,
awr_info IN VARCHAR2)
RETURN XMLTYPE;
dbms_ash.incrementData(
filter_list IN VARCHAR2,
begin_time_utc IN VARCHAR2,
bucket_size IN NUMBER,
show_sql IN VARCHAR2,
verbose_xml IN VARCHAR2,
include_bg IN VARCHAR2,
instance_number IN NUMBER,
minimize_cost IN VARCHAR2)
RETURN XMLTYPE;
dbms_ash.incrementHistogram(
filter_list IN VARCHAR2,
begin_time_utc IN VARCHAR2,
bucket_size IN NUMBER,
show_sql IN VARCHAR2,
verbose_xml IN VARCHAR2,
include_bg IN VARCHAR2,
instance_number IN NUMBER)
RETURN XMLTYPE;
dbms_ash.incrementTimePicker(
begin_time_utc IN VARCHAR2,
bucket_size IN NUMBER,
show_sql IN VARCHAR2,
verbose_xml IN VARCHAR2,
instance_number IN NUMBER,
AWR_INFO IN VARCHAR2)
RETURN XMLTYPE;
