This post is also available in:
Português
Since Oct-2017 I am quarterly writing a post dissecting the changes implemented by Oracle CPUs so we can understand better the modifications implemented by Oracle in our Databases.
So what oracle internal objects were changed in 180116?
VERSION PATCH OWNER TYPE TOTAL -------------------- ---------- ------------------------------ ------------------------------ ---------- 11.2.0.4 PSU & BP SYS PACKAGE 1 11.2.0.4 PSU & BP SYS PACKAGE BODY 2 12.1.0.2 BP SYS PACKAGE BODY 3 12.1.0.2 PSU & BP SYS PACKAGE BODY 1 12.2.0.1 JUL2017RUR SYS PACKAGE BODY 2 12.2.0.1 OCT2017RUR SYS PACKAGE BODY 2 12.2.0.1 RU CTXSYS PACKAGE BODY 1 12.2.0.1 RU DVSYS TYPE 6 12.2.0.1 RU DVSYS VIEW 2 12.2.0.1 RU SYS PACKAGE 1 12.2.0.1 RU SYS PACKAGE BODY 10 12.2.0.1 RU SYS VIEW 10
And created?
VERSION PATCH OWNER TYPE TOTAL -------------------- ---------- ------------------------------ ------------------------------ ---------- 11.2.0.4 OJVM PUBLIC SYNONYM 21 12.1.0.2 BP SYS PACKAGE BODY 1 12.1.0.2 OJVM PUBLIC SYNONYM 21 12.1.0.2 PSU SYS PACKAGE BODY 1
So which are exactly the objects modified by 180116?
VERSION PATCH OWNER TYPE NAME -------------------- ---------- ------------------------------ ------------------------------ ------------------------------ 11.2.0.4 PSU & BP SYS PACKAGE BODY DBMS_BACKUP_RESTORE 11.2.0.4 PSU & BP SYS PACKAGE DBMS_BACKUP_RESTORE 11.2.0.4 PSU & BP SYS PACKAGE BODY DBMS_LOGREP_EXP 12.1.0.2 BP SYS PACKAGE BODY DBMS_LOGREP_EXP 12.1.0.2 BP SYS PACKAGE BODY DBMS_OPTIM_BUNDLE 12.1.0.2 BP SYS PACKAGE BODY PRVT_ILM 12.1.0.2 PSU & BP SYS PACKAGE BODY CDBVIEW 12.2.0.1 JUL2017RUR SYS PACKAGE BODY CDBVIEW 12.2.0.1 JUL2017RUR SYS PACKAGE BODY PRVTEMX_CELL 12.2.0.1 OCT2017RUR SYS PACKAGE BODY CDBVIEW 12.2.0.1 OCT2017RUR SYS PACKAGE BODY PRVTEMX_CELL 12.2.0.1 RU CTXSYS PACKAGE BODY DRVDDL 12.2.0.1 RU DVSYS TYPE KU$_DV_REALM_MEMBER_T 12.2.0.1 RU DVSYS TYPE KU$_DV_RULE_SET_MEMBER_T 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072649$ 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072661$ 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072855$ 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072867$ 12.2.0.1 RU DVSYS VIEW KU$_DV_REALM_MEMBER_VIEW 12.2.0.1 RU DVSYS VIEW KU$_DV_RULE_SET_MEMBER_VIEW 12.2.0.1 RU SYS PACKAGE PRVT_COMPRESSION 12.2.0.1 RU SYS PACKAGE BODY CDBVIEW 12.2.0.1 RU SYS PACKAGE BODY DBMS_APPLY_ADM_INTERNAL 12.2.0.1 RU SYS PACKAGE BODY DBMS_AQADM_SYS 12.2.0.1 RU SYS PACKAGE BODY DBMS_COMPRESSION 12.2.0.1 RU SYS PACKAGE BODY DBMS_LOGREP_EXP 12.2.0.1 RU SYS PACKAGE BODY DBMS_OPTIM_BUNDLE 12.2.0.1 RU SYS PACKAGE BODY DBMS_PRVTAQIM 12.2.0.1 RU SYS PACKAGE BODY DBMS_RCVMAN 12.2.0.1 RU SYS PACKAGE BODY PRVTEMX_CELL 12.2.0.1 RU SYS PACKAGE BODY PRVT_ILM 12.2.0.1 RU SYS VIEW LOGSTDBY_RU_UNSUPPORT_TAB_12_1 12.2.0.1 RU SYS VIEW LOGSTDBY_RU_UNSUPPORT_TAB_12_2 12.2.0.1 RU SYS VIEW LOGSTDBY_SUPPORT_TAB_12_1 12.2.0.1 RU SYS VIEW LOGSTDBY_SUPPORT_TAB_12_2 12.2.0.1 RU SYS VIEW LOGSTDBY_UNSUPPORT_TAB_12_1 12.2.0.1 RU SYS VIEW LOGSTDBY_UNSUPPORT_TAB_12_2 12.2.0.1 RU SYS VIEW OGG_SUPPORT_TAB_11_2 12.2.0.1 RU SYS VIEW OGG_SUPPORT_TAB_11_2B 12.2.0.1 RU SYS VIEW OGG_SUPPORT_TAB_12_1 12.2.0.1 RU SYS VIEW OGG_SUPPORT_TAB_12_2
What changed for each?
P.S:
< = Added Lines
> = Removed Lines
11.2.0.4
SYS.DBMS_BACKUP_RESTORE - PACKAGE BODY (PSU & BP):
1542,1546c1542 < ,PLTFRMFR IN BINARY_INTEGER < ,ENCRYPT IN NUMBER < ,ENC_ALGORITHM IN NUMBER < ,ALLOWTDE IN NUMBER < ,PASSWORD IN VARCHAR2); --- > ,PLTFRMFR IN BINARY_INTEGER); 1678,1708c1674 < BEGIN < BACKUPBACKUPPIECE(BPNAME, FNAME, HANDLE, COMMENT, MEDIA, CONCUR, < RECID, STAMP, TAG, PARAMS, MEDIA_POOL, REUSE, < CHECK_LOGICAL, COPYNO, DEFFMT, COPY_RECID, < COPY_STAMP, NPIECES, DEST, PLTFRMFR, 0, 0, 0, NULL); < END; < < PROCEDURE BACKUPBACKUPPIECE( BPNAME IN VARCHAR2 < ,FNAME IN VARCHAR2 < ,HANDLE OUT VARCHAR2 < ,COMMENT OUT VARCHAR2 < ,MEDIA OUT VARCHAR2 < ,CONCUR OUT BOOLEAN < ,RECID OUT NUMBER < ,STAMP OUT NUMBER < ,TAG IN VARCHAR2 DEFAULT NULL < ,PARAMS IN VARCHAR2 DEFAULT NULL < ,MEDIA_POOL IN BINARY_INTEGER DEFAULT 0 < ,REUSE IN BOOLEAN DEFAULT FALSE < ,CHECK_LOGICAL IN BOOLEAN < ,COPYNO IN BINARY_INTEGER < ,DEFFMT IN BINARY_INTEGER < ,COPY_RECID IN NUMBER < ,COPY_STAMP IN NUMBER < ,NPIECES IN BINARY_INTEGER < ,DEST IN BINARY_INTEGER < ,PLTFRMFR IN BINARY_INTEGER < ,ENCRYPT IN NUMBER < ,ENC_ALGORITHM IN NUMBER < ,ALLOWTDE IN NUMBER < ,PASSWORD IN VARCHAR2) IS --- > 1718,1719c1684 < NPIECES, FALSE, DEST, PLTFRMFR, < ENCRYPT, ENC_ALGORITHM, ALLOWTDE, PASSWORD); --- > NPIECES, FALSE, DEST, PLTFRMFR); 1764,1768c1729 < ,PLTFRMFR => 0 < ,ENCRYPT => 0 < ,ENC_ALGORITHM => 0 < ,ALLOWTDE => 0 < ,PASSWORD => NULL); --- > ,PLTFRMFR => 0);
SYS.DBMS_BACKUP_RESTORE - PACKAGE (PSU & BP):
7493,7523d7492 < PROCEDURE backupBackupPiece( bpname IN varchar2 < ,fname IN varchar2 < ,handle OUT varchar2 < ,comment OUT varchar2 < ,media OUT varchar2 < ,concur OUT boolean < ,recid OUT number < ,stamp OUT number < ,tag IN varchar2 default NULL < ,params IN varchar2 default NULL < ,media_pool IN binary_integer default 0 < ,reuse IN boolean default FALSE < ,check_logical IN boolean < ,copyno IN binary_integer < ,deffmt IN binary_integer < ,copy_recid IN number < ,copy_stamp IN number < ,npieces IN binary_integer < ,dest IN binary_integer < ,pltfrmfr IN binary_integer < ,encrypt IN number < ,enc_algorithm IN number < ,allowTDE IN number < ,password IN varchar2); < -- < -- Description and return values can be found with original declaration < -- above. The new arguments are: < -- encrypt flag < -- algorithm used for encryption < -- allow TDE < -- password
SYS.DBMS_LOGREP_EXP - PACKAGE BODY (PSU & BP):
2961c2961,2962 < --- > > 2962a2964,2968 > SELECT NVL(MAX(CO.SCN), 0) INTO IGNORE_SCN > FROM ALL_CAPTURE_PREPARED_TABLES CO > WHERE CO.TABLE_OWNER = INSTANCE_EXTENDED_INFO_EXP.SCHEMA > AND CO.TABLE_NAME = INSTANCE_EXTENDED_INFO_EXP.NAME; > 2964,2965c2970,2974 < < IGNORE_SCN := 0; --- > IF (IGNORE_SCN = 0) THEN > DBMS_LOGREP_UTIL.DUMP_TRACE( > 'Warning: ignore_scn is 0 for object '||SCHEMA||'.'||NAME); > END IF; >
12.1.0.2
SYS.DBMS_LOGREP_EXP - PACKAGE BODY (BP):
2966a2967 > 2968a2970,2992 > > > > > > > BEGIN > SELECT /*+ USE_NL_WITH_INDEX(cd I_CDEF2) */ NVL(MAX(CO.SCN), 0) > INTO IGNORE_SCN > FROM SYS.DBA_CAPTURE_PREPARED_TABLES CO > WHERE CO.TABLE_OWNER = INSTANCE_EXTENDED_INFO_EXP.SCHEMA > AND CO.TABLE_NAME = INSTANCE_EXTENDED_INFO_EXP.NAME; > EXCEPTION > WHEN DBMS_STREAMS_DECL.TABLE_OR_VIEW_NOT_EXIST THEN > SELECT /*+ USE_NL_WITH_INDEX(cd I_CDEF2) */ NVL(MAX(CO.SCN), 0) > INTO IGNORE_SCN > FROM SYS.ALL_CAPTURE_PREPARED_TABLES CO > WHERE CO.TABLE_OWNER = INSTANCE_EXTENDED_INFO_EXP.SCHEMA > AND CO.TABLE_NAME = INSTANCE_EXTENDED_INFO_EXP.NAME; > WHEN OTHERS THEN RAISE; > END; > > 2970,2971c2994,2998 < < IGNORE_SCN := 0; --- > IF (IGNORE_SCN = 0) THEN > DBMS_LOGREP_UTIL.DUMP_TRACE( > 'Warning: ignore_scn is 0 for object '||SCHEMA||'.'||NAME); > END IF; >
SYS.DBMS_OPTIM_BUNDLE - PACKAGE BODY (BP):
271a272
> LOC_CON_ID NUMBER := CON_ID;
280c281,283
<
---
> IF CON_ID = 1 THEN
> LOC_CON_ID := 0;
> END IF;
287c290
< OPEN C1(INSTNAME, CON_ID);
---
> OPEN C1(INSTNAME, LOC_CON_ID);
302c305
< OPEN C1(INSTNAME_LOCAL, CON_ID);
---
> OPEN C1(INSTNAME_LOCAL, LOC_CON_ID);
760a764
> LOC_CON_ID NUMBER := CON_ID;
765c769,771
<
---
> IF CON_ID = 1 THEN
> LOC_CON_ID := 0;
> END IF;
769c775
< SQL_CMD := 'select count(*) from sys.gv$spparameter where NAME =''optimizer_features_enable'' and ISSPECIFIED = ''TRUE'' and con_id = ' || CON_ID;
---
> SQL_CMD := 'select count(*) from sys.gv$spparameter where NAME =''optimizer_features_enable'' and ISSPECIFIED = ''TRUE'' and con_id = ' || LOC_CON_ID;
781c787
< ' and SID != ''*'' and ISSPECIFIED = ''TRUE'' and con_id = ' || CON_ID;
---
> ' and SID != ''*'' and ISSPECIFIED = ''TRUE'' and con_id = ' || LOC_CON_ID;
805c811
< OPEN CUR_OFE(INSTNAME, CON_ID);
---
> OPEN CUR_OFE(INSTNAME, LOC_CON_ID);
811c817
< OPEN CUR_OFE('*', CON_ID);
---
> OPEN CUR_OFE('*', LOC_CON_ID);
894,896c900,908
<
<
< OPEN CUR_COUNT(CON_ID);
---
> IF CON_ID = 1 THEN
>
>
>
> OPEN CUR_COUNT(0);
> ELSE
>
> OPEN CUR_COUNT(CON_ID);
> END IF;
SYS.PRVT_ILM - PACKAGE BODY (BP):
214a215 > V_TRACE_LEVEL NUMBER := ILM_DEBUG_NONE; 216,219c217 < < < < IF (ILM_DEBUG_LEVEL > ILM_DEBUG_NONE) THEN --- > IF(ILM_DEBUG_LEVEL >= V_TRACE_LEVEL) THEN 222c220 < TRACE(ILM_DEBUG_LEVEL, 'Start rid End rid'); --- > TRACE(V_TRACE_LEVEL, 'Start rid End rid'); 224c222 < TRACE(ILM_DEBUG_LEVEL, P_TAB_RID(I).START_RID --- > TRACE(V_TRACE_LEVEL, P_TAB_RID(I).START_RID 228,230c226,228 < ELSE < TRACE(ILM_DEBUG_LEVEL, 'No rid ranges generated'); < END IF; --- > ELSE > TRACE(V_TRACE_LEVEL, 'No rid ranges generated'); > END IF; 234d231 < 3009,3010d3005 < < || ' sys.prvt_ilm.setdebug();'
SYS.CDBVIEW - PACKAGE BODY (PSU & BP):
2,4d1 < < function isLegalOwnerViewName(owner IN varchar2, oldview IN varchar2, < newview IN varchar2) return boolean; 21,23d17 < table_not_found EXCEPTION; < PRAGMA exception_init(table_not_found, -942); < 40,43d33 < if (not isLegalOwnerViewName(owner, oldview_name, newview_name)) then < RAISE table_not_found; < end if; < 94,139d83 < -- This function is created to prevent SQL injection. We couldn't use < -- dbms_assert because catcdbviews.sql is called before dbms_assert < -- is created < function isLegalOwnerViewName(owner IN varchar2, oldview IN varchar2, < newview IN varchar2) return boolean as < cCheck number; < cleanOldview varchar2(128); < cleanNewview varchar2(128); < begin < < -- Check if owner already exist < execute immediate 'SELECT COUNT(*) FROM USER$ WHERE NAME = :1' < into cCheck using upper(owner); < if (cCheck = 0) then < RETURN FALSE; < end if; < < -- Check if oldview already exist < execute immediate 'SELECT COUNT(*) FROM OBJ$ WHERE NAME = :1' || < ' AND (TYPE# = 4 OR TYPE# = 2)' < into cCheck using upper(oldview); < if (cCheck = 0) then < RETURN FALSE; < end if; < < -- Check for appropriate newview name < -- The following is allowed for newview name < -- 1. Substitute 'DBA' with 'CDB' < -- 2. Substitute 'AWR_PDB' with 'CDB_HIST' < -- 3. Substitute 'ATTRIBUTES' with 'ATTRIB' < -- 4. Substitute 'DATABASE' with 'CDB' < -- 5. Remove 'REDUCED' < -- 6. Add 'AWRI$_CDB' < cleanOldview := REGEXP_REPLACE(upper(oldview), < 'DBA|DATABASE|_| |HIST|ATTRIB(UTE)?S?|CDB|AWR_PDB|REDUCED'); < cleanNewview := REGEXP_REPLACE(upper(newview), < 'CDB|DATABASE|_| |HIST|ATTRIB(UTE)?S?|AWRI\$'); < < if (cleanOldview = cleanNewview) then < RETURN TRUE; < end if; < < RETURN FALSE; < < end isLegalOwnerViewName; <
12.2.0.1
SYS.PRVTEMX_CELL - PACKAGE BODY (JUL2017RUR):
6915,6924c6915,6918 < sum( < decode( < e.metric_id, < 361, e.metric_value - nvl(b.metric_value,0), < 0)) bytes, < sum( < decode( < e.metric_id, < 360, e.metric_value - nvl(b.metric_value,0), < 0)) rq, --- > sum(decode(e.metric_id, > 361, e.metric_value, 0)) bytes, > sum(decode(e.metric_id, > 360, e.metric_value, 0)) rq, 6926,6935c6920,6923 < sum( < decode( < e.metric_id, < 361, e.metric_value - nvl(b.metric_value,0), < 0)) scrub_by, < sum( < decode( < e.metric_id, < 360, e.metric_value - nvl(b.metric_value,0), < 0)) scrub_rq --- > sum(decode(e.metric_id, > 361, e.metric_value, 0)) scrub_by, > sum(decode(e.metric_id, > 360, e.metric_value, 0)) scrub_rq 6945c6933 < and e.metric_id between 360 and 361 --- > and e.metric_id between 360 and 362
CTXSYS.DRVDDL - PACKAGE BODY (RU):
3607d3606 < STARTDOCID NUMBER := NULL; 3709,3716d3707 < < < IF (IDX.IDX_TYPE = DRIXMD.IDX_TYPE_CONTEXT2 AND < IXP.IXP_ID != 0) THEN < STARTDOCID := DRIXMD.GETNEXTID(IDX.IDX_ID, IXP.IXP_ID); < END IF; < < 3736c3727 < DRVDML.MAINTAINKTAB(IDX, IXP, STARTDOCID); --- > DRVDML.MAINTAINKTAB(IDX, IXP);
DVSYS.KU$_DV_REALM_MEMBER_T - TYPE (RU):
5d4 < oidval raw(16), /* unique id */
DVSYS.KU$_DV_RULE_SET_MEMBER_T - TYPE (RU):
5d4 < oidval raw(16), /* unique id */
DVSYS.SYS_YOID0000072649$ - TYPE (RU):
1,2c1,3 < type "SYS_YOID0000072649$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072649$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E))
DVSYS.SYS_YOID0000072661$ - TYPE (RU):
1,2c1,2 < type "SYS_YOID0000072661$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072661$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE))
DVSYS.SYS_YOID0000072855$ - TYPE (RU):
1,2c1,3 < type "SYS_YOID0000072855$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072855$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E))
DVSYS.SYS_YOID0000072867$ - TYPE (RU):
1,2c1,2 < type "SYS_YOID0000072867$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072867$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE))
DVSYS.KU$_DV_REALM_MEMBER_VIEW - VIEW (RU):
1c1 < select '0','0',sys_guid(), --- > select '0','0',
DVSYS.KU$_DV_RULE_SET_MEMBER_VIEW - VIEW (RU):
1c1 < select '0','0', sys_guid(), --- > select '0','0',
SYS.PRVT_COMPRESSION - PACKAGE (RU):
3,4d2 < <
SYS.CDBVIEW - PACKAGE BODY (RU & JUL2017RUR & OCT2017RUR):
2,4d1 < < function isLegalOwnerViewName(owner IN varchar2, oldview IN varchar2, < newview IN varchar2) return boolean; 25,27d21 < table_not_found EXCEPTION; < PRAGMA exception_init(table_not_found, -942); < 38,41d31 < if (not isLegalOwnerViewName(owner, oldview_name, newview_name)) then < RAISE table_not_found; < end if; < 158,203d147 < -- This function is created to prevent SQL injection. We couldn't use < -- dbms_assert because catcdbviews.sql is called before dbms_assert < -- is created < function isLegalOwnerViewName(owner IN varchar2, oldview IN varchar2, < newview IN varchar2) return boolean as < cCheck number; < cleanOldview varchar2(128); < cleanNewview varchar2(128); < begin < < -- Check if owner already exist < execute immediate 'SELECT COUNT(*) FROM USER$ WHERE NAME = :1' < into cCheck using upper(owner); < if (cCheck = 0) then < RETURN FALSE; < end if; < < -- Check if oldview already exist < execute immediate 'SELECT COUNT(*) FROM OBJ$ WHERE NAME = :1' || < ' AND (TYPE# = 4 OR TYPE# = 2)' < into cCheck using upper(oldview); < if (cCheck = 0) then < RETURN FALSE; < end if; < < -- Check for appropriate newview name < -- The following is allowed for newview name < -- 1. Substitute 'DBA' with 'CDB' < -- 2. Substitute 'AWR_PDB' with 'CDB_HIST' < -- 3. Substitute 'ATTRIBUTES' with 'ATTRIB' < -- 4. Substitute 'DATABASE' with 'CDB' < -- 5. Remove 'REDUCED' < -- 6. Add 'AWRI$_CDB' < cleanOldview := REGEXP_REPLACE(upper(oldview), < 'DBA|DATABASE|_| |HIST|ATTRIB(UTE)?S?|CDB|AWR_PDB|REDUCED'); < cleanNewview := REGEXP_REPLACE(upper(newview), < 'CDB|DATABASE|_| |HIST|ATTRIB(UTE)?S?|AWRI\$'); < < if (cleanOldview = cleanNewview) then < RETURN TRUE; < end if; < < RETURN FALSE; < < end isLegalOwnerViewName; <
SYS.DBMS_APPLY_ADM_INTERNAL - PACKAGE BODY (RU):
2165c2165,2166 < IF ( METHOD_NUM IN (XSDCH_METHOD_MAXIMUM, XSDCH_METHOD_MINIMUM, --- > > IF ((METHOD_NUM IN (XSDCH_METHOD_MAXIMUM, XSDCH_METHOD_MINIMUM, 2167,2168c2168,2171 < XSDCH_METHOD_MIN_AND_EQUALS, XSDCH_METHOD_DELTA) AND < OPERATION_NUM NOT IN (XSDCH_OP_NUM_INSERT, XSDCH_OP_NUM_UPDATE)) THEN --- > XSDCH_METHOD_MIN_AND_EQUALS) AND > OPERATION_NUM NOT IN (XSDCH_OP_NUM_INSERT, XSDCH_OP_NUM_UPDATE)) OR > (METHOD_NUM = XSDCH_METHOD_DELTA AND > OPERATION_NUM != XSDCH_OP_NUM_UPDATE)) THEN
SYS.DBMS_AQADM_SYS - PACKAGE BODY (RU):
2922,2933c2922 < < < < < < < < < < < < TMP_STR := NLS_UPPER(LTRIM(RTRIM(SORT_TAB(I))), 'nls_sort=binary'); --- > TMP_STR := NLS_UPPER(LTRIM(RTRIM(SORT_TAB(I))));
SYS.DBMS_COMPRESSION - PACKAGE BODY (RU):
3,14d2 < < < < KDZACMPLVL_MIN CONSTANT NUMBER := 0; < KDZACMPLVL_BASIC CONSTANT NUMBER := 0; < KDZACMPLVL_UNCMP CONSTANT NUMBER := 1; < KDZACMPLVL_ADVANCED CONSTANT NUMBER := 2; < KDZACMPLVL_HCCQL CONSTANT NUMBER := 3; < KDZACMPLVL_HCCQH CONSTANT NUMBER := 4; < KDZACMPLVL_HCCAL CONSTANT NUMBER := 5; < KDZACMPLVL_HCCAH CONSTANT NUMBER := 6; < 362,363c350,351 < < IF COMP_LEVEL = KDZACMPLVL_UNCMP THEN --- > > IF COMP_LEVEL = 1 THEN 365c353 < ELSIF (COMP_LEVEL IN (KDZACMPLVL_BASIC, KDZACMPLVL_ADVANCED)) THEN --- > ELSIF COMP_LEVEL = 2 THEN 422,426c410 < IF (COMP_LEVEL = KDZACMPLVL_BASIC) THEN < FOR_COMPRESSION :=COMP_BASIC; < ELSIF COMP_LEVEL = KDZACMPLVL_ADVANCED THEN < FOR_COMPRESSION := COMP_ADVANCED; < END IF; --- > FOR_COMPRESSION :=COMP_BASIC; 438c422 < ELSIF COMP_LEVEL = KDZACMPLVL_HCCQL THEN --- > ELSIF COMP_LEVEL = 3 THEN 440c424 < ELSIF COMP_LEVEL = KDZACMPLVL_HCCQH THEN --- > ELSIF COMP_LEVEL = 4 THEN 442c426 < ELSIF COMP_LEVEL = KDZACMPLVL_HCCAL THEN --- > ELSIF COMP_LEVEL = 5 THEN 444c428 < ELSIF COMP_LEVEL = KDZACMPLVL_HCCAH THEN --- > ELSIF COMP_LEVEL = 6 THEN
SYS.DBMS_LOGREP_EXP - PACKAGE BODY (RU):
2993a2994,3018 > > > > > > > > > > BEGIN > SELECT /*+ USE_NL_WITH_INDEX(cd I_CDEF2) */ NVL(MAX(CO.SCN), 0) > INTO IGNORE_SCN > FROM SYS.DBA_CAPTURE_PREPARED_TABLES CO > WHERE CO.TABLE_OWNER = INSTANCE_EXTENDED_INFO_EXP.SCHEMA > AND CO.TABLE_NAME = INSTANCE_EXTENDED_INFO_EXP.NAME; > EXCEPTION > WHEN DBMS_STREAMS_DECL.TABLE_OR_VIEW_NOT_EXIST THEN > SELECT /*+ USE_NL_WITH_INDEX(cd I_CDEF2) */ NVL(MAX(CO.SCN), 0) > INTO IGNORE_SCN > FROM SYS.ALL_CAPTURE_PREPARED_TABLES CO > WHERE CO.TABLE_OWNER = INSTANCE_EXTENDED_INFO_EXP.SCHEMA > AND CO.TABLE_NAME = INSTANCE_EXTENDED_INFO_EXP.NAME; > WHEN OTHERS THEN RAISE; > END; > 2996,2997c3021,3025 < < IGNORE_SCN := 0; --- > IF (IGNORE_SCN = 0) THEN > DBMS_LOGREP_UTIL.DUMP_TRACE( > 'Warning: ignore_scn is 0 for object '||SCHEMA||'.'||NAME); > END IF; >
SYS.DBMS_OPTIM_BUNDLE - PACKAGE BODY (RU):
279a280
> LOC_CON_ID NUMBER := CON_ID;
288c289,291
<
---
> IF CON_ID = 1 THEN
> LOC_CON_ID := 0;
> END IF;
295c298
< OPEN C1(INSTNAME, CON_ID);
---
> OPEN C1(INSTNAME, LOC_CON_ID);
310c313
< OPEN C1(INSTNAME_LOCAL, CON_ID);
---
> OPEN C1(INSTNAME_LOCAL, LOC_CON_ID);
769a773
> LOC_CON_ID NUMBER := CON_ID;
774c778,780
<
---
> IF CON_ID = 1 THEN
> LOC_CON_ID := 0;
> END IF;
778c784
< SQL_CMD := 'select count(*) from sys.gv$spparameter where NAME =''optimizer_features_enable'' and ISSPECIFIED = ''TRUE'' and con_id = ' || CON_ID;
---
> SQL_CMD := 'select count(*) from sys.gv$spparameter where NAME =''optimizer_features_enable'' and ISSPECIFIED = ''TRUE'' and con_id = ' || LOC_CON_ID;
790c796
< ' and SID != ''*'' and ISSPECIFIED = ''TRUE'' and con_id = ' || CON_ID;
---
> ' and SID != ''*'' and ISSPECIFIED = ''TRUE'' and con_id = ' || LOC_CON_ID;
814c820
< OPEN CUR_OFE(INSTNAME, CON_ID);
---
> OPEN CUR_OFE(INSTNAME, LOC_CON_ID);
820c826
< OPEN CUR_OFE('*', CON_ID);
---
> OPEN CUR_OFE('*', LOC_CON_ID);
903,905c909,917
<
<
< OPEN CUR_COUNT(CON_ID);
---
> IF CON_ID = 1 THEN
>
>
>
> OPEN CUR_COUNT(0);
> ELSE
>
> OPEN CUR_COUNT(CON_ID);
> END IF;
SYS.DBMS_PRVTAQIM - PACKAGE BODY (RU):
1714,1725c1714 < < < < < < < < < < < < TMP_STR := NLS_UPPER(LTRIM(RTRIM(SORT_TAB(I))), 'nls_sort=binary'); --- > TMP_STR := NLS_UPPER(LTRIM(RTRIM(SORT_TAB(I))));
SYS.DBMS_RCVMAN - PACKAGE BODY (RU):
17221,17229c17221,17237 < SELECT COUNT(*) INTO LOGEXISTS FROM DUAL < WHERE EXISTS < (SELECT 1 FROM V$ARCHIVED_LOG < WHERE RESETLOGS_TIME = LAST_RESETTIM < AND RESETLOGS_CHANGE# = LAST_RESETNUM < AND ARCHIVED = 'YES' < AND THREAD# = LAST_TRD < AND SEQUENCE# = LAST_SEQ+1); < IF LOGEXISTS > 0 THEN --- > IF LOG.SEQ = 1 THEN > LOGEXISTS := 0; > > SELECT COUNT(*) INTO LOGEXISTS FROM DUAL > WHERE EXISTS > (SELECT 1 FROM V$ARCHIVED_LOG > WHERE RESETLOGS_TIME = LAST_RESETTIM > AND RESETLOGS_CHANGE# = LAST_RESETNUM > AND THREAD# = LAST_TRD > AND SEQUENCE# = LAST_SEQ+1); > IF LOGEXISTS > 0 > THEN > > POSSIBLE_GAP := LAST_SCN; > POSSIBLE_GAP_RLGSCN := LAST_RESETNUM; > END IF; > ELSE
SYS.PRVTEMX_CELL - PACKAGE BODY (RU & OCT2017RUR):
7102,7111c7102,7105 < sum( < decode( < e.metric_id, < 361, e.metric_value - nvl(b.metric_value,0), < 0)) bytes, < sum( < decode( < e.metric_id, < 360, e.metric_value - nvl(b.metric_value,0), < 0)) rq, --- > sum(decode(e.metric_id, > 361, e.metric_value, 0)) bytes, > sum(decode(e.metric_id, > 360, e.metric_value, 0)) rq, 7113,7122c7107,7110 < sum( < decode( < e.metric_id, < 361, e.metric_value - nvl(b.metric_value,0), < 0)) scrub_by, < sum( < decode( < e.metric_id, < 360, e.metric_value - nvl(b.metric_value,0), < 0)) scrub_rq --- > sum(decode(e.metric_id, > 361, e.metric_value, 0)) scrub_by, > sum(decode(e.metric_id, > 360, e.metric_value, 0)) scrub_rq 7132c7120 < and e.metric_id between 360 and 361 --- > and e.metric_id between 360 and 362
SYS.PRVT_ILM - PACKAGE BODY (RU):
229a230 > V_TRACE_LEVEL NUMBER := ILM_DEBUG_NONE; 231,233c232 < < < IF(ILM_DEBUG_LEVEL > ILM_DEBUG_NONE) THEN --- > IF(ILM_DEBUG_LEVEL >= V_TRACE_LEVEL) THEN 236c235 < TRACE(ILM_DEBUG_LEVEL, 'Start rid End rid'); --- > TRACE(V_TRACE_LEVEL, 'Start rid End rid'); 239c238 < TRACE(ILM_DEBUG_LEVEL, P_TAB_RID(I).START_RID --- > TRACE(V_TRACE_LEVEL, P_TAB_RID(I).START_RID 241a241 > 243c243 < TRACE(ILM_DEBUG_LEVEL, 'No rid ranges generated'); --- > TRACE(V_TRACE_LEVEL, 'No rid ranges generated'); 244a245 > 3703,3704d3703 < < || ' sys.prvt_ilm.setdebug();'
SYS.LOGSTDBY_RU_UNSUPPORT_TAB_12_1 - VIEW (RU):
92c92 < where sk.action in (-5, -11) --- > where sk.action=-5 184c184,185 < where t.obj# = c2.obj# --- > where bitand(c2.property, 32) = 32 /* Hidden */ > and t.obj# = c2.obj# 212c213 < where sk.action in (-5, -11) --- > where sk.action=-5 269c270 < where sk.action in (-5, -11) --- > where sk.action=-5
SYS.LOGSTDBY_RU_UNSUPPORT_TAB_12_2 - VIEW (RU):
80,92c80,81 < , 4) = 4) or (c1.type# = 111 and bitand(c1.property, 8) = 8) or (c1.type# in (58 < , 121, 123) and < (exists < (select 1 from obj$ o3, coltype$ ct3, user$ u3 < where u3.user# = o3.owner# < and c1.obj# = ct3.obj# < and c1.intcol# = ct3.intcol# < and ct3.toid = o3.oid$ < and exists (select 1 from system.logstdby$skip_support sk < where sk.action=-11 < and sk.name = u3.name /* type owner */ < and sk.name2 = o3.name /* type name */)))))))))) /* T < able has a Temporal Validity column */ --- > , 4) = 4) or (c1.type# = 111 and bitand(c1.property, 8) = 8))))))) /* Table has > a Temporal Validity column */ 170c159,160 < where t.obj# = c2.obj# --- > where bitand(c2.property, 32) = 32 /* Hidden */ > and t.obj# = c2.obj# 184,195c174,176 < (c2.type# = 111 and bitand(c2.property, 8) = 8) or (c2.type# in < (58, 121, 123) and < (exists < (select 1 from obj$ o3, coltype$ ct3, user$ u3 < where u3.user# = o3.owner# < and c2.obj# = ct3.obj# < and c2.intcol# = ct3.intcol# < and ct3.toid = o3.oid$ < and exists (select 1 from system.logstdby$skip_support sk < where sk.action=-11 < and sk.name = u3.name /* type owner */ < and sk.name2 = o3.name /* type name */)))))))) or --- > (c2.type# = 111 and bitand(c2.property, 8) = 8) > /* REF */ > )))) or
SYS.LOGSTDBY_SUPPORT_TAB_12_1 - VIEW (RU):
85c85 < where sk.action in (-5, -11) --- > where sk.action=-5 195c195,196 < where t.obj# = c2.obj# --- > where bitand(c2.property, 32) = 32 /* Hidden */ > and t.obj# = c2.obj# 223c224 < where sk.action in (-5, -11) --- > where sk.action=-5 285c286 < where sk.action in (-5, -11) --- > where sk.action=-5
SYS.LOGSTDBY_SUPPORT_TAB_12_2 - VIEW (RU):
87,97c87 < = 'FALSE')) or (c1.type# in (58, 121, 123) and < (exists < (select 1 from obj$ o3, coltype$ ct3, user$ u3 < where u3.user# = o3.owner# < and c1.obj# = ct3.obj# < and c1.intcol# = ct3.intcol# < and ct3.toid = o3.oid$ < and exists (select 1 from system.logstdby$skip_support sk < where sk.action=-11 < and sk.name = u3.name /* type owner */ < and sk.name2 = o3.name /* type name */)))))))))) --- > = 'FALSE')))))))) 201c191,192 < where t.obj# = c2.obj# --- > where bitand(c2.property, 32) = 32 /* Hidden */ > and t.obj# = c2.obj# 227,237c218,219 < = 'FALSE')) or (c2.type# in (58, 121, 123) and < (exists < (select 1 from obj$ o3, coltype$ ct3, user$ u3 < where u3.user# = o3.owner# < and c2.obj# = ct3.obj# < and c2.intcol# = ct3.intcol# < and ct3.toid = o3.oid$ < and exists (select 1 from system.logstdby$skip_support sk < where sk.action=-11 < and sk.name = u3.name /* type owner */ < and sk.name2 = o3.name /* type name */)))))))) or --- > = 'FALSE')) /* REF */ > )))) or
SYS.LOGSTDBY_UNSUPPORT_TAB_12_1 - VIEW (RU):
92c92 < where sk.action in (-5, -11) --- > where sk.action=-5 187c187,188 < where t.obj# = c2.obj# --- > where bitand(c2.property, 32) = 32 /* Hidden */ > and t.obj# = c2.obj# 215c216 < where sk.action in (-5, -11) --- > where sk.action=-5 272c273 < where sk.action in (-5, -11) --- > where sk.action=-5
SYS.LOGSTDBY_UNSUPPORT_TAB_12_2 - VIEW (RU):
96,107c96,97 < = 'FALSE')) or (c1.type# in (58, 121, 123) and < (exists < (select 1 from obj$ o3, coltype$ ct3, user$ u3 < where u3.user# = o3.owner# < and c1.obj# = ct3.obj# < and c1.intcol# = ct3.intcol# < and ct3.toid = o3.oid$ < and exists (select 1 from system.logstdby$skip_support sk < where sk.action=-11 < and sk.name = u3.name /* type owner */ < and sk.name2 = o3.name /* type name */)))))))))) /* 0 < x00020000 table is used as an AQ queue table */ --- > = 'FALSE')))))))) /* 0x00020000 table is used as an AQ queue table > */ 197c187,188 < where t.obj# = c2.obj# --- > where bitand(c2.property, 32) = 32 /* Hidden */ > and t.obj# = c2.obj# 223,233c214,215 < = 'FALSE')) or (c2.type# in (58, 121, 123) and < (exists < (select 1 from obj$ o3, coltype$ ct3, user$ u3 < where u3.user# = o3.owner# < and c2.obj# = ct3.obj# < and c2.intcol# = ct3.intcol# < and ct3.toid = o3.oid$ < and exists (select 1 from system.logstdby$skip_support sk < where sk.action=-11 < and sk.name = u3.name /* type owner */ < and sk.name2 = o3.name /* type name */)))))))) or --- > = 'FALSE')) /* REF */ > )))) or
SYS.OGG_SUPPORT_TAB_11_2 - VIEW (RU):
40,57d39 < /* ADT typed table with BFILE attribute */ < and ((bitand(t.property, 1) = 1 and < c.type# = 114 /* BFILE */ and < exists(select 1 < from sys.col$ c1 < where c1.obj#=t.obj# and < c1.name = 'SYS_NC_ROWINFO$' and < c1.type# = 121)) < /* Relational table with ADT column having BFILE attribute */ < or (bitand(t.property, 1) = 0 and < c.type# = 114 /* BFILE */ and < bitand(c.property, 32) = 32 /* hidden */ and < exists (select 1 < from sys.col$ c1 < where c1.obj#=t.obj# and < c1.col# = c.col# and < bitand(c1.property, 32) = 0 /* not hidden */ and < c1.type# = 121)) 59c41 < or ((c.type# in (8,24,58,112,113,114,115,121,123) --- > and ((c.type# in (8,24,58,112,113,114,115,121,123) 61,80c43,61 < and (bitand(t.property, 1) = 0 /* not a typed table or */ < and 0 = (select count(*) from sys.col$ c2 < where t.obj# = c2.obj# < and bitand(c2.property, 32) != 32 /* Not hidden */ < and bitand(c2.property, 8) != 8 /* Not virtual */ < and bitand(c2.property, 128) != 128 /* not stored in lob */ < and (c2.type# in ( 1, /* VARCHAR2 */ < 2, /* NUMBER */ < 12, /* DATE */ < 23, /* RAW */ < 96, /* CHAR */ < 100, /* BINARY FLOAT */ < 101, /* BINARY DOUBLE */ < 180, /* TIMESTAMP (..) */ < 181, /* TIMESTAMP(..) WITH TIME ZONE */ < 182, /* INTERVAL YEAR(..) TO MONTH */ < 183, /* INTERVAL DAY(..) TO SECOND(..) */ < 208, /* UROWID */ < 231) /* TIMESTAMP(..) WITH LOCAL TZ */ < )))))) --- > and (bitand(t.property, 1) = 0 /* not a typed table or */ > and 0 = (select count(*) from sys.col$ c2 > where t.obj# = c2.obj# > and bitand(c2.property, 32) != 32 /* Not hidden */ > and bitand(c2.property, 8) != 8 /* Not virtual */ > and bitand(c2.property, 128) != 128 /* not stored in lob */ > and (c2.type# in ( 1, /* VARCHAR2 */ > 2, /* NUMBER */ > 12, /* DATE */ > 23, /* RAW */ > 96, /* CHAR */ > 100, /* BINARY FLOAT */ > 101, /* BINARY DOUBLE */ > 180, /* TIMESTAMP (..) */ > 181, /* TIMESTAMP(..) WITH TIME ZONE */ > 182, /* INTERVAL YEAR(..) TO MONTH */ > 183, /* INTERVAL DAY(..) TO SECOND(..) */ > 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ > ))))) 128d108 < 208, /* UROWID */
SYS.OGG_SUPPORT_TAB_11_2B - VIEW (RU):
41,58d40 < /* ADT typed table with BFILE attribute */ < and ((bitand(t.property, 1) = 1 and < c.type# = 114 /* BFILE */ and < exists(select 1 < from sys.col$ c1 < where c1.obj#=t.obj# and < c1.name = 'SYS_NC_ROWINFO$' and < c1.type# = 121)) < /* Relational table with ADT column having BFILE attribute */ < or (bitand(t.property, 1) = 0 and < c.type# = 114 /* BFILE */ and < bitand(c.property, 32) = 32 /* hidden */ and < exists (select 1 < from sys.col$ c1 < where c1.obj#=t.obj# and < c1.col# = c.col# and < bitand(c1.property, 32) = 0 /* not hidden */ and < c1.type# = 121)) 60c42 < or ((c.type# in (8,24,58,112,113,114,115,121,123) --- > and ((c.type# in (8,24,58,112,113,114,115,121,123) 62,81c44,62 < and (bitand(t.property, 1) = 0 /* not a typed table or */ < and 0 = (select count(*) from sys.col$ c2 < where t.obj# = c2.obj# < and bitand(c2.property, 32) != 32 /* Not hidden */ < and bitand(c2.property, 8) != 8 /* Not virtual */ < and bitand(c2.property, 128) != 128 /* not stored in lob */ < and (c2.type# in ( 1, /* VARCHAR2 */ < 2, /* NUMBER */ < 12, /* DATE */ < 23, /* RAW */ < 96, /* CHAR */ < 100, /* BINARY FLOAT */ < 101, /* BINARY DOUBLE */ < 180, /* TIMESTAMP (..) */ < 181, /* TIMESTAMP(..) WITH TIME ZONE */ < 182, /* INTERVAL YEAR(..) TO MONTH */ < 183, /* INTERVAL DAY(..) TO SECOND(..) */ < 208, /* UROWID */ < 231) /* TIMESTAMP(..) WITH LOCAL TZ */ < )))))) --- > and (bitand(t.property, 1) = 0 /* not a typed table or */ > and 0 = (select count(*) from sys.col$ c2 > where t.obj# = c2.obj# > and bitand(c2.property, 32) != 32 /* Not hidden */ > and bitand(c2.property, 8) != 8 /* Not virtual */ > and bitand(c2.property, 128) != 128 /* not stored in lob */ > and (c2.type# in ( 1, /* VARCHAR2 */ > 2, /* NUMBER */ > 12, /* DATE */ > 23, /* RAW */ > 96, /* CHAR */ > 100, /* BINARY FLOAT */ > 101, /* BINARY DOUBLE */ > 180, /* TIMESTAMP (..) */ > 181, /* TIMESTAMP(..) WITH TIME ZONE */ > 182, /* INTERVAL YEAR(..) TO MONTH */ > 183, /* INTERVAL DAY(..) TO SECOND(..) */ > 231) /* TIMESTAMP(..) WITH LOCAL TIME ZONE */ > ))))) 124d104 < 208, /* UROWID */
SYS.OGG_SUPPORT_TAB_12_1 - VIEW (RU):
66,96d65 < /* ADT typed table with BFILE attribute */ < or (bitand(t.property, 1) = 1 and < c.type# = 114 /* BFILE */ and < exists(select 1 < from sys.col$ c1 < where c1.obj# = t.obj# and < c1.name = 'SYS_NC_ROWINFO$' and < c1.type# = 121)) < /* Relational table with ADT column having BFILE attribute */ < or (bitand(t.property, 1) = 0 and < c.type# = 114 /* BFILE */ and < bitand(c.property, 32)=32 /* hidden */ and < exists (select 1 < from sys.col$ c1 < where c1.obj# = t.obj# and < c1.col# = c.col# and < bitand(c1.property, 32) = 0 /* not hidden */ and < c1.type# = 121)) < /* Any table (relational or typed) that has an unsupported */ < /* built-in ADT */ < or (c.type# in (58, 121, 123) and < (exists < (select 1 from obj$ o3, coltype$ ct3, user$ u3 < where u3.user# = o3.owner# < and c.obj# = ct3.obj# < and c.intcol# = ct3.intcol# < and ct3.toid = o3.oid$ < and exists (select 1 from system.logstdby$skip_support sk < where sk.action in (-11) < and sk.name = u3.name /* type owner */ < and sk.name2 = o3.name /* type name */)))) 117d85 < 208, /* UROWID */ 162c130 < where sk.action in (-5, -11) --- > where sk.action=-5 201d168 < 208, /* UROWID */ 223c190,191 < where t.obj# = c2.obj# --- > where bitand(c2.property, 32) = 32 /* Hidden */ > and t.obj# = c2.obj# 251c219 < where sk.action in (-5, -11) --- > where sk.action=-5 289c257 < where sk.action in (-5, -11) --- > where sk.action=-5
SYS.OGG_SUPPORT_TAB_12_2 - VIEW (RU):
67,97d66 < /* ADT typed table with BFILE attribute */ < or (bitand(t.property, 1) = 1 and < c.type# = 114 /* BFILE */ and < exists(select 1 < from sys.col$ c1 < where c1.obj#=t.obj# and < c1.name = 'SYS_NC_ROWINFO$' and < c1.type# = 121)) < /* Relational table with ADT column having BFILE attribute */ < or (bitand(t.property, 1) = 0 and < c.type# = 114 /* BFILE */ and < bitand(c.property, 32) = 32 /* hidden */ and < exists (select 1 < from sys.col$ c1 < where c1.obj#=t.obj# and < c1.col# = c.col# and < bitand(c1.property, 32) = 0 /* not hidden */ and < c1.type# = 121)) < /* Any table (relational or typed) that has an unsupported */ < /* built-in ADT */ < or (c.type# in (58, 121, 123) and < (exists < (select 1 from obj$ o3, coltype$ ct3, user$ u3 < where u3.user# = o3.owner# < and c.obj# = ct3.obj# < and c.intcol# = ct3.intcol# < and ct3.toid = o3.oid$ < and exists (select 1 from system.logstdby$skip_support sk < where sk.action=-11 < and sk.name = u3.name /* type owner */ < and sk.name2 = o3.name /* type name */)))) 118d86 < 208, /* UROWID */ 177d144 < 208, /* UROWID */ 195c162,163 < where t.obj# = c2.obj# --- > where bitand(c2.property, 32) = 32 /* Hidden */ > and t.obj# = c2.obj# 209c177,179 < (c2.type# = 111 and bitand(c2.property, 8) = 8))))) or --- > (c2.type# = 111 and bitand(c2.property, 8) = 8) > /* REF */ > )))) or
Useful Links:
- Download Assistant: MOS Note: 2118136.2
- 2018 January CPU: http://www.oracle.com/technetwork/security-advisory/cpujan2018-3236628.html
Have you enjoyed? Please leave a comment or give a 👍!
2 comments
Hi RJ,
Could you please post the SQL statement to find which objects were modified and created after Patch
Thanks
JK
Author
Hi JK,
It's not a SQL. I calculate the checksum of objects before and after the patch and compare them.
Take a look in the official oracle doc for DBMS_CRYPTO and research how to calculate hashes using it. This is the first step!
Regards,
RJ