New DBMS_ASH and DBMS_BDSQL packages - Apr2018 Patch

This post is also available in: Português

After applying the 180417 PSU and BP over 12.1.0.2 Database, I've noted 2 new packages created on the database: DBMS_ASH and DBMS_BDSQL.

VERSION              PATCH      OWNER                TYPE                 NAME
-------------------- ---------- -------------------- -------------------- --------------------
12.1.0.2             PSU & BP   SYS                  PACKAGE              DBMS_ASH
12.1.0.2             BP ONLY    SYS                  PACKAGE              DBMS_BDSQL
12.1.0.2             BP ONLY    SYS                  PACKAGE BODY         DBMS_ASH
12.1.0.2             BP ONLY    SYS                  PACKAGE BODY         DBMS_BDSQL

* Note DBMS_ASH Package Body was not created by the PSU, only by the BP. Probably this was a bug or something still under development.

DBMS_ASH:

PACKAGE dbms_ash AUTHID CURRENT_USER IS

  TYPE FILTERTABLETYPE IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(30);

  TYPE ADDINFOTYPE IS RECORD (
    IDNAMESQL          VARCHAR2(32767)

  , ID2NAME_DYNAMIC    VARCHAR2(32767)

  , ID2NAME_AWR        VARCHAR2(32767)

  , ID2NAME_CONST      VARCHAR2(32767)

  , IDNAMEXML          VARCHAR2(32767)
  );

  TYPE DIMTYPE IS RECORD (

    NAME                VARCHAR2(30)
  , ENABLED             BOOLEAN

  , SELECTSTR           VARCHAR2(32767)
  , MAPSQL              VARCHAR2(32767)
  , SELECTSTRMAP        VARCHAR2(32767)
  , ISDIMMASKED         BOOLEAN
  , FROMCLAUSE          VARCHAR2(32767)
  , MAPXML              VARCHAR2(32767)
  , WHERECLAUSE         VARCHAR2(32767)
  , ADDINFO             ADDINFOTYPE
  , IS_PDB_SPECIFIC     BOOLEAN
  , CATEGORY            VARCHAR2(128)
  );

  TYPE DIMTABLETYPE IS TABLE OF DIMTYPE INDEX BY VARCHAR2(30);

  TYPE BINDTYPE IS RECORD (
    NAME VARCHAR2(100)
  , VALUE VARCHAR2(4000)
  );

  TYPE BINDARRAYTYPE IS VARRAY(100) OF BINDTYPE;

  TYPE QUERYBLOCKARRAYTYPE IS VARRAY(200) OF VARCHAR2(4000);

  TYPE CONTEXTTYPE IS RECORD (

    IS_LOCAL          BOOLEAN
  , IS_CDB_ROOT       BOOLEAN
  , LOCAL_IS_PDB      BOOLEAN
  , LOCAL_DBID        NUMBER
  , LOCAL_VERSION     VARCHAR(30)
  , LOCAL_COMP_VER    VARCHAR(30)
  , LOCAL_CONID       NUMBER
  , LOCAL_CONDBID     NUMBER
  , UNDERSCORES       BOOLEAN

  , BEGINTIMEUTC      DATE
  , ENDTIMEUTC        DATE

  , BUCKETCOUNT       NUMBER
  , BUCKETINTERVAL    NUMBER
  , LASTBUCKETSIZE    NUMBER

  , MEMENABLE         BOOLEAN
  , MEMTZ             NUMBER
  , MEMSIZEDAYS       NUMBER

  , DISKENABLE        BOOLEAN
  , DISK_COMP_VER     VARCHAR(30)
  , DBID              NUMBER
  , BEGINSNAPID       NUMBER
  , ENDSNAPID         NUMBER
  , AWRTABLEPREFIX    VARCHAR(20)
  , DISKENDTIMEUTC    DATE
  , DISKTZ            NUMBER

  , SHOW_SQL          BOOLEAN
  , VERBOSE_XML       BOOLEAN
  , ERROR_XML         XMLTYPE
  , DIAG_START_TIME   TIMESTAMP
  , DIAG_CONTEXT_SECS NUMBER
  , DIAG_PICKER_SECS  NUMBER
  , DIAG_DATA_SECS    NUMBER
  , DIAG_CPUINFO_SECS NUMBER

  , INCLUDE_BG        BOOLEAN
  , DIMTABLE          DIMTABLETYPE
  , MEMFILTERPREDICATE VARCHAR2(32767)
  , DISKFILTERPREDICATE VARCHAR2(32767)
  , GVFILTERPREDICATE VARCHAR2(32767)
  , SAMPLE_RATIO      NUMBER
  , EST_ROW_COUNT     NUMBER
  , EXP_ROW_COUNT     NUMBER
  , ACTIVITYLINEXML   XMLTYPE
  , CPUCOUNT          NUMBER
  , CPUCORECOUNT      NUMBER

  , QUERY             QUERYBLOCKARRAYTYPE
  , USE_UTC_BINDS     BOOLEAN
  , BINDS             BINDARRAYTYPE
  );

  REPORT_INTERNAL_VERSION CONSTANT VARCHAR2(64) := '29';

  OMC_TIME_FORMAT CONSTANT VARCHAR2(30) := 'MM/DD/YYYY HH24:MI:SS';

  OMC_ALLOWED_ERR_RATIO CONSTANT NUMBER := 0.9;

  OMC_DEF_BUCKETS CONSTANT NUMBER := 120;

  OMC_DEF_ROWS_PER_BUCKET CONSTANT NUMBER := 20;

  OMC_DEF_RT_MIN_BUCKET_SIZE CONSTANT NUMBER := 10;

  OMC_DEF_HIST_MIN_BUCKET_SIZE CONSTANT NUMBER := 10;

  OMC_DEF_SQLTEXT_LEN CONSTANT NUMBER := 200;

  VER_12_2 CONSTANT VARCHAR2(12) := '1202000000';
  VER_12_1_2 CONSTANT VARCHAR2(12) := '1201000200';
  VER_12_1 CONSTANT VARCHAR2(12) := '1201000000';
  VER_12   CONSTANT VARCHAR2(12) := '1200000000';
  VER_11_MIN CONSTANT VARCHAR2(12) := '1102000200';

  TOP_ADD_INFO_COUNT      CONSTANT BINARY_INTEGER := 20;
  MAX_INFO_TIME_LIMIT     CONSTANT BINARY_INTEGER := 2;

  RSRC_CONS_CAT           CONSTANT VARCHAR2(128) := 'resource_consumption_cat';
  SESS_ID_CAT             CONSTANT VARCHAR2(128) := 'session_identifiers_cat';
  SESS_ATTR_CAT           CONSTANT VARCHAR2(128) := 'session_attributes_cat';
  SQL_CAT                 CONSTANT VARCHAR2(128) := 'sql_cat';
  PLSQL_CAT               CONSTANT VARCHAR2(128) := 'pl_sql_cat';
  TARGET_CAT              CONSTANT VARCHAR2(128) := 'target_category';

  ERR_DIMNAME_TOO_LONG CONSTANT NUMBER := -13720;
  ERR_DIMNAME_INVALID  CONSTANT NUMBER := -13721;

  FUNCTION STR_TO_ASCII(S IN VARCHAR) RETURN VARCHAR;

  FUNCTION FETCH_SQLTEXT_LOCAL(P_SQLID IN VARCHAR, P_DBID IN NUMBER,
                               P_TIME_LIMIT IN VARCHAR)
  RETURN   VARCHAR;

  FUNCTION FETCH_SQLTEXT_AWR(P_SQLID IN VARCHAR, P_DBID IN NUMBER,
                             P_IS_PDB IN VARCHAR, P_TIME_LIMIT IN VARCHAR)
  RETURN   VARCHAR;

  FUNCTION FETCH_OBJ_NAME_LOCAL(P_OBJ_ID IN NUMBER, P_DBID IN NUMBER,
                                P_CON_DBID IN NUMBER, P_TIME_LIMIT IN VARCHAR)
  RETURN   VARCHAR;

  FUNCTION FETCH_OBJ_NAME_AWR(P_OBJ_ID IN NUMBER, P_DBID IN NUMBER,
                              P_CON_DBID IN NUMBER, P_IS_PDB IN VARCHAR,
                              P_TIME_LIMIT IN VARCHAR)
  RETURN   VARCHAR;

  FUNCTION FETCH_PROCEDURE_NAME(P_OBJ_ID IN NUMBER, P_SUBOBJ_ID IN NUMBER,
                                P_CON_DBID IN NUMBER, P_TIME_LIMIT IN VARCHAR)
  RETURN VARCHAR;

  FUNCTION FETCH_USER_NAME(P_USER_ID IN NUMBER, P_CON_DBID IN NUMBER,
                           P_TIME_LIMIT IN VARCHAR)
  RETURN VARCHAR;

  FUNCTION GETCPUINFO(DBID IN NUMBER := NULL,
                      OBSERVATIONTIME IN VARCHAR := NULL)
  RETURN XMLTYPE;

  FUNCTION GETTIMEPICKERREALTIME(
      TIME_SINCE_SEC IN NUMBER := 3600
    , SHOW_SQL       IN VARCHAR2 := 'n'
    , VERBOSE_XML    IN VARCHAR2 := 'n')
  RETURN XMLTYPE;

  FUNCTION INCREMENTTIMEPICKER(
      BEGIN_TIME_UTC IN VARCHAR2
    , BUCKET_SIZE    IN NUMBER
    , SHOW_SQL       IN VARCHAR2 := 'n'
    , VERBOSE_XML    IN VARCHAR2 := 'n')
  RETURN XMLTYPE;

  FUNCTION GETTIMEPICKERHISTORICAL(
      DBID           IN NUMBER := NULL
    , BEGIN_TIME_UTC IN VARCHAR2 := NULL
    , END_TIME_UTC   IN VARCHAR2 := NULL
    , TIME_SINCE_SEC IN NUMBER := 86400
    , SHOW_SQL       IN VARCHAR2 := 'n'
    , VERBOSE_XML    IN VARCHAR2 := 'n')
  RETURN XMLTYPE;

  FUNCTION GETDATAREALTIME(
      FILTER_LIST     IN VARCHAR2 := NULL
    , TIME_SINCE_SEC  IN NUMBER := 3600
    , SHOW_SQL        IN VARCHAR2 := 'n'
    , VERBOSE_XML     IN VARCHAR2 := 'n'
    , INCLUDE_BG      IN VARCHAR2 := 'n')
  RETURN XMLTYPE;

  FUNCTION INCREMENTDATA(
      FILTER_LIST    IN VARCHAR2 := NULL
    , BEGIN_TIME_UTC IN VARCHAR2
    , BUCKET_SIZE    IN NUMBER
    , SHOW_SQL       IN VARCHAR2 := 'n'
    , VERBOSE_XML    IN VARCHAR2 := 'n'
    , INCLUDE_BG      IN VARCHAR2 := 'n')
  RETURN XMLTYPE;

  FUNCTION GETDATAHISTORICAL(
      DBID            IN NUMBER := NULL
    , FILTER_LIST     IN VARCHAR2 := NULL
    , BEGIN_TIME_UTC  IN VARCHAR2 := NULL
    , END_TIME_UTC    IN VARCHAR2 := NULL
    , TIME_SINCE_SEC  IN NUMBER := 86400
    , SHOW_SQL        IN VARCHAR2 := 'n'
    , VERBOSE_XML     IN VARCHAR2 := 'n'
    , INCLUDE_BG      IN VARCHAR2 := 'n')
  RETURN XMLTYPE;

END DBMS_ASH;

DBMS_BDSQL:

PACKAGE "DBMS_BDSQL" AUTHID DEFINER
 AS

 -- Here are the exceptions that can come from this module
 -- We're going to use RAISE_APPLICATION_EEROR to raise these errors

 NULL_CLUSTER_NAME_PASSED constant PLS_INTEGER := -20899;
 NULL_CURRENT_DB_USER_PASSED constant PLS_INTEGER := -20898;
 NULL_FOURTH_PARAMETER_PASSED constant PLS_INTEGER := -20897;
 INVALID_USERENV_ATTRIBUTE constant PLS_INTEGER := -20896;
 DUPLICATE_ROW constant PLS_INTEGER := -20895;
 ROLE_PRIV_ERR constant PLS_INTEGER := -20894;
 NULL_SYSCTX_PARM_HADOOP_PASSED constant PLS_INTEGER:= -20893;
 INVALID_NAME_FOR_US constant PLS_INTEGER := -20892;
 INVALID_NAME_ASSERT constant PLS_INTEGER := -20891;
 NO_ROWS_FOUND constant PLS_INTEGER := -20890;
 NOTHING_TO_REVOKE constant PLS_INTEGER := -20889;
 NAMESPACE_TOO_LONG constant PLS_INTEGER := -20888;
 HADOOP_USER_TOO_LONG constant PLS_INTEGER := -20887;
 AMBIGUOUS_ROW constant PLS_INTEGER := -20886;

 NAMESPACE_MAX_LEN constant PLS_INTEGER := 30;
 HADOOP_USER_MAX_LEN constant PLS_INTEGER := 30;

 --- DBMS_ASSERT.SIMPLE_SQL_NAME didn't like one of the names given
 assert_invalid_name EXCEPTION;
  PRAGMA EXCEPTION_INIT(assert_invalid_name, -44003);
 --

 -- ADD_USER_MAP:
 --
 PROCEDURE ADD_USER_MAP(cluster_name IN VARCHAR2 DEFAULT '[DEFAULT]',
                        current_database_user    IN VARCHAR2,  -- NOT NULL
                    syscontext_namespace    IN VARCHAR2,   -- DEFAULT NULL
                    syscontext_parm_hadoop_user IN VARCHAR2); -- NOT NULL

 -- REMOVE_USER_MAP:
 --
 PROCEDURE REMOVE_USER_MAP(
                    cluster_name           IN VARCHAR2 DEFAULT '[DEFAULT]',
                    current_database_user  IN VARCHAR2);   -- NOT NULL

 -- SEL_USERMAP:
 PROCEDURE SEL_USERMAP(cluster_name           IN VARCHAR2,
                       current_database_user  IN VARCHAR2,
                       mapping               OUT VARCHAR2,
                       rc                    OUT PLS_INTEGER);

END DBMS_BDSQL;

Both packages are not yet officially documented so their usage are still unclear. They seem to be for internal use only. However, one thing I could notice is that DBMS_HADOOP now interacts with DBMS_BDSQL.SEL_USERMAP.

Their unwrapped Package Bodys are pretty big to post here. I did some overall analysis in SQL Injection and at least they look clear =]

Have you enjoyed? Please leave a comment or give a 👍!

Leave a Reply

Your email address will not be published.