Oracle | DBMS

Oracle DBMS_ASH Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

Purpose Active Session History

AUTHID CURRENT_USER

Dependencies

DBMS_ASSERT	DUAL	V_$PARAMETER
DBMS_LOB	GV_$INSTANCE	V_$SQLCOMMAND
DBMS_MANAGEMENT_PACKS	NLS_SESSION_PARAMETERS	V_$SYSTEM_EVENT
DBMS_OUTPUT	PLITBLM	WRI$_REPT_ASH_OMX
DBMS_SQL	PRVTEMX_DBHOME	XMLTYPE
DBMS_STANDARD	PRVTEMX_RSRCMGR	XQSEQUENCE
DBMS_UTILITY	PRVT_ASH_OMX

Documented No

First Available 12.2

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Our security-focused question: Why does PUBLIC needs access to active session history? We don't have a viable answer.

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	GETDATAHISTORICAL	GETTIMEPICKERREALTIME
FETCH_PROCEDURE_NAME	GETDATAREALTIME	GETVERSION
FETCH_SQLTEXT	GETHISTOGRAMHISTORICAL	GET_BLOCKING_SESSION_DATA
FETCH_USER_NAME	GETHISTOGRAMREALTIME	INCREMENTDATA
GETADDITIONALINFO	GETSQLTEXT	INCREMENTHISTOGRAM
GETAWRINFO	GETTIMEFORMAT	INCREMENTTIMEPICKER
GETCPUINFO	GETTIMEPICKERHISTORICAL	STR_TO_ASCII
GETDATA

FETCH_OBJ_NAME

Undocumented 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;

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

Undocumented 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;

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

GETADDITIONALINFO (new 23ai)

Undocumented dbms_ash.getadditionalinfo( dbid IN NUMBER, instance_number IN NUMBER, real_time_mode IN VARCHAR2, show_sql IN VARCHAR2, dim_list IN VARCHAR2, sqlid_ist IN VARCHAR2, plsql_list IN VARCHAR2, object_list IN VARCHAR2, user_list IN VARCHAR2) RETURN sys.xmlType;

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;

SELECT dbid

      FROM v$database;

      

            DBID

      ----------

      3091983078

      
      

      SELECT dbms_ash.getawrinfo(3091983078,'01272021', '01282021', 1);

      

      DBMS_ASH.GETAWRINFO(3091983078,'01272021','01282021',1)

      -------------------------------------------------------------------------

      <report>

        <report_parameters>

          <type>awr_info</type>

          <dbid>3091983078</dbid>

          <begin_time_utc>01272021</begin_time_utc>

          <end_time_utc>01282021</end_time_utc>

          <instance_number>1</instance_number>

        </report_parameters>

        <awr_snaps>

          <metadata data_source="LOCAL" 
      retention="8" interval="60"></metadata>

        </awr_snaps>

      </report>

GETCPUINFO

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 DEFAULT NULL, observationtime IN VARCHAR2 DEFAULT NULL, instance_number IN NUMBER DEFAULT NULL) RETURN XMLTYPE;

SELECT dbms_ash.getcpuinfo;

      

      GETCPUINFO

      -----------------------------------------------------------------------------

      <report>

        <input></input>

        <cpuinfo time="01/29/2024 02:57:03" cpus="2" cores="1" limit="2"></cpuinfo>

      </report>

GETDATA

Undocumented dbms_ash.getData( data_type IN VARCHAR2, time_type IN VARCHAR2, filter_list IN VARCHAR2, args IN VARCHAR2) RETURN XMLTYPE;

SELECT dbms_ash.getData('', '', '', '');

      

      DBMS_ASH.GETDATA('','','','')

      ---------------------------------------------------------------------------------------

      <report end_time="12/01/2024 14:23:37" time_zone="0" xml_version="61" is_cdb_root="Y">

        <sys_info sys_tz="0" min_time="11/15/2024 20:57:17" max_time="12/01/2024 14:23:37"

          con_id="0" auto_type="NONE" version="23.4.0.24.05" local_role="PRIMARY"

          local_is_exadata="FALSE" local_dbtimezone="0" local_is_rac="FALSE">

        </sys_info>

        <timing start_time="12/01/2024 14:23:37" end_time="12/01/2024 14:23:37" total=".1228"

          add_info="0" add_info_budget="2.3722" context=".021" time_picker=".0426"

          cpu_info=".0562" data="0">

        </timing>

        <context is_local="TRUE" is_cdb_root="TRUE" local_use_gv="FALSE" local_is_pdb="FALSE"

          local_is_rac="FALSE" local_is_autonomous="FALSE" local_dbid="3091983078"

          local_role="PRIMARY" local_version="23.4.0.24.05" local_comp_ver="2304002405"

          underscores="FALSE" sysTZ="0" minAvailTimeUTC="11/15/2024 20:57:17"

          maxAvailTimeUTC="12/01/2024 14:23:37" endTimeUTC="12/01/2024 14:23:37"

          include_awr_info="FALSE" monitoring_mode="FALSE" memory_only="FALSE"

          resolution="MEDIUM" memEnable="TRUE" memSizeDays="15.72662" memTZ="0"

          diskEnable="TRUE" disk_comp_ver="2300000000" dbid="3091983078" beginSnapID="4653"

          endSnapID="4653" awrTablePrefix="DBA_HIST_" diskEndTimeUTC="12/01/2024 13:58:18"

          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>

          <monitoring_mode>n</monitoring_mode>

          <memory_only>n</memory_only>

        </report_parameters>

        <cpuinfo time="12/01/2024 14:23:37" cpus="8" cores="8" limit="8">

          <list name="cores"><c t="12/01/2024 13:58:18" v="8"></c></list>

          <list name="cpus"><c t="12/01/2024 13:58:18" v="8"></c></list>

          <list name="limit"><c t="12/01/2024 13:58:18" v="8"></c></list>

        </cpuinfo>

        <histogram></histogram>

      </report>

GETDATAHISTORICAL

Undocumented dbms_ash.getDataHistorical( dbid IN NUMBER DEFAULT NULL, filter_list IN VARCHAR2 DEFAULT NULL, begin_time_utc IN VARCHAR2 DEFAULT NULL, end_time_utc IN VARCHAR2 DEFAULT NULL, time_since_sec IN NUMBER DEFAULT NULL, show_sql IN VARCHAR2 DEFAULT NULL, verbose_xml IN VARCHAR2 DEFAULT NULL, include_bg IN VARCHAR2 DEFAULT NULL, instance_number IN NUMBER DEFAULT NULL, minimize_cost IN VARCHAR2 DEFAULT NULL) RETURN XMLTYPE;

SELECT dbms_ash.getDataHistorical;

      

      GETDATAHISTORICAL

      -------------------------------------------------------------------------------------

      <report begin_time="11/30/2024 14:24:27" end_time="12/01/2024 14:24:27" time_zone="0"

        xml_version="61" is_cdb_root="Y">

        <sys_info sys_tz="0" con_id="0" auto_type="NONE" version="23.4.0.24.05"

          local_role="PRIMARY" local_is_exadata="FALSE" local_dbtimezone="0"

          local_is_rac="FALSE">

        </sys_info>

        <timing start_time="12/01/2024 14:24:27" end_time="12/01/2024 14:24:29"

          est_rows="894" exp_rows="6300" total="1.6699" add_info=".9181"

          add_info_budget="9.8671" context=".0026" time_picker=".0443" cpu_info=".0061"

          data="1.6166">

        </timing>

        <context is_local ="TRUE" is_cdb_root="TRUE" local_use_gv="FALSE" ....

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 DEFAULT NULL, filter_list IN VARCHAR2 DEFAULT NULL, begin_time_utc IN VARCHAR2 DEFAULT NULL, end_time_utc IN VARCHAR2 DEFAULT NULL, time_since_sec IN NUMBER DEFAULT NULL, show_sql IN VARCHAR2 DEFAULT NULL, verbose_xml IN VARCHAR2 DEFAULT NULL, include_bg IN VARCHAR2 DEFAULT NULL, instance_number IN NUMBER DEFAULT NULL) RETURN XMLTYPE;

SELECT dbms_ash.getHistogramHistorical;

      

      GETHISTOGRAMHISTORICAL

      -------------------------------------------------------------------------------------

      <report begin_time="11/30/2024 14:25:52" end_time="12/01/2024 14:25:52" time_zone="0"

        xml_version="61" is_cdb_root="Y">

        <sys_info sys_tz="0" min_time="11/15/2024 21:06:16" max_time="12/01/2024 14:25:52"

          con_id="0" auto_type="NONE" version="23.4.0.24.05" local_role="PRIMARY"

          local_is_exadata="FALSE" local_dbtimezone="0" local_is_rac="FALSE">

        </sys_info>

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

GETSQLTEXT (new 23ai)

Undocumented dbms_ash.getSQLText( p_dbid IN NUMBER, p_sql_ids IN VARCHAR2) RETURN sys.xmlType;

TBD

GETTIMEFORMAT (new 23ai)

Returns current NLS date-time format dbms_ash.getTimeFormat RETURN VARCHAR2;

SELECT value

      FROM sys.v_$nls_parameters

      WHERE parameter = 'NLS_DATE_FORMAT';

      

      PARAMETER       
      VALUE                   CON_ID

      --------------- ----------------------- ------

      NLS_DATE_FORMAT DD-MON-YYYY HH24:MI:SS       1

      

      

      SELECT dbms_ash.getTimeFormat;

      

      GETTIMEFORMAT

      ----------------------

      MM/DD/YYYY HH24:MI:SS

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;

      

      GETVERSION

      ----------

              48

GET_BLOCKING_SESSION_DATA (new 23ai)

Undocumented dbms_ash.get_blocking_session_data( data_type IN VARCHAR2, source_type IN VARCHAR2, filter_list IN VARCHAR2, begin_time_utc IN VARCHAR2, end_time_utc IN VARCHAR2, time_since_sec IN NUMBER, show_sql IN VARCHAR2, filter_common_user IN VARCHAR2) RETURN sys.xmlType;

TBD

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 demo I am not sure what it is: Hardly seems worthwhile

Appears to only be able to handle up to ASCII 255. dbms_ash.str_to_ascii(s IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_ash.str_to_ascii('Daniel' || CHR(299) || 'Morgan');

      

      DBMS_ASH.STR_TO_ASCII('DANIEL'||CHR(299)||'MORGAN')

      ----------------------------------------------------

      Daniel+Morgan