This post is also available in:
English
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 👍!
