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 will be available on generic Linux platforms in January and soon on AIX and Windows

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