Oracle DBMS_ASH
Version 19.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Active Session History
AUTHID CURRENT_USER
Dependencies
DBMS_ASSERT DUAL PRVT_ASH_OMX
DBMS_LOB PLITBLM XMLTYPE
DBMS_OUTPUT PRVTEMX_DBHOME XQSEQUENCE
DBMS_STANDARD    
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.
Source {ORACLE_HOME}/rdbms/admin/prvsash.plb
Subprograms
 
FETCH_OBJ_NAME_AWR
Undocumented dbms_ash.fetch_obj_name_awr(
p_obj_id     IN NUMBER,
p_dbid       IN NUMBER,
p_con_dbid   IN NUMBER,
p_is_pdb     IN VARCHAR2,
p_time_limit IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
FETCH_OBJ_NAME_LOCAL
Undocumented dbms_ash.fetch_obj_name_local
p_obj_id     IN NUMBER
p_dbid       IN NUMBER
p_con_dbid   IN NUMBER
p_time_limit IN VARCHAR2
RETURN VARCHAR2;
TBD
 
FETCH_PROCEDURE_NAME
Undocumented dbms_ash.fetch_procedure_name(
p_obj_id     IN NUMBER,
p_subobj_id  IN NUMBER,
p_con_dbid   IN NUMBER,
p_time_limit IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
FETCH_SQLTEXT_AWR
Undocumented dbms_ash.fetch_sqltext_awr(
p_sqlid      IN VARCHAR2,
p_dbid       IN NUMBER,
p_is_pdb     IN VARCHAR2,
p_time_limit IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
FETCH_SQLTEXT_LOCAL
Undocumented dbms_ash.fetch_sqltext_local(
p_sqlid      IN VARCHAR2,
p_dbid       IN NUMBER,
p_time_limit IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
FETCH_USER_NAME
Undocumented dbms_ash.fetch_user_name(
p_user_id    IN NUMBER,
p_con_dbid   IN NUMBER,
p_time_limit IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GETAWRINFO
Returns snapshot information formatted as XML

Manually formatted for clarity
dbms_ash.get_awr_info(
p_dbid           IN NUMBER,
p_begin_time_utc IN VARCHAR2,
p_end_time_utc   IN VARCHAR2,
p_inst_num       IN NUMBER)
RETURN XMLTYPE;
SQL> SELECT dbms_ash.getawrinfo(1863203691, '06292019', '06302019', 1)
  2  FROM dual;

BMS_ASH.GETAWRINFO(1863203691,'06292019','06302019',1)
------------------------------------------------------
<report>
  <report_parameters>
    <type>awr_info</type><dbid>1863203691</dbid>
    <begin_time_utc>06292019</begin_time_utc>
    <end_time_utc>06302019</end_time_utc>
    <instance_number>1</instance_number>
  </report_parameters>
  <awr_snaps>
    <snap snap_id="46" snap_time="06/29/2019 00:00:13" cnt_inst="1" task_id="57" owner="SYS" task_name="ADDM:1863203691_1_46" fdg_count="0"></snap>
    <snap snap_id="47" snap_time="06/29/2019 01:41:06" cnt_inst="1" task_id="58" owner="SYS" task_name="ADDM:1863203691_1_47" fdg_count="0"></snap>
    <snap snap_id="48" snap_time="06/29/2019 02:00:16" cnt_inst="1" task_id="59" owner="SYS" task_name="ADDM:1863203691_1_48" fdg_count="0"></snap>
    <snap snap_id="49" snap_time="06/29/2019 03:00:56" cnt_inst="1" task_id="60" owner="SYS" task_name="ADDM:1863203691_1_49" fdg_count="0"></snap>
    <snap snap_id="50" snap_time="06/29/2019 04:00:06" cnt_inst="1" task_id="61" owner="SYS" task_name="ADDM:1863203691_1_50" fdg_count="0"></snap>
    <snap snap_id="51" snap_time="06/29/2019 08:36:03" cnt_inst="1" task_id="62" owner="SYS" task_name="ADDM:1863203691_1_51" fdg_count="0"></snap>
    <snap snap_id="52" snap_time="06/29/2019 10:48:41" cnt_inst="1" task_id="63" owner="SYS" task_name="ADDM:1863203691_1_52" fdg_count="0"></snap>
    <snap snap_id="53" snap_time="06/29/2019 11:00:43" cnt_inst="1" task_id="64" owner="SYS" task_name="ADDM:1863203691_1_53" fdg_count="0"></snap>
    <snap snap_id="54" snap_time="06/29/2019 13:20:07" cnt_inst="1" task_id="65" owner="SYS" task_name="ADDM:1863203691_1_54" fdg_count="0"></snap>
    <snap snap_id="55" snap_time="06/29/2019 14:00:35" cnt_inst="1" task_id="66"     owner="SYS" task_name="ADDM:1863203691_1_55" fdg_count="0"></snap>
    <snap snap_id="56" snap_time="06/29/2019 15:00:16" cnt_inst="1" task_id="67" owner="SYS" task_name="ADDM:1863203691_1_56" fdg_count="0"></snap>
    <snap snap_id="57" snap_time="06/29/2019 16:40:54" cnt_inst="1" task_id="68" owner="SYS" task_name="ADDM:1863203691_1_57" fdg_count="0"></snap>
    <snap snap_id="58" snap_time="06/29/2019 17:00:58" cnt_inst="1" task_id="69" owner="SYS" task_name="ADDM:1863203691_1_58" fdg_count="0"></snap>
    <snap snap_id="59" snap_time="06/29/2019 18:00:23" cnt_inst="1" task_id="70" owner="SYS" task_name="ADDM:1863203691_1_59" fdg_count="0"></snap>
    <snap snap_id="60" snap_time="06/29/2019 19:12:21" cnt_inst="1" task_id="71" owner="SYS" task_name="ADDM:1863203691_1_60" fdg_count="0"></snap>
    <snap snap_id="61" snap_time="06/29/2019 20:00:50" cnt_inst="1" task_id="72" owner="SYS" task_name="ADDM:1863203691_1_61" fdg_count="0"></snap>
    <snap snap_id="62" snap_time="06/29/2019 21:20:46" cnt_inst="1" task_id="73" owner="SYS" task_name="ADDM:1863203691_1_62" fdg_count="0"></snap>
    <snap snap_id="63" snap_time="06/29/2019 22:00:26" cnt_inst="1" task_id="74" owner="SYS" task_name="ADDM:1863203691_1_63" fdg_count="0"></snap>
  </awr_snaps>
</report>
 
GETCPUINFO (new 19c parameter)
For the requested DBID, date and instance returns the number of threads and cores

Return value, at right, formatted for clarity
dbms_ash.getCPUinfo(
dbid            IN NUMBER,
observationtime IN VARCHAR2,
instance_number IN NUMBER)
RETURN XMLTYPE;
SQL> SELECT dbms_ash.getcpuinfo(1863203691, '06292019', 1)
  2  FROM dual;

DBMS_ASH.GETCPUINFO(1863203691,'06292019',1)
--------------------------------------------
<report>
  <input dbid="1863203691" requested_time="06292019" instance_number="1"></input>
    <cpuinfo time="06/29/2019 00:00:13" cpus="4" cores="2" limit="4"></cpuinfo>
</report>
 
GETDATA
Undocumented dbms_ash.getData(
data_type   IN VARCHAR2,
time_type   IN VARCHAR2,
filter_list IN VARCHAR2,
args        IN VARCHAR2)
RETURN XMLTYPE;
TBD
 
GETDATAHISTORICAL
Undocumented 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(1863203691)
FROM dual;
 
GETDATAREALTIME
Undocumented 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;
TBD
 
GETHISTOGRAMHISTORICAL
Undocumented

Manually formatted for clarity
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(1863203691)
FROM dual;

<report begin_time="06/29/2019 22:08:19" end_time="06/30/2019 22:08:19" time_zone="-5" xml_version="40" is_cdb_root="Y">
  <timing start_time="06/30/2019 17:08:19" end_time="06/30/201917:08:19" est_rows="26" exp_rows="2400" total=".047" add_info="0" add_info_budget="9.907" context="0" time_picker=".031" cpu_info=".016" data="0">
  </timing>
  <report_parameters>
    <type>histogram historical</type>
    <dbid>1863203691</dbid>
    <time_since_sec>86400</time_since_sec>
    <show_sql>n</show_sql>
    <verbose_xml>n</verbose_xml>
    <include_bg>n</include_bg>
    <minimize_cost>n</minimize_cost>
  </report_parameters>
  <cpuinfo time="06/30/2019 22:08:19" cpus="4" cores="2" limit="4"></cpuinfo>
  <histogram bucket_count="120" bucket_interval="720" last_bucket_interval="0">
    <bucket number="11" avg_active_sess="0" filtered_aas="0"></bucket>
    <bucket number="14" avg_active_sess=".01" filtered_aas=".01"></bucket>
    <bucket number="23" avg_active_sess="3.94" filtered_aas="3.94"></bucket>
    <bucket number="77" avg_active_sess=".01" filtered_aas=".01"></bucket>
    <bucket number="93" avg_active_sess="0" filtered_aas="0"></bucket>
    <bucket number="102" avg_active_sess=".01" filtered_aas=".01"></bucket>
    <bucket number="103" avg_active_sess="0" filtered_aas="0"></bucket>
    <bucket number="110" avg_active_sess="0" filtered_aas="0"></bucket>
    <bucket number="119" avg_active_sess="0" filtered_aas="0"></bucket>
  </histogram>
</report>
 
GETHISTOGRAMREALTIME
Undocumented 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;
TBD
 
GETTIMEPICKERHISTORICAL
Undocumented 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;
TBD
 
GETTIMEPICKERREALTIME
Undocumented 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;
TBD
 
GETVERSION
Returns the ASH version number dbms_ash.getVersion RETURN VARCHAR2;
SELECT dbms_ash.getVersion
FROM dual;

GETVERSION
----------
40
 
INCREMENTDATA
Undocumented 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;
TBD
 
INCREMENTHISTOGRAM
Undocumented 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;
TBD
 
INCREMENTTIMEPICKER
Undocumented 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;
TBD
 
STR_TO_ASCII
No doubt Oracle has some intended purpose for this but given the output of this test I am not sure what it is.

Appears to only be able to handle up to ASCII 255.
dbms_ash.str_to_ascii(s IN VARCHAR2) RETURN VARCHAR2;
SQL> SELECT dbms_ash.str_to_ascii('Daniel' || CHR(299) || 'Morgan')
  2* FROM dual;

DBMS_ASH.STR_TO_ASCII('DANIEL'||CHR(240)||'MORGAN')
----------------------------------------------------
DanielMorgan

Related Topics
Built-in Functions
Built-in Packages
DBMS_ASH_INTERNAL
What's New In 18c
What's New In 19c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx