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 👍!