Oracle DBMS_ASH_INTERNAL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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.
AUTHID CURRENT_USER
Dependencies
ASHVIEWER DBMS_OUTPUT PRVT_AWR_DATA
AWRRPT_INSTANCE_LIST_TYPE DBMS_PERF PRVT_AWR_VIEWER
CDB_PROCEDURES DBMS_REPORT PRVT_REPORT_TAGS
DBA_HIST_PARAMETER DBMS_STANDARD PRVT_SMGUTIL
DBA_HIST_SQLTEXT DBMS_SWRF_REPORT_INTERNAL V$DATABASE
DBA_PROCEDURES DBMS_SYS_ERROR V$INSTANCE
DBA_USERS DBMS_WORKLOAD_REPOSITORY V$LATCH
DBMS_ASSERT DBMS_WRR_INTERNAL V$SQL
DBMS_AWR_REPORT_LAYOUT DUAL WRI$_REPT_ASH
DBMS_LOB PLITBLM XMLTYPE
DBMS_MANAGEMENT_PACKS PRVTEMX_PERF  
Documented No
First Available Not known but present in 11gR2
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvsash.plb
Subprograms
 
ASH_PHASE_EXECUTION
Undocumented dbms_ash_internal.ash_phase_execution(
max_rank IN NUMBER,
min_perc IN NUMBER)
RETURN VARCHAR2;
TBD
 
ASH_ROLLUP_SQL_1D (new 12.1 parameters)
Undocumented 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;
TBD
 
ASH_ROLLUP_SQL_2D (new 12.1 parameters)
Undocumented 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;
TBD
 
ASH_ROLLUP_SQL_3D (new 12.1 parameters)
Undocumented 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;
TBD
 
ASH_ROLLUP_XML_1D
Undocumented dbms_ash_internal.ash_rollup_xml_1d(section_tag IN VARCHAR2) RETURN XMLTYPE;
TBD
 
ASH_ROLLUP_XML_2D
Undocumented dbms_ash_internal.ash_rollup_xml_2d(section_tag IN VARCHAR2) RETURN XMLTYPE;
TBD
 
ASH_SQL_FOR_AWR (new 12.1)
Undocumented 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;
/
 
ASH_VIEW_SQL
Undocumented dbms_ash_internal.ash_view_sql RETURN VARCHAR2;
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 355


DECLARE
 retVal VARCHAR2(32767);
BEGIN
  retVal := dbms_ash_internal.ash_view_sql;
  dbms_output.put_line(retVal);
END;
/
 
BUILD_ACTION_TAG
Builds an ASH action tag dbms_ash_internal.build_action_tag(
action_name IN VARCHAR2,
ash_count   IN NUMBER,
xtra        IN XMLTYPE)
RETURN XMLTYPE;
CREATE TABLE xtab(
xval XMLTYPE);

DECLARE
 xOut XMLTYPE;
BEGIN
  xOut := dbms_ash_internal.build_action_tag('Testing', 42, NULL);
  INSERT INTO xtab VALUES (xOut);
END;
/

SELECT * FROM xtab;
 
BUILD_DATABASE_HEADER_TAG
Builds an ASH database header tag 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;
CREATE TABLE xtab(
xval XMLTYPE);

DECLARE
 xOut XMLTYPE;
BEGIN
  xOut := dbms_ash_internal.build_database_header_tag(1, 'ORADATA', '12.1.0', 'Y', 2, 'oradata', 'PERRITO4', NULL);
  INSERT INTO xtab VALUES (xOut);
END;
/

SELECT * FROM xtab;
 
BUILD_EVENT_TAG
Builds an ASH event tag dbms_ash_internal.build_event_tag(
event_name IN VARCHAR2,
wait_class IN VARCHAR2,
ash_count  IN NUMBER,
xtra       IN XMLTYPE)
RETURN XMLTYPE;
CREATE TABLE xtab(
xval XMLTYPE);

SELECT DISTINCT event
FROM v$active_session_history
ORDER BY 1;

DECLARE
 xOut XMLTYPE;
BEGIN
  xOut := dbms_ash_internal.build_event_tag('oracle thread bootstrap', 'other', 42, NULL);
  INSERT INTO xtab VALUES (xOut);
END;
/

SELECT * FROM xtab;
 
BUILD_SERVICE_MODULE_TAG
Builds an ASH service module tag 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, 'pdborcl', 'TEST_MOD',42, NULL);
  INSERT INTO xtab VALUES (xOut);
END;
/

SELECT * FROM xtab;
 
BUILD_SESSION_TAG
Builds an ASH session tag 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;
/

SELECT * FROM xtab;
 
BUILD_SQL_TAG
Builds an ASH  SQL tag 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);

SQL> SELECT sql_id, plan_hash_value
   2 FROM v$sqlarea
   3 WHERE rownum = 1;

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
3kv05jq710003       457189206

DECLARE
 xOut XMLTYPE;
BEGIN
  xOut := dbms_ash_internal.build_sql_tag('3kv05jq710003', 457189206, 63, 42, NULL);
  INSERT INTO xtab VALUES (xOut);
END;
/

SELECT * FROM xtab;
 
FORMAT_PLSQL
Undocumented 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;
DECLARE
 vOut VARCHAR2(8192);
BEGIN
  vOut := dbms_ash_internal.format_plsql('SYS', 'OWA_UTIL', 'PACKAGE', 'WHO_CALLED_ME', NULL);
  dbms_output.put_line(vOut);

  vOut := dbms_ash_internal.format_plsql('SYS', 'DBMS_SYS_ERROR', 'PACKAGE', 'RAISE_SYSTEM_ERROR', 5);
  dbms_output.put_line(vOut);
END;
/
 
GETPACKAGEVERSION
Undocumented dbms_ash_internal.getPackageVersion RETURN NUMBER;
SELECT dbms_ash_internal.getPackageVersion
FROM dual;
 
GET_ASH_HEADING
Undocumented dbms_ash_internal.get_ash_heading RETURN VARCHAR2;
SELECT dbms_ash_internal.get_ash_heading
FROM dual;
 
GET_BLKSID_ACTIVE
Undocumented dbms_ash_internal.get_blksid_active(
blksid    IN NUMBER,
blksidsrl IN NUMBER,
blkinstid IN NUMBER,
maxlen    IN NUMBER)
RETURN VARCHAR2;
TBD
 
GET_BLKSID_DETAILS
Undocumented dbms_ash_internal.get_blksid_details(
blksid    IN NUMBER,
blksidsrl IN NUMBER,
blkinstid IN NUMBER);
TBD
 
GET_BLKSID_PROGRAM
Undocumented dbms_ash_internal.get_blksid_program(
blksid    IN NUMBER,
blksidsrl IN NUMBER,
blkinstid IN NUMBER,
maxlen    IN NUMBER)
RETURN VARCHAR2;
TBD
 
GET_BLKSID_USER
Undocumented dbms_ash_internal.get_blksid_user(
blksid    IN NUMBER,
blksidsrl IN NUMBER,
blkinstid IN NUMBER)
RETURN VARCHAR2;
TBD
 
GET_BLKSID_XIDS
Undocumented dbms_ash_internal.get_blksid_xids(
blksid    IN NUMBER,
blksidsrl IN NUMBER,
blkinstid IN NUMBER)
RETURN NUMBER;
TBD
 
GET_BLK_STR
Undocumented dbms_ash_internal.get_blk_str(
sid    IN NUMBER,
srl    IN NUMBER,
instid IN NUMBER)
RETURN VARCHAR2;
SELECT sid, serial#
FROM v$session
WHERE sid IN (
  SELECT sid
  FROM v$mystat
  WHERE rownum = 1);

  SID    SERIAL#
----- ----------
  368        131

DECLARE
 vOut VARCHAR2(128);
BEGIN
  vOut := dbms_ash_internal.get_blk_str(368, 131, 1);
  dbms_output.put_line(vOut);
END;
/
 
GET_LATCH_NAME
Returns the ASH report formatted name of a latch dbms_ash_internal.get_latch_name(lnum IN NUMBER) RETURN VARCHAR2;
SELECT dbms_ash_internal.get_latch_name(99)
FROM dual;
 
GET_OBJ_NAME
Returns the ASH report formatted name of an object and its type dbms_ash_internal.get_obj_name(
owner IN VARCHAR2,
obj   IN VARCHAR2,
sub   IN VARCHAR2,
type  IN VARCHAR2)
RETURN VARCHAR2;
DECLARE
 vOut VARCHAR2(128);
BEGIN
  vOut := dbms_ash_internal.get_obj_name(USER, 'UTL_FILE', NULL, 'PACKAGE');
  dbms_output.put_line(vOut);
END;
/
 
GET_PLSQL_NAME (new 12.1 parameter)
Given a DBID, object_id, and subprogram_id returns the owner and name of an object for the ASH report dbms_ash_internal.get_plsql_name(
con_id        IN NUMBER,
dbid          IN NUMBER,
object_id     IN NUMBER,
subprogram_id IN NUMBER)
RETURN VARCHAR2;
show con_id

SQL> SELECT dbid FROM v$database;

      DBID
----------
1929260586

SQL> SELECT object_id, subprogram_id
   2 FROM dba_procedures
   3 WHERE ROWNUM = 1;

OBJECT_ID  SUBPROGRAM_ID
---------- -------------
      6375             1

DECLARE
 vOut VARCHAR2(128);
BEGIN
  vOut := dbms_ash_internal.get_plsql_name(1, 1929260586, 6375, 1);
  dbms_output.put_line(vOut);
END;
/
 
GET_SID_ACTIVE
Undocumented dbms_ash_internal.get_sid_active(
num_samples IN NUMBER,
maxlen      IN NUMBER)
RETURN VARCHAR2;
DECLARE
 vOut VARCHAR2(30);
BEGIN
  vOut := dbms_ash_internal.get_sid_active(368, 30);
  dbms_output.put_line(vOut);
END;
/
 
GET_SID_STR
Undocumented dbms_ash_internal.get_sid_str(
sid IN NUMBER,
srl IN NUMBER)
RETURN VARCHAR2;
DECLARE
 vOut VARCHAR2(30);
BEGIN
  vOut := dbms_ash_internal.get_sid_active(368, 131);
  dbms_output.put_line(vOut);
END;
/
 
GET_SLOT_WIDTH
Undocumented 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
 
GET_SQLTEXT
Undocumented dbms_ash_internal.get_sqltext(
sqlid IN VARCHAR2,
chnum IN NUMBER)
RETURN CLOB;
conn sys@pdbdev as sysdba

SELECT /* DEMO1 */ COUNT(*)
FROM dba_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;
/
 
GET_SQL_PLAN_INFO
Undocumented dbms_ash_internal.get_sql_plan_info(
line_id   IN NUMBER,
operation IN VARCHAR2,
options   IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GET_SQL_PLAN_PERC
Undocumented dbms_ash_internal.get_sql_plan_perc(
sql_plan_hash_value IN VARCHAR2,
perc                IN NUMBER)
RETURN NUMBER;
SELECT dbms_ash_internal.get_sql_plan_perc('1120054736', 66)
FROM dual;

-- looks remarkably useless or broken in Beta 2
 
GET_USER_NAME
Undocumented dbms_ash_internal.get_user_name(uid IN NUMBER) RETURN VARCHAR2;
SELECT user#, name
FROM user$
ORDER BY 1;

SELECT dbms_ash_internal.get_user_name(102)
FROM dual;

-- looks remarkably broken in Beta2
 
INITIALIZE
Undocumented 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);
TBD
 
IN_MEMORY_ASH_VIEW_SQL
Undocumented dbms_ash_internal.in_memory_ash_view_sql RETURN VARCHAR2;
SELECT dbms_ash_internal.in_memory_ash_view_sql
FROM dual;
 
ON_DISK_ASH_VIEW_SQL
Undocumented dbms_ash_internal.on_disk_ash_view_sql RETURN VARCHAR2;
SELECT dbms_ash_internal.on_disk_ash_view_sql
FROM dual;
 
REPORT_ASHVIEWER_XML (new 12.1 parameters)
Undocumented 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;
TBD
 
RUNREPORT (new 12.1 parameters)
Undocumented 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);
TBD
 
SAMPLE_TIME_TO_ID
Undocumented dbms_ash_internal.sample_time_to_id(
stime   IN DATE,
usedisk IN NUMBER)
RETURN NUMBER;
TBD
 
SHRINK_STRING
Undocumented dbms_ash_internal.shrink_string(
name IN VARCHAR2,
len  IN NUMBER)
RETURN VARCHAR2;
SELECT dbms_ash_internal.shrink_string('Next Generation Database', 12)
FROM dual;
 
SLOT_ID_TO_TIME
Undocumented dbms_ash_internal.slot_id_to_time(
slotid  IN NUMBER,
usedisk IN NUMBER,
frmt    IN VARCHAR2,
collen  IN NUMBER)
RETURN VARCHAR2;
TBD
 
UNIFIED_DBA_FILES
Returns a formatted query of database files for a DBID dbms_ash_internal.unified_dba_files RETURN VARCHAR2;
SELECT dbms_ash_internal.unified_dba_files
FROM dual;
 
UNIFIED_DBA_OBJECTS
Returns a formatted query of database objects for a DBID dbms_ash_internal.unified_dba_objects RETURN VARCHAR2;
SELECT dbms_ash_internal.unified_dba_objects
FROM dual;
 
UNIFIED_PLSQL_SUBPROGRAMS
Returns a formatted query for PL/SQL subprograms dbms_ash_internal.unified_plsql_subprograms RETURN VARCHAR2;
SELECT dbms_ash_internal.unified_plsql_subprograms
FROM dual;
 
UNIFIED_SERVICE_NAMES
Returns SQL service hash and name for services dbms_ash_internal.unified_service_names RETURN VARCHAR2;
SELECT dbms_ash_internal.unified_service_names
FROM dual;
 
UNIFIED_SQLSTAT
Returns SQL for an inline view returning SQL_ID and FORCE_MATCHING_SIGNATURE from dba_hist_sqlstat dbms_ash_internal.unified_sq_stat RETURN VARCHAR2;
SELECT dbms_ash_internal.unified_sqlstat
FROM dual;

Related Topics
ASH
Packages
Troubleshooting

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-2014 Daniel A. Morgan All Rights Reserved