This post is also available in: Português
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.
To check previous changes, follow the links below:
- Dissecting 181016 BP, PSU, RU and RUR
- Dissecting 180717 BP, PSU, RU and RUR
- Dissecting 180417 BP, PSU, RU and RUR
- Dissecting 180116 BP, PSU, RU and RUR
- Dissecting 171017 BP, PSU, RU and RUR
So what oracle internal objects were changed in January 2019 - 190115?
VERSION PATCH OWNER TYPE TOTAL ---------- ---------- ------------------------------ --------------- ---------- 11.2.0.4 BP SYS PACKAGE BODY 1 12.1.0.2 PSU & BP SYS PACKAGE BODY 1 12.2.0.1 JUL2018RUR SYS PACKAGE 2 12.2.0.1 JUL2018RUR SYS PACKAGE BODY 2 12.2.0.1 OCT2018RUR DVSYS TYPE 5 12.2.0.1 OCT2018RUR SYS PACKAGE 2 12.2.0.1 OCT2018RUR SYS PACKAGE BODY 7 12.2.0.1 RU SYS PACKAGE 1 12.2.0.1 RU SYS PACKAGE BODY 2 18.0.0.0 18.3RUR SYS PACKAGE 4 18.0.0.0 18.3RUR SYS PACKAGE BODY 3 18.0.0.0 18.4RUR SYS PACKAGE 4 18.0.0.0 18.4RUR SYS PACKAGE BODY 3 18.0.0.0 RU GSMADMIN_INTERNAL PACKAGE BODY 2 18.0.0.0 RU SYS PACKAGE 5 18.0.0.0 RU SYS PACKAGE BODY 6 18.0.0.0 RU SYS TYPE 4 18.0.0.0 RU SYS TYPE BODY 4 18.0.0.0 RU XDB PACKAGE BODY 1
And created?
-
P.S: Nothing was added for 11.2 /12.1 / 12.2 / 18c in Oracle objects.
So which are exactly the objects modified by 190115?
VERSION PATCH OWNER TYPE NAME ACTION ---------- --------------- ------------------------------ --------------- ------------------------------ ------ 11.2.0.4 BP SYS PACKAGE BODY DBMS_PARALLEL_EXECUTE_INTERNAL MOD 12.1.0.2 PSU & BP SYS PACKAGE BODY CDBVIEW MOD 12.2.0.1 OCT2018RUR DVSYS TYPE SYS_YOID0000072649$ MOD 12.2.0.1 OCT2018RUR DVSYS TYPE SYS_YOID0000072661$ MOD 12.2.0.1 OCT2018RUR DVSYS TYPE SYS_YOID0000072667$ MOD 12.2.0.1 OCT2018RUR DVSYS TYPE SYS_YOID0000072694$ MOD 12.2.0.1 OCT2018RUR DVSYS TYPE SYS_YOID0000072697$ MOD 12.2.0.1 OCT2018RUR SYS PACKAGE BODY DBMS_XSTREAM_ADM_INTERNAL MOD 12.2.0.1 OCT2018RUR SYS PACKAGE BODY DBMS_XSTREAM_ADM_UTL MOD 12.2.0.1 OCT2018RUR SYS PACKAGE BODY DBMS_XSTREAM_AUTH_IVK MOD 12.2.0.1 OCT2018RUR SYS PACKAGE BODY DBMS_XSTREAM_GG_INTERNAL MOD 12.2.0.1 OCT2018RUR SYS PACKAGE BODY DBMS_XSTREAM_UTL_IVK MOD 12.2.0.1 OCT2018RUR & SYS PACKAGE CDBVIEW MOD JUL2018RUR 12.2.0.1 OCT2018RUR & SYS PACKAGE CDBVIEW_INTERNAL MOD JUL2018RUR 12.2.0.1 OCT2018RUR & SYS PACKAGE BODY CDBVIEW MOD JUL2018RUR 12.2.0.1 OCT2018RUR & SYS PACKAGE BODY CDBVIEW_INTERNAL MOD JUL2018RUR 12.2.0.1 RU SYS PACKAGE DBMS_AUTOTASK_PRVT MOD 12.2.0.1 RU SYS PACKAGE BODY DBMS_AUTOTASK_PRVT MOD 12.2.0.1 RU SYS PACKAGE BODY DBMS_SQLPATCH MOD 18.0.0.0 RU GSMADMIN_INTERNAL PACKAGE BODY DBMS_GSM_COMMON MOD 18.0.0.0 RU GSMADMIN_INTERNAL PACKAGE BODY DBMS_GSM_DBADMIN MOD 18.0.0.0 RU SYS PACKAGE DBMS_AUTOTASK_PRVT MOD 18.0.0.0 RU SYS PACKAGE BODY DBMS_AUTOTASK_PRVT MOD 18.0.0.0 RU SYS PACKAGE BODY DBMS_STATS MOD 18.0.0.0 RU SYS PACKAGE BODY DBMS_STATS_ADVISOR MOD 18.0.0.0 RU SYS TYPE JDOM_T MOD 18.0.0.0 RU SYS TYPE JSON_ARRAY_T MOD 18.0.0.0 RU SYS TYPE JSON_ELEMENT_T MOD 18.0.0.0 RU SYS TYPE JSON_OBJECT_T MOD 18.0.0.0 RU SYS TYPE BODY JDOM_T MOD 18.0.0.0 RU SYS TYPE BODY JSON_ARRAY_T MOD 18.0.0.0 RU SYS TYPE BODY JSON_ELEMENT_T MOD 18.0.0.0 RU SYS TYPE BODY JSON_OBJECT_T MOD 18.0.0.0 RU XDB PACKAGE BODY DBMS_SODA_ADMIN MOD 18.0.0.0 RU & 18.4RUR & SYS PACKAGE CDBVIEW MOD 18.3RUR 18.0.0.0 RU & 18.4RUR & SYS PACKAGE CDBVIEW_INTERNAL MOD 18.3RUR 18.0.0.0 RU & 18.4RUR & SYS PACKAGE DBMS_BACKUP_RESTORE MOD 18.3RUR 18.0.0.0 RU & 18.4RUR & SYS PACKAGE DBMS_REGISTRY MOD 18.3RUR 18.0.0.0 RU & 18.4RUR & SYS PACKAGE BODY CDBVIEW MOD 18.3RUR 18.0.0.0 RU & 18.4RUR & SYS PACKAGE BODY CDBVIEW_INTERNAL MOD 18.3RUR 18.0.0.0 RU & 18.4RUR & SYS PACKAGE BODY DBMS_RCVMAN MOD 18.3RUR
What changed for each? (click to see the changes)
- 11.2.0.4
- 12.1.0.2
- 12.2.0.1
- DVSYS.SYS_YOID0000072649$ - TYPE 12cR2 (OCT2018RUR)
- DVSYS.SYS_YOID0000072661$ - TYPE 12cR2 (OCT2018RUR)
- DVSYS.SYS_YOID0000072667$ - TYPE 12cR2 (OCT2018RUR)
- DVSYS.SYS_YOID0000072694$ - TYPE 12cR2 (OCT2018RUR)
- DVSYS.SYS_YOID0000072697$ - TYPE 12cR2 (OCT2018RUR)
- SYS.CDBVIEW - PACKAGE 12cR2 (OCT2018RUR & JUL2018RUR)
- SYS.CDBVIEW_INTERNAL - PACKAGE 12cR2 (OCT2018RUR & JUL2018RUR)
- SYS.DBMS_AUTOTASK_PRVT - PACKAGE 12cR2 (RU)
- SYS.CDBVIEW - PACKAGE BODY 12cR2 (OCT2018RUR & JUL2018RUR)
- SYS.CDBVIEW_INTERNAL - PACKAGE BODY 12cR2 (OCT2018RUR & JUL2018RUR)
- SYS.DBMS_AUTOTASK_PRVT - PACKAGE BODY 12cR2 (RU)
- SYS.DBMS_SQLPATCH - PACKAGE BODY 12cR2 (RU)
- SYS.DBMS_XSTREAM_ADM_INTERNAL - PACKAGE BODY 12cR2 (OCT2018RUR)
- SYS.DBMS_XSTREAM_ADM_UTL - PACKAGE BODY 12cR2 (OCT2018RUR)
- SYS.DBMS_XSTREAM_AUTH_IVK - PACKAGE BODY 12cR2 (OCT2018RUR)
- SYS.DBMS_XSTREAM_GG_INTERNAL - PACKAGE BODY 12cR2 (OCT2018RUR)
- SYS.DBMS_XSTREAM_UTL_IVK - PACKAGE BODY 12cR2 (OCT2018RUR)
- 18.0.0.0
- GSMADMIN_INTERNAL.DBMS_GSM_COMMON - PACKAGE BODY 18c (RU)
- GSMADMIN_INTERNAL.DBMS_GSM_DBADMIN - PACKAGE BODY 18c (RU)
- SYS.CDBVIEW - PACKAGE 18c (RU & 18.4RUR & 18.3RUR)
- SYS.CDBVIEW_INTERNAL - PACKAGE 18c (RU & 18.4RUR & 18.3RUR)
- SYS.DBMS_AUTOTASK_PRVT - PACKAGE 18c (RU)
- SYS.DBMS_BACKUP_RESTORE - PACKAGE 18c (RU)
- SYS.DBMS_BACKUP_RESTORE - PACKAGE 18c (18.3RUR)
- SYS.DBMS_BACKUP_RESTORE - PACKAGE 18c (18.4RUR)
- SYS.DBMS_REGISTRY - PACKAGE 18c (18.4RUR)
- SYS.DBMS_REGISTRY - PACKAGE 18c (18.3RUR)
- SYS.DBMS_REGISTRY - PACKAGE 18c (RU)
- SYS.CDBVIEW - PACKAGE BODY 18c (RU & 18.4RUR & 18.3RUR)
- SYS.CDBVIEW_INTERNAL - PACKAGE BODY 18c (RU & 18.4RUR & 18.3RUR)
- SYS.DBMS_AUTOTASK_PRVT - PACKAGE BODY 18c (RU)
- SYS.DBMS_RCVMAN - PACKAGE BODY 18c (18.3RUR)
- SYS.DBMS_RCVMAN - PACKAGE BODY 18c (RU)
- SYS.DBMS_RCVMAN - PACKAGE BODY 18c (18.4RUR)
- SYS.DBMS_STATS - PACKAGE BODY 18c (RU)
- SYS.DBMS_STATS_ADVISOR - PACKAGE BODY 18c (RU)
- SYS.JDOM_T - TYPE 18c (RU)
- SYS.JSON_ARRAY_T - TYPE 18c (RU)
- SYS.JSON_ELEMENT_T - TYPE 18c (RU)
- SYS.JSON_OBJECT_T - TYPE 18c (RU)
- SYS.JDOM_T - TYPE BODY 18c (RU)
- SYS.JSON_ARRAY_T - TYPE BODY 18c (RU)
- SYS.JSON_ELEMENT_T - TYPE BODY 18c (RU)
- SYS.JSON_OBJECT_T - TYPE BODY 18c (RU)
- XDB.DBMS_SODA_ADMIN - PACKAGE BODY 18c (RU)
Changed Objects
P.S:
> = Added Lines
< = Removed Lines
11.2.0.4
681a682,686 > > IF C.CUR_SIZE = 0 THEN > GOTO DONE; > END IF; > 685a691,693 > <<DONE>> > > 687c695 < IF (BUFFERED_CHUNK# = 0) THEN --- > IF (BUFFERED_CHUNKS.COUNT = 0) THEN
12.1.0.2
2a3,7 > type tabs_array is varray(11) of dbms_id; > sens_table_array tabs_array; > type sens_tabs is table of boolean index by dbms_id; > sens_table_list sens_tabs; > 22a28,29 > insuff_privilege EXCEPTION; > PRAGMA exception_init(insuff_privilege, -1031); 47a55,67 > -- > -- Bug 27445727 : Certain SYS owned tables like USER$, LINK$ etc. are out > -- of bounds even for DBA level users. So we should not allow cloning such > -- tables into a Container_Data view and be able to bypass this protection. > -- > -- Since this procedure does not support quoted identifiers, we need not > -- worry about canonicalizing the owner or oldview_name arguments before > -- comparing against SYS and the array of sensitive tables. > -- > if (upper_owner = 'SYS' and sens_table_list.exists(upper_oldview)) then > RAISE insuff_privilege; > end if; > 139a160,177 > -- > -- Initialize the list of SYS owned sensitive tables which should > -- not be allowed to be shadow-copied even within Create_CDBView. > -- > -- Once ER 24598663 gets implemented, these entries will be part of a > -- SYS owned metadata table and instead of hard-coding the list, we will > -- be populating the list by fetching it from the table. > -- > begin > sens_table_array := tabs_array('ENC$', 'LINK$', 'USER$', 'DEFAULT_PWD$', > 'XS$VERIFIERS', 'USER_HISTORY$', > 'HIST_HEAD$', 'HISTGRM$','CDB_LOCAL_ADMINAUTH$', > 'PDB_CREATE$','PDB_SYNC$'); > > for i in 1..11 loop > sens_table_list(sens_table_array(i)) := TRUE; > end loop; >
12.2.0.1
2c2 < type "SYS_YOID0000072649$" as object( "SYS_NC00001$" RAW(16)) --- > type "SYS_YOID0000072649$" as object( "SYS_NC00001$" VARCHAR2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(128 BYTE))
2c2 < type "SYS_YOID0000072661$" as object( "SYS_NC00001$" RAW(16)) --- > type "SYS_YOID0000072661$" as object( "SYS_NC00001$" VARCHAR2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE))
2c2 < type "SYS_YOID0000072667$" as object( "SYS_NC00001$" RAW(16)) --- > type "SYS_YOID0000072667$" as object( "SYS_NC00001$" VARCHAR2(128 BYTE), "SYS_NC00002$" VARCHAR2(100 BYTE), "SYS_NC00003$" VARCHAR2(128 BYTE), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_NC00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" NUMBER)
2c2 < type "SYS_YOID0000072694$" as object( "SYS_NC00001$" RAW(16)) --- > type "SYS_YOID0000072694$" as object( "SYS_NC00001$" VARCHAR2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(128 BYTE), "SYS_NC00004$" VARCHAR2(128 BYTE))
2c2 < type "SYS_YOID0000072697$" as object( "SYS_NC00001$" RAW(16)) --- > type "SYS_YOID0000072697$" as object( "SYS_NC00001$" VARCHAR2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(100 BYTE), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_NC00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" VARCHAR2(128 BYTE))
1c1 < package CDBView as --- > package CDBView AUTHID CURRENT_USER as
10a11,13 > procedure create_cdbview(chk_upgrd IN boolean, owner IN varchar2, > oldview_name IN varchar2, newview_name IN varchar2); >
43a44,53 > > > > > > > > > PROCEDURE REFRESH_CLIENT_CONFIG; >
3,10c3 < function isLegalOwnerViewName(owner IN varchar2, oldview IN varchar2, < newview IN varchar2) return boolean; < -- Create the cdb view < -- private helper procedure to create the cdb view < -- Note that quotes should not be added around owner, oldview_name and < -- newview_name before create_cdbview is invoked since all three are used < -- as literals to query dictionary views. < procedure create_cdbview(chk_upgrd IN boolean, owner IN varchar2, --- > procedure create_cdbview(chk_upgrd IN boolean, owner IN varchar2, 12,133c5,8 < sqlstmt varchar2(4000); < col_name varchar2(128); < comments varchar2(4000); < col_type number; < upper_owner varchar2(128); < upper_oldview varchar2(128); < quoted_owner varchar2(130); -- 2 more than size of owner < quoted_oldview varchar2(130); -- 2 more than size of oldview_name < quoted_newview varchar2(130); -- 2 more than size of newview_name < unsupp_col_condition varchar2(4000); < colcomments varchar2(4000); < unsupp_col_count number; < colcommentscur SYS_REFCURSOR; < table_not_found EXCEPTION; < PRAGMA exception_init(table_not_found, -942); < < < cursor tblcommentscur is select c.comment$ < from sys.obj$ o, sys.user$ u, sys.com$ c < where o.name = upper_oldview and u.name = upper_owner < and o.obj# = c.obj# and o.owner#=u.user# < and (o.type# = 4 or o.type# = 2) < and c.col# is null; < < begin < < if (not isLegalOwnerViewName(owner, oldview_name, newview_name)) then < RAISE table_not_found; < end if; < < -- convert owner and view names to upper case < upper_owner := upper(owner); < upper_oldview := upper(oldview_name); < < quoted_owner := '"' || upper_owner || '"'; < quoted_oldview := '"' || upper_oldview || '"'; < quoted_newview := '"' || upper(newview_name) || '"'; < < -- Create cdb view < sqlstmt := 'CREATE OR REPLACE VIEW ' || < quoted_owner || '.' || quoted_newview || < ' CONTAINER_DATA AS SELECT k.*, k.CON$NAME, k.CDB$NAME' || < ' FROM CONTAINERS(' || quoted_owner || '.' || quoted_oldview || ') k'; < < --dbms_output.put_line(sqlstmt); < execute immediate sqlstmt; < < -- table and column comments < open tblcommentscur; < fetch tblcommentscur into comments; < comments := replace(comments, '''',''''''); < sqlstmt := 'comment on table ' || quoted_owner || '.' || quoted_newview || < ' is ''' || comments || ' in all containers'''; < -- dbms_output.put_line(sqlstmt); < execute immediate sqlstmt; < close tblcommentscur; < < sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || < '.CON_ID is ''container id'''; < -- dbms_output.put_line(sqlstmt); < execute immediate sqlstmt; < < sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || < '.CON$NAME is ''Container Name'''; < -- dbms_output.put_line(sqlstmt); < execute immediate sqlstmt; < < sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || < '.CDB$NAME is ''Database Name'''; < -- dbms_output.put_line(sqlstmt); < execute immediate sqlstmt; < < colcomments := 'select c.name, co.comment$ ' || < 'from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co ' || < 'where o.name = ''' || upper_oldview || ''' ' || < 'and u.name = ''' || upper_owner || ''' ' || < 'and o.owner# = u.user# and (o.type# = 4 or o.type# = 2) ' || < 'and o.obj# = c.obj# ' || < 'and c.obj# = co.obj# and c.intcol# = co.col# ' || < -- skip hidden column < 'and bitand(c.property, 32) = 0 '|| < -- skip null comment < 'and co.comment$ is not null'; < -- skip Long, Nested Table, Varray columns < unsupp_col_condition := 'c.type# = 8 or c.type# = 122 or c.type# = 123 ' || < -- skip ADT and REF columns < 'or c.type# = 121 or c.type# = 111 ' || < -- Bug 20683085: skip Opaque Type column except < -- xmltype stored as LOB. Check xmltype as lob using < -- property bit KQLDCOP2_XSLB. < -- Bug 23083309: if there are unsupported columns, < -- then XMLType column is skipped (hidden XMLType lob < -- column is already handled) < 'or (c.type# = 58 and ' || < '((bitand(c.property, ' || < 'power(2,32)*4194304)<>power(2,32)*4194304) '|| < 'or :u > 0)) ' || < -- Bug 21785587: skip long raw < 'or c.type# = 24'; < < sqlstmt := colcomments || ' and (' || unsupp_col_condition || ')'; < < unsupp_col_count := 0; < EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || sqlstmt ||')' < INTO unsupp_col_count USING unsupp_col_count; < < open colcommentscur for colcomments ||' and not ('|| < unsupp_col_condition ||')' < USING unsupp_col_count; < loop < fetch colcommentscur into col_name, comments; < exit when colcommentscur%NOTFOUND; < < comments := replace(comments, '''',''''''); < sqlstmt := 'comment on column ' || < quoted_owner || '.' || quoted_newview || '.' || < col_name || ' is ''' || comments || ''''; < -- dbms_output.put_line(sqlstmt); < execute immediate sqlstmt; < end loop; < close colcommentscur; < end; --- > begin > CDBView_internal.create_cdbview(chk_upgrd, owner, oldview_name, > newview_name); > end; 137,140c12,19 < tablename dbms_id; < colname dbms_id; < stmt varchar2(400); < retval varchar2(4000); --- > tablename dbms_id; > colname dbms_id; > stmt varchar2(400); > retval varchar2(4000); > isuser_sys boolean := FALSE; > scr_priv number := 0; > insuff_priv exception; > pragma exception_init(insuff_priv, -1031); 141a21,47 > -- > -- The Current_User should either be SYS or be in possession of > -- SELECT_CATALOG_ROLE to be able to query view definitions. > -- This is how Dbms_Metadata.Get_DDL operates, so we are trying > -- to have the same privilege model here too. If none of this is > -- true, we will raise ORA-1031 error. > -- > -- We are doing a direct query against SESSION_ROLES dictionary table > -- instead of using Dbms_Priv_Capture.Ses_Has_Role_Priv function, as > -- the package Dbms_Priv_Capture may not be available by the time this > -- package CDBView gets created. Also use dyn SQL for the same reason. > -- > if (sys_context('userenv','current_userid') = 0) then > isuser_sys := TRUE; > else > begin > execute immediate 'select count(*) from sys.session_roles where > role = ''SELECT_CATALOG_ROLE''' into scr_priv; > exception > when others then NULL; > end; > end if; > > if NOT (isuser_sys OR scr_priv > 0) then > raise insuff_priv; > end if; > 157,203d62 < < -- 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; <
1c1,217 < package body CDBView_internal as --- > package body CDBView_Internal is > > MAX_SENS_TAB_COUNT CONSTANT number := 11; > type tabs_array is varray(MAX_SENS_TAB_COUNT) of dbms_id; > sens_table_array tabs_array; > type sens_tabs is table of boolean index by dbms_id; > sens_table_list sens_tabs; > > function isLegalOwnerViewName(owner IN varchar2, oldview IN varchar2, > newview IN varchar2) return boolean; > -- Create the cdb view > -- private helper procedure to create the cdb view > -- Note that quotes should not be added around owner, oldview_name and > -- newview_name before create_cdbview is invoked since all three are used > -- as literals to query dictionary views. > procedure create_cdbview(chk_upgrd IN boolean, owner IN varchar2, > oldview_name IN varchar2, newview_name IN varchar2) as > sqlstmt varchar2(4000); > col_name dbms_id; > comments varchar2(4000); > col_type number; > upper_owner varchar2(128); > upper_oldview varchar2(128); > newview dbms_id := newview_name; > quoted_owner dbms_quoted_id; -- 2 more than size of owner > quoted_oldview dbms_quoted_id; -- 2 more than size of oldview_name > quoted_newview dbms_quoted_id; -- 2 more than size of newview_name > > unsupp_col_condition varchar2(4000); > colcomments varchar2(4000); > unsupp_col_count number; > colcommentscur SYS_REFCURSOR; > table_not_found EXCEPTION; > PRAGMA exception_init(table_not_found, -942); > insuff_privilege EXCEPTION; > PRAGMA exception_init(insuff_privilege, -1031); > > cursor tblcommentscur is select c.comment$ > from sys.obj$ o, sys.user$ u, sys.com$ c > where o.name = upper_oldview and u.name = upper_owner > and o.obj# = c.obj# and o.owner#=u.user# > and (o.type# = 4 or o.type# = 2) > and c.col# is null; > > begin > > -- convert owner and view names to upper case > upper_owner := upper(owner); > upper_oldview := upper(oldview_name); > > -- > -- No need to check for ownername, viewname correctness, if the Current_User > -- is SYS, as would be the case with most of Create_CDBView invocations > -- across RDBMS code. > -- > -- As per recommendation from Mark, it is not safe to give a free pass to > -- SYS user, in case CDBView.Create_CDBView gets invoked from within a SYS > -- owned definer rights PL/SQL code and the inputs to those could be user > -- provided and potentially malicious. > -- > if (not isLegalOwnerViewName(upper_owner, upper_oldview, newview_name)) then > RAISE table_not_found; > end if; > > -- > -- Bug 27445727 : Certain SYS owned tables like USER$, LINK$ etc. are out > -- of bounds even for DBA level users. So we should not allow cloning such > -- tables into a Container_Data view and be able to bypass this protection. > -- > -- Since this procedure does not support quoted identifiers, we need not > -- worry about canonicalizing the owner or oldview_name arguments before > -- comparing against SYS and the array of sensitive tables. > -- > if (upper_owner = 'SYS' and sens_table_list.exists(upper_oldview)) then > RAISE insuff_privilege; > end if; > > quoted_owner := '"' || upper_owner || '"'; > quoted_oldview := '"' || upper_oldview || '"'; > quoted_newview := '"' || upper(newview_name) || '"'; > > -- Create cdb view > sqlstmt := 'CREATE OR REPLACE VIEW ' || > quoted_owner || '.' || quoted_newview || > ' CONTAINER_DATA AS SELECT k.*, k.CON$NAME, k.CDB$NAME' || > ' FROM CONTAINERS(' || quoted_owner || '.' || quoted_oldview || ') k'; > > --dbms_output.put_line(sqlstmt); > execute immediate sqlstmt; > > -- table and column comments > open tblcommentscur; > fetch tblcommentscur into comments; > comments := replace(comments, '''',''''''); > sqlstmt := 'comment on table ' || quoted_owner || '.' || quoted_newview || > ' is ''' || comments || ' in all containers'''; > -- dbms_output.put_line(sqlstmt); > execute immediate sqlstmt; > close tblcommentscur; > > sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || > '.CON_ID is ''container id'''; > -- dbms_output.put_line(sqlstmt); > execute immediate sqlstmt; > > sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || > '.CON$NAME is ''Container Name'''; > -- dbms_output.put_line(sqlstmt); > execute immediate sqlstmt; > > sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || > '.CDB$NAME is ''Database Name'''; > -- dbms_output.put_line(sqlstmt); > execute immediate sqlstmt; > > colcomments := 'select c.name, co.comment$ ' || > 'from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co ' || > 'where o.name = ''' || upper_oldview || ''' ' || > 'and u.name = ''' || upper_owner || ''' ' || > 'and o.owner# = u.user# and (o.type# = 4 or o.type# = 2) ' || > 'and o.obj# = c.obj# ' || > 'and c.obj# = co.obj# and c.intcol# = co.col# ' || > -- skip hidden column > 'and bitand(c.property, 32) = 0 '|| > -- skip null comment > 'and co.comment$ is not null'; > -- skip Long, Nested Table, Varray columns > unsupp_col_condition := 'c.type# = 8 or c.type# = 122 or c.type# = 123 ' || > -- skip ADT and REF columns > 'or c.type# = 121 or c.type# = 111 ' || > -- Bug 20683085: skip Opaque Type column except > -- xmltype stored as LOB. Check xmltype as lob using > -- property bit KQLDCOP2_XSLB. > -- Bug 23083309: if there are unsupported columns, > -- then XMLType column is skipped (hidden XMLType lob > -- column is already handled) > 'or (c.type# = 58 and ' || > '((bitand(c.property, ' || > 'power(2,32)*4194304)<>power(2,32)*4194304) '|| > 'or :u > 0)) ' || > -- Bug 21785587: skip long raw > 'or c.type# = 24'; > > sqlstmt := colcomments || ' and (' || unsupp_col_condition || ')'; > > unsupp_col_count := 0; > EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || sqlstmt ||')' > INTO unsupp_col_count USING unsupp_col_count; > > open colcommentscur for colcomments ||' and not ('|| > unsupp_col_condition ||')' > USING unsupp_col_count; > loop > fetch colcommentscur into col_name, comments; > exit when colcommentscur%NOTFOUND; > > comments := replace(comments, '''',''''''); > sqlstmt := 'comment on column ' || > quoted_owner || '.' || quoted_newview || '.' || > col_name || ' is ''' || comments || ''''; > -- dbms_output.put_line(sqlstmt); > execute immediate sqlstmt; > end loop; > close colcommentscur; > end; > > -- 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; > ownerId number; > cleanOldview dbms_id; > cleanNewview dbms_id; > begin > > -- Check if owner already exist > -- USER$ contains both users and roles, exclude roles from the check > begin > execute immediate 'SELECT USER# FROM USER$ WHERE TYPE# = 1 AND NAME = :1' > into ownerId using owner; > exception > when no_data_found then return false; > end; > > -- Check if oldview already exist, qualify with owner# to avoid ORA-1422 > -- error,if two dictionary view with the same name exists across different > -- schemas like DBA_DV_STATUS which exists in both SYS and DVSYS schemas. > begin > execute immediate 'SELECT 1 FROM OBJ$ WHERE NAME = :1' || > ' AND TYPE# in (2, 4) and owner# = :2' > into cCheck using oldview, ownerId; > exception > when no_data_found then return false; > end; > > -- 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; 17c233,251 < end; --- > -- > -- Initialize the list of SYS owned sensitive tables which should > -- not be allowed to be shadow-copied even within Create_CDBView. > -- > -- Once ER 24598663 gets implemented, these entries will be part of a > -- SYS owned metadata table and instead of hard-coding the list, we will > -- be populating the list by fetching it from the table. > -- > begin > sens_table_array := tabs_array('ENC$', 'LINK$', 'USER$', 'DEFAULT_PWD$', > 'XS$VERIFIERS', 'USER_HISTORY$', > 'HIST_HEAD$', 'HISTGRM$','CDB_LOCAL_ADMINAUTH$', > 'PDB_CREATE$','PDB_SYNC$'); > > for i in 1..MAX_SENS_TAB_COUNT loop > sens_table_list(sens_table_array(i)) := TRUE; > end loop; > > end CDBView_Internal;
171a172,186 > > > > > > > > > PROCEDURE REFRESH_CLIENT_CONFIG IS > EXTERNAL LANGUAGE C > NAME "ketrcc_refresh_client_config" > WITH CONTEXT > PARAMETERS(CONTEXT) > LIBRARY DBMS_AUTOTASK_PRVT_LIB; >
67a68,70 > > ORIG_CONTAINER_NAME VARCHAR2(128) := NULL; > 77a81,106 > > > > FUNCTION CHECK_CONTAINER_SWITCH(E_MSG OUT VARCHAR2) RETURN BOOLEAN IS > L_CONTAINER_NAME VARCHAR2(128); > CONTAINER_SWITCHED BOOLEAN; > BEGIN > SELECT SYS_CONTEXT('userenv', 'con_name') > INTO L_CONTAINER_NAME > FROM DUAL; > > DEBUG_PUT_LINE('l_container_name '||L_CONTAINER_NAME||' orig_container_name '||ORIG_CONTAINER_NAME); > > IF ORIG_CONTAINER_NAME IS NULL OR > L_CONTAINER_NAME != ORIG_CONTAINER_NAME THEN > E_MSG := 'Unexpected container change to ' || L_CONTAINER_NAME; > CONTAINER_SWITCHED := TRUE; > ELSE > E_MSG := NULL; > CONTAINER_SWITCHED := FALSE; > END IF; > > RETURN CONTAINER_SWITCHED; > END; > > 150c179,180 < S_NOTOK_COMPONENTS = NOTOK_COMPONENTS --- > S_NOTOK_COMPONENTS = NOTOK_COMPONENTS, > S_CONTAINER_NAME = ORIG_CONTAINER_NAME 163a194 > L_MSG VARCHAR2(300); 194c225,226 < S_NOTOK_COMPONENTS --- > S_NOTOK_COMPONENTS, > S_CONTAINER_NAME 224c256,257 < NOTOK_COMPONENTS --- > NOTOK_COMPONENTS, > ORIG_CONTAINER_NAME 227a261,265 > > IF CHECK_CONTAINER_SWITCH(L_MSG) THEN > RAISE_APPLICATION_ERROR(-20003, L_MSG); > END IF; > 259d296 < 285c322 < RETURN '$Header: rdbms/src/server/dict/plsicds/prvtsqlpatch.sql /st_rdbms_12.2.0.1.0dbbp/3 2018/05/02 15:37:07 surman Exp $'; --- > RETURN '$Header: rdbms/src/server/dict/plsicds/prvtsqlpatch.sql /st_rdbms_12.2.0.1.0dbbp/4 2018/10/09 03:33:10 sanagara Exp $'; 603a641,642 > CONTAINER_SWITCHED BOOLEAN := FALSE; > L_MSG VARCHAR2(300); 608,610c647,655 < UPDATE DBA_REGISTRY_SQLPATCH < SET STATUS = 'END' < WHERE ROWID = CURRENT_REGISTRY_ROWID; --- > > > CONTAINER_SWITCHED := CHECK_CONTAINER_SWITCH(L_MSG); > > IF NOT CONTAINER_SWITCHED THEN > > UPDATE DBA_REGISTRY_SQLPATCH > SET STATUS = 'END' > WHERE ROWID = CURRENT_REGISTRY_ROWID; 616,617c661,662 < WHERE S_CURRENT_PATCH_ID = CURRENT_PATCH.PATCH_ID < AND S_CURRENT_PATCH_UID = CURRENT_PATCH.PATCH_UID; --- > WHERE S_CURRENT_PATCH_ID = CURRENT_PATCH.PATCH_ID > AND S_CURRENT_PATCH_UID = CURRENT_PATCH.PATCH_UID; 619c664,665 < COMMIT; --- > COMMIT; > END IF; 648a695,700 > > > IF CONTAINER_SWITCHED THEN > RAISE_APPLICATION_ERROR(-20003, L_MSG); > END IF; > 661a714 > L_MSG VARCHAR2(300); 667a721,725 > > IF CHECK_CONTAINER_SWITCH(L_MSG) THEN > RAISE_APPLICATION_ERROR(-20003, L_MSG); > END IF; > 670,671c728,734 < DEBUG_PUT_LINE(' initialize not complete, never install'); < RETURN NOTHING_SQL; --- > > > > > > > RAISE_APPLICATION_ERROR(-20004, 'Uninitialized state in install_file'); 1013a1077,1082 > > > SELECT SYS_CONTEXT('userenv', 'con_name') > INTO ORIG_CONTAINER_NAME > FROM DUAL; >
445c445 < END IF; --- > END IF; 496c496 < INCREMENT := INCREMENT + 1; --- > INCREMENT := INCREMENT + 1; 583c583 < END IF; --- > END IF; 2727c2727 < --- > 3310c3310 < HAS_ROLE NUMBER := 0; --- > HAS_ROLE NUMBER := 0; 3433c3433 < --- > 3484c3484 < --- >
687c687 < SCN OCINUMBER, SCN INDICATOR SB2, --- > SCN OCINUMBER, SCN INDICATOR SB2, 689,690c689,690 < POS OCIRAW, POS INDICATOR SB2, < POS MAXLEN SB4, POS LENGTH SB4) --- > POS OCIRAW, POS INDICATOR SB2, > POS MAXLEN SB4, POS LENGTH SB4) 1526c1526 < DBMS_XSTREAM_ADM_UTL.CONVERT_SCN_TO_POS(PROCESSED_LWM_SCN, --- > DBMS_XSTREAM_ADM_UTL.CONVERT_SCN_TO_POS(PROCESSED_LWM_SCN,
792c792 < INCREMENT := INCREMENT + 1; --- > INCREMENT := INCREMENT + 1; 908c908 < END IF; --- > END IF;
77a78 > PRBA NUMBER := RBA; 87c88 < SEQ# = SEQNO AND RBA = RBA AND INDEX# = INDEX_NUM; --- > SEQ# = SEQNO AND RBA = PRBA AND INDEX# = INDEX_NUM; 91c92 < SEQ# = SEQNO AND RBA = RBA AND INDEX# = INDEX_NUM; --- > SEQ# = SEQNO AND RBA = PRBA AND INDEX# = INDEX_NUM;
1438c1438 < ' queue=' || QUEUE_NAME || ' src_db=' || SOURCE_DATABASE || --- > ' queue=' || QUEUE_NAME || ' src_db=' || SOURCE_DATABASE || 2908c2908 < END IF; --- > END IF;
18.0.0.0
7763a7764 > 7775,7778c7776,7788 < CUR_SCHEMA IN VARCHAR2) IS < CANON_JOB_NAME DBMS_ID; < CANON_JOB_SUBNAME DBMS_ID; < CANON_JOB_OWNER DBMS_ID; --- > CUR_SCHEMA IN VARCHAR2, > LOCK_NAME OUT DBMS_ID, > LOCK_HANDLE OUT VARCHAR2, > RECIPIENTS_LIST OUT DBMS_UTILITY.LNAME_ARRAY, > NEW_RAISE_EVENTS_NUM OUT PLS_INTEGER, > CANON_JOB_NAME IN OUT DBMS_ID, > CANON_JOB_SUBNAME IN OUT DBMS_ID, > CANON_JOB_OWNER IN OUT DBMS_ID, > CUR_FLAGS OUT NUMBER, > DOES_SUB_EXIST OUT BOOLEAN, > DOES_REG_EXIST OUT BOOLEAN, > REAL_JB_NAME IN VARCHAR2 > ) IS 7782d7791 < NEW_RAISE_EVENTS_NUM PLS_INTEGER := 0; 7791d7799 < EVENT_10862 PLS_INTEGER := 0; 7798,7799d7805 < LOCK_NAME DBMS_ID; < LOCK_HANDLE VARCHAR2(128); 7802d7807 < RECIPIENTS_LIST DBMS_UTILITY.LNAME_ARRAY; 7805d7809 < CUR_FLAGS NUMBER; 7807c7811 < REAL_JOB_NAME VARCHAR2(2 * ORA_MAX_NAME_LEN + 5) := JOB_NAME; --- > REAL_JOB_NAME VARCHAR2(2 * ORA_MAX_NAME_LEN + 5) := REAL_JB_NAME; 7817c7821,7822 < SYS.DBMS_ISCHED.TRACE_EMAIL('Entering add_job_email_notification'); --- > SYS.DBMS_ISCHED.TRACE_EMAIL('Entering ' || > 'dbms_isched.add_job_email_notification'); 7819a7825,7836 > IF DO_TRC THEN > SYS.DBMS_ISCHED.TRACE_EMAIL('Current user: ' || > SYS_CONTEXT('USERENV','CURRENT_USER') ); > SYS.DBMS_ISCHED.TRACE_EMAIL('Session user: ' || > SYS_CONTEXT('USERENV','SESSION_USER') ); > SYS.DBMS_ISCHED.TRACE_EMAIL('Current schema: ' || > SYS_CONTEXT('USERENV','CURRENT_SCHEMA') ); > END IF; > > NEW_RAISE_EVENTS_NUM := 0; > CUR_FLAGS := 0; > 7844,7868c7861,7862 < RESOLVE3_NAME(REAL_JOB_NAME, CANON_JOB_NAME, CANON_JOB_SUBNAME, < CANON_JOB_OWNER, CUR_SCHEMA); < < < FULL_JOB_NAME := '"'||CANON_JOB_OWNER||'"."'||CANON_JOB_NAME||'"'; < < < IF INVOKER != CANON_JOB_OWNER AND INVOKER != 'SYS' AND < BITAND(SYS_PRIVS, 2) = 0 < THEN < < BEGIN < SYS.DBMS_ISCHED_UTL.CHECK_OBJECT_PRIVS(CANON_JOB_NAME, CANON_JOB_OWNER, 'JOB', < INVOKER, 'ALTER', TRUE, SYS_PRIVS); < EXCEPTION WHEN OTHERS THEN < < IF SQLCODE = -23306 AND CANON_JOB_SUBNAME IS NULL < AND REAL_JOB_NAME = JOB_NAME THEN < REAL_JOB_NAME := '"'||CUR_SCHEMA||'".' || JOB_NAME; < GOTO TRY_RESOLVE_AGAIN; < ELSE < RAISE; < END IF; < END; < END IF; --- > FULL_JOB_NAME := DBMS_ASSERT.ENQUOTE_NAME(CANON_JOB_OWNER, FALSE) ||'.'|| > DBMS_ASSERT.ENQUOTE_NAME(CANON_JOB_NAME , FALSE); 7892c7886,7890 < REAL_JOB_NAME := '"'||CUR_SCHEMA||'".' || JOB_NAME; --- > REAL_JOB_NAME := DBMS_ASSERT.ENQUOTE_NAME(CUR_SCHEMA, FALSE) || > '.' || JOB_NAME; > > RESOLVE3_NAME(REAL_JOB_NAME, CANON_JOB_NAME, CANON_JOB_SUBNAME, > CANON_JOB_OWNER, CUR_SCHEMA); 7981,7990d7978 < < < < DBMS_SYSTEM.READ_EV(10862, EVENT_10862); < IF EVENT_10862 = 0 THEN < EXECUTE IMMEDIATE < 'ALTER SESSION SET EVENTS ''10862 TRACE NAME CONTEXT FOREVER, LEVEL 1'''; < END IF; < < 7992,7993c7980,7981 < 'select count(*) from sys.dba_QUEUE_SUBSCRIBERS where owner=''SYS'' < and queue_name=''SCHEDULER$_EVENT_QUEUE'' and consumer_name=:lname' --- > 'select count(*) from sys.user_QUEUE_SUBSCRIBERS where > queue_name=''SCHEDULER$_EVENT_QUEUE'' and consumer_name=:lname' 7996,8003c7984,7985 < BEGIN < < DBMS_AQADM.ADD_SUBSCRIBER < (QUEUE_NAME => 'SYS.SCHEDULER$_EVENT_QUEUE', < SUBSCRIBER => SYS.AQ$_AGENT(LOCK_NAME, NULL, NULL)); < EXCEPTION WHEN OTHERS THEN < IF SQLCODE = -24034 THEN NULL; < ELSE --- > DOES_SUB_EXIST := FALSE; > BEGIN 8005,8007c7987,7994 < IF EVENT_10862 = 0 THEN < EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ' || < '''10862 TRACE NAME CONTEXT OFF''' ; --- > DBMS_AQADM.ADD_SUBSCRIBER > (QUEUE_NAME => 'SYS.SCHEDULER$_EVENT_QUEUE', > SUBSCRIBER => SYS.AQ$_AGENT(LOCK_NAME, NULL, NULL)); > EXCEPTION WHEN OTHERS THEN > IF SQLCODE = -24034 THEN NULL; > ELSE > LOCK_ERROR := DBMS_LOCK.RELEASE(LOCKHANDLE=>LOCK_HANDLE); > RAISE; 8009,8019c7996,7998 < < LOCK_ERROR := DBMS_LOCK.RELEASE(LOCKHANDLE=>LOCK_HANDLE); < RAISE; < END IF; < END; < END IF; < < < IF EVENT_10862 = 0 THEN < EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS ' || < '''10862 TRACE NAME CONTEXT OFF''' ; --- > END; > ELSE > DOES_SUB_EXIST := TRUE; 8025,8029d8003 < IF ROW_COUNT = 0 THEN < < SYS.DBMS_AQADM_SYS.ENABLE_DB_ACCESS(LOCK_NAME,INVOKER,TRUE); < END IF; < 8033a8008,8012 > EXCEPTION WHEN OTHERS THEN > LOCK_ERROR := DBMS_LOCK.RELEASE(LOCKHANDLE=>LOCK_HANDLE); > RAISE; > END; > 8035,8042c8014,8020 < < < REGINFO := SYS.AQ$_REG_INFO('SYS.SCHEDULER$_EVENT_QUEUE:"'||LOCK_NAME||'"', < DBMS_AQ.NAMESPACE_AQ, < 'plsql://SYS.SCHEDULER$_JOB_EVENT_HANDLER', < NULL); < REGLIST := SYS.AQ$_REG_INFO_LIST(REGINFO); < DBMS_AQ.REGISTER ( REGLIST, 1 ); --- > DOES_REG_EXIST := FALSE; > ELSE > DOES_REG_EXIST := TRUE; > END IF; > > IF DO_TRC THEN > SYS.DBMS_ISCHED.TRACE_EMAIL('Exiting dbms_isched.add_job_email_notification'); 8045a8024,8078 > > END; > > PROCEDURE ADD_JOB_EMAIL_NOTIFICATION_PH3 > ( > RECIPIENTS_LIST IN DBMS_UTILITY.LNAME_ARRAY, > NEW_RAISE_EVENTS_NUM IN PLS_INTEGER, > CANON_JOB_NAME IN DBMS_ID, > CANON_JOB_SUBNAME IN DBMS_ID, > CANON_JOB_OWNER IN DBMS_ID, > INVOKER IN VARCHAR2, > SENDER IN VARCHAR2, > SUBJECT IN VARCHAR2, > BODY IN VARCHAR2, > FILTER_CONDITION IN VARCHAR2, > LOCK_NAME IN DBMS_ID, > LOCK_HANDLE IN VARCHAR2, > CUR_FLAGS IN NUMBER, > RECIPIENTS IN VARCHAR2, > EVENTS IN VARCHAR2 > ) IS > TYPE EVENT_NAME_LIST IS TABLE OF DBMS_ID; > TYPE EVENT_NUMBER_LIST IS TABLE OF NUMBER; > EVENT_NAMES EVENT_NAME_LIST := EVENT_NAME_LIST('JOB_STARTED', > 'JOB_SUCCEEDED', 'JOB_FAILED', 'JOB_BROKEN', 'JOB_COMPLETED', > 'JOB_STOPPED', 'JOB_SCH_LIM_REACHED', 'JOB_DISABLED', > 'JOB_CHAIN_STALLED','JOB_OVER_MAX_DUR','JOB_RUN_COMPLETED','JOB_ALL_EVENTS'); > EVENT_NUMBERS EVENT_NUMBER_LIST := > EVENT_NUMBER_LIST(1,2,4,8,16,32,64,128,256,512,2+4+32,1024-1); > EVENT_10862 PLS_INTEGER := 0; > LOCK_ERROR PLS_INTEGER; > FULL_JOB_NAME VARCHAR2(3 * ORA_MAX_NAME_LEN + 8); > DO_TRC BOOLEAN := FALSE; > EV_27402 PLS_INTEGER; > BEGIN > > DBMS_SYSTEM.READ_EV(27402, EV_27402); > IF BITAND(EV_27402,128)=128 THEN DO_TRC := TRUE; END IF; > > > IF DO_TRC THEN > SYS.DBMS_ISCHED.TRACE_EMAIL('Entered add_job_email_notification_ph3'); > END IF; > > IF DO_TRC THEN > SYS.DBMS_ISCHED.TRACE_EMAIL('Current user: ' || > SYS_CONTEXT('USERENV','CURRENT_USER') ); > SYS.DBMS_ISCHED.TRACE_EMAIL('Session user: ' || > SYS_CONTEXT('USERENV','SESSION_USER') ); > SYS.DBMS_ISCHED.TRACE_EMAIL('Current schema: ' || > SYS_CONTEXT('USERENV','CURRENT_SCHEMA') ); > END IF; > > BEGIN > 8152a8186,8189 > FULL_JOB_NAME := DBMS_ASSERT.ENQUOTE_NAME(CANON_JOB_OWNER, FALSE) ||'.'|| > DBMS_ASSERT.ENQUOTE_NAME(CANON_JOB_NAME , FALSE); > > 8159c8196,8197 < FULL_JOB_NAME := FULL_JOB_NAME ||'."'||CANON_JOB_SUBNAME||'"'; --- > FULL_JOB_NAME := FULL_JOB_NAME ||'.'|| > DBMS_ASSERT.ENQUOTE_NAME(CANON_JOB_SUBNAME,FALSE); 8165a8204,8208 > > > IF DO_TRC THEN > SYS.DBMS_ISCHED.TRACE_EMAIL('Exiting add_job_email_notification_ph3'); > END IF;
1575a1576 > LOC_PVALUE VARCHAR2(4096); 1595a1597,1603 > LOC_PVALUE := DBMS_ASSERT.NOOP(PVALUE); > > > IF LOC_PVALUE IS NULL OR LOC_PVALUE = '' THEN > LOC_PVALUE := ''''''; > END IF; > 1597,1599c1605,1607 < 'ALTER SYSTEM SET "'|| < DBMS_ASSERT.NOOP(PNAME)|| < '"='||DBMS_ASSERT.NOOP(PVALUE)|| --- > 'ALTER SYSTEM SET '|| > DBMS_ASSERT.ENQUOTE_NAME(PNAME)|| > '='||DBMS_ASSERT.NOOP(LOC_PVALUE)|| 1604,1606c1612,1614 < EXECUTE IMMEDIATE 'ALTER SYSTEM SET "'|| < DBMS_ASSERT.NOOP(PNAME)|| < '"='||DBMS_ASSERT.NOOP(PVALUE)|| --- > EXECUTE IMMEDIATE 'ALTER SYSTEM SET '|| > DBMS_ASSERT.ENQUOTE_NAME(PNAME)|| > '='||DBMS_ASSERT.NOOP(LOC_PVALUE)|| 1683,1685c1691,1693 < 'ALTER SYSTEM SET "'|| < DBMS_ASSERT.NOOP(PNAME)|| < '"='||DBMS_ASSERT.NOOP(LOC_PVALUE)|| --- > 'ALTER SYSTEM SET '|| > DBMS_ASSERT.ENQUOTE_NAME(PNAME)|| > '='||DBMS_ASSERT.NOOP(LOC_PVALUE)|| 1690,1692c1698,1700 < EXECUTE IMMEDIATE 'ALTER SYSTEM SET "'|| < DBMS_ASSERT.NOOP(PNAME)|| < '"='||DBMS_ASSERT.NOOP(LOC_PVALUE)|| --- > EXECUTE IMMEDIATE 'ALTER SYSTEM SET '|| > DBMS_ASSERT.ENQUOTE_NAME(PNAME)|| > '='||DBMS_ASSERT.NOOP(LOC_PVALUE)|| 1770,1772c1778,1780 < 'ALTER SYSTEM SET "'|| < DBMS_ASSERT.NOOP(PNAME)|| < '"='||DBMS_ASSERT.NOOP(LOC_PVALUE)|| --- > 'ALTER SYSTEM SET '|| > DBMS_ASSERT.ENQUOTE_NAME(PNAME)|| > '='||DBMS_ASSERT.NOOP(LOC_PVALUE)|| 1777,1779c1785,1787 < EXECUTE IMMEDIATE 'ALTER SYSTEM SET "'|| < DBMS_ASSERT.NOOP(PNAME)|| < '"='||DBMS_ASSERT.NOOP(LOC_PVALUE)|| --- > EXECUTE IMMEDIATE 'ALTER SYSTEM SET '|| > DBMS_ASSERT.ENQUOTE_NAME(PNAME)|| > '='||DBMS_ASSERT.NOOP(LOC_PVALUE)||
1341,1343c1341,1343 < DBMS_GSM_COMMON.SETDBPARAMETER( '_dbgroup_name', ''); < DBMS_GSM_COMMON.RESETDBPARAMETER('_dbgroup_name'); < DBMS_GSM_COMMON.SETDBPARAMETER( '_shd_reptype', ''); --- > DBMS_GSM_COMMON.SETDBPARAMETER( '_shardgroup_name', ''); > DBMS_GSM_COMMON.RESETDBPARAMETER('_shardgroup_name'); > DBMS_GSM_COMMON.SETDBPARAMETER( '_shd_reptype', TO_CHAR(0));
1c1 < package CDBView as --- > package CDBView AUTHID CURRENT_USER as
10a11,13 > procedure create_cdbview(chk_upgrd IN boolean, owner IN varchar2, > oldview_name IN varchar2, newview_name IN varchar2); >
43a44,53 > > > > > > > > > PROCEDURE REFRESH_CLIENT_CONFIG; >
12c12 < bannerVersion CONSTANT VARCHAR2(15) := '18.04.00.00'; --- > bannerVersion CONSTANT VARCHAR2(15) := '18.05.00.00'; 24,26c24,26 < fsn CONSTANT NUMBER := instr('18.04.00.00', '.'); < msn CONSTANT NUMBER := instr('18.04.00.00', '.', fsn + 1); < psn CONSTANT NUMBER := instr('18.04.00.00', '.', msn + 1); --- > fsn CONSTANT NUMBER := instr('18.05.00.00', '.'); > msn CONSTANT NUMBER := instr('18.05.00.00', '.', fsn + 1); > psn CONSTANT NUMBER := instr('18.05.00.00', '.', msn + 1); 29c29 < to_number(substr('18.04.00.00', 1, fsn - 1)); --- > to_number(substr('18.05.00.00', 1, fsn - 1)); 31c31 < to_number(substr('18.04.00.00', fsn + 1, msn - fsn - 1)); --- > to_number(substr('18.05.00.00', fsn + 1, msn - fsn - 1)); 33c33 < to_number(substr('18.04.00.00', msn + 1, psn - msn - 1)); --- > to_number(substr('18.05.00.00', msn + 1, psn - msn - 1)); 35c35 < to_number(substr('18.04.00.00', psn + 1)); --- > to_number(substr('18.05.00.00', psn + 1));
12c12 < bannerVersion CONSTANT VARCHAR2(15) := '18.04.00.00'; --- > bannerVersion CONSTANT VARCHAR2(15) := '18.03.02.00'; 24,26c24,26 < fsn CONSTANT NUMBER := instr('18.04.00.00', '.'); < msn CONSTANT NUMBER := instr('18.04.00.00', '.', fsn + 1); < psn CONSTANT NUMBER := instr('18.04.00.00', '.', msn + 1); --- > fsn CONSTANT NUMBER := instr('18.03.02.00', '.'); > msn CONSTANT NUMBER := instr('18.03.02.00', '.', fsn + 1); > psn CONSTANT NUMBER := instr('18.03.02.00', '.', msn + 1); 29c29 < to_number(substr('18.04.00.00', 1, fsn - 1)); --- > to_number(substr('18.03.02.00', 1, fsn - 1)); 31c31 < to_number(substr('18.04.00.00', fsn + 1, msn - fsn - 1)); --- > to_number(substr('18.03.02.00', fsn + 1, msn - fsn - 1)); 33c33 < to_number(substr('18.04.00.00', msn + 1, psn - msn - 1)); --- > to_number(substr('18.03.02.00', msn + 1, psn - msn - 1)); 35c35 < to_number(substr('18.04.00.00', psn + 1)); --- > to_number(substr('18.03.02.00', psn + 1));
12c12 < bannerVersion CONSTANT VARCHAR2(15) := '18.04.00.00'; --- > bannerVersion CONSTANT VARCHAR2(15) := '18.04.01.00'; 24,26c24,26 < fsn CONSTANT NUMBER := instr('18.04.00.00', '.'); < msn CONSTANT NUMBER := instr('18.04.00.00', '.', fsn + 1); < psn CONSTANT NUMBER := instr('18.04.00.00', '.', msn + 1); --- > fsn CONSTANT NUMBER := instr('18.04.01.00', '.'); > msn CONSTANT NUMBER := instr('18.04.01.00', '.', fsn + 1); > psn CONSTANT NUMBER := instr('18.04.01.00', '.', msn + 1); 29c29 < to_number(substr('18.04.00.00', 1, fsn - 1)); --- > to_number(substr('18.04.01.00', 1, fsn - 1)); 31c31 < to_number(substr('18.04.00.00', fsn + 1, msn - fsn - 1)); --- > to_number(substr('18.04.01.00', fsn + 1, msn - fsn - 1)); 33c33 < to_number(substr('18.04.00.00', msn + 1, psn - msn - 1)); --- > to_number(substr('18.04.01.00', msn + 1, psn - msn - 1)); 35c35 < to_number(substr('18.04.00.00', psn + 1)); --- > to_number(substr('18.04.01.00', psn + 1));
13c13 < '18.4.0.0.0'; --- > '18.4.1.0.0';
13c13 < '18.4.0.0.0'; --- > '18.3.2.0.0';
13c13 < '18.4.0.0.0'; --- > '18.5.0.0.0';
3,10c3 < function isLegalOwnerViewName(owner IN varchar2, oldview IN varchar2, < newview IN varchar2) return varchar2; < -- Create the cdb view < -- private helper procedure to create the cdb view < -- Note that quotes should not be added around owner, oldview_name and < -- newview_name before create_cdbview is invoked since all three are used < -- as literals to query dictionary views. < procedure create_cdbview(chk_upgrd IN boolean, owner IN varchar2, --- > procedure create_cdbview(chk_upgrd IN boolean, owner IN varchar2, 12,133c5,8 < sqlstmt varchar2(4000); < col_name varchar2(128); < comments varchar2(4000); < col_type number; < newview varchar2(128); < quoted_owner varchar2(130); -- 2 more than size of owner < quoted_oldview varchar2(130); -- 2 more than size of oldview_name < quoted_newview varchar2(130); -- 2 more than size of newview_name < unsupp_col_condition varchar2(4000); < colcomments varchar2(4000); < unsupp_col_count number; < colcommentscur SYS_REFCURSOR; < table_not_found EXCEPTION; < PRAGMA exception_init(table_not_found, -942); < < < cursor tblcommentscur is select c.comment$ < from sys.obj$ o, sys.user$ u, sys.com$ c < where o.name = oldview_name and u.name = owner < and o.obj# = c.obj# and o.owner#=u.user# < and (o.type# = 4 or o.type# = 2) < and c.col# is null; < < begin < < newview := isLegalOwnerViewName(owner, oldview_name, newview_name); < if (newview is NULL) then < RAISE table_not_found; < end if; < < quoted_owner := '"' || owner || '"'; < quoted_oldview := '"' || oldview_name || '"'; < quoted_newview := '"' || newview || '"'; < < -- Create cdb view < sqlstmt := 'CREATE OR REPLACE VIEW ' || < quoted_owner || '.' || quoted_newview || < ' CONTAINER_DATA AS' || < ' SELECT k.*, k.CON$NAME, k.CDB$NAME, k.CON$ERRNUM, k.CON$ERRMSG' || < ' FROM CONTAINERS(' || quoted_owner || '.' || quoted_oldview || ') k'; < < execute immediate sqlstmt; < < -- table and column comments < open tblcommentscur; < fetch tblcommentscur into comments; < comments := replace(comments, '''',''''''); < sqlstmt := 'comment on table ' || quoted_owner || '.' || quoted_newview || < ' is ''' || comments || ' in all containers'''; < execute immediate sqlstmt; < close tblcommentscur; < < sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || < '.CON_ID is ''container id'''; < execute immediate sqlstmt; < < sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || < '.CON$NAME is ''Container Name'''; < execute immediate sqlstmt; < < sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || < '.CDB$NAME is ''Database Name'''; < execute immediate sqlstmt; < < sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || < '.CON$ERRNUM is ''Error Number'''; < execute immediate sqlstmt; < < sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || < '.CON$ERRMSG is ''Error Message'''; < execute immediate sqlstmt; < < colcomments := 'select c.name, co.comment$ ' || < 'from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co ' || < 'where o.name = :1 ' || < 'and u.name = :2 ' || < 'and o.owner# = u.user# and (o.type# = 4 or o.type# = 2) ' || < 'and o.obj# = c.obj# ' || < 'and c.obj# = co.obj# and c.intcol# = co.col# ' || < -- skip hidden column < 'and bitand(c.property, 32) = 0 '|| < -- skip null comment < 'and co.comment$ is not null'; < -- skip Long, Nested Table, Varray columns < unsupp_col_condition := 'c.type# = 8 or c.type# = 122 or c.type# = 123 ' || < -- skip ADT and REF columns < 'or c.type# = 121 or c.type# = 111 ' || < -- Bug 20683085: skip Opaque Type column except < -- xmltype stored as LOB. Check xmltype as lob using < -- property bit KQLDCOP2_XSLB. < -- Bug 23083309: if there are unsupported columns, < -- then XMLType column is skipped (hidden XMLType lob < -- column is already handled) < 'or (c.type# = 58 and ' || < '((bitand(c.property, ' || < 'power(2,32)*4194304)<>power(2,32)*4194304) '|| < 'or :3 > 0)) ' || < -- Bug 21785587: skip long raw < 'or c.type# = 24'; < < sqlstmt := colcomments || ' and (' || unsupp_col_condition || ')'; < < unsupp_col_count := 0; < EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || sqlstmt ||')' < INTO unsupp_col_count USING oldview_name, owner, unsupp_col_count; < < open colcommentscur for colcomments ||' and not ('|| < unsupp_col_condition ||')' < USING oldview_name, owner, unsupp_col_count; < loop < fetch colcommentscur into col_name, comments; < exit when colcommentscur%NOTFOUND; < < comments := replace(comments, '''',''''''); < sqlstmt := 'comment on column ' || < quoted_owner || '.' || quoted_newview || '.' || < col_name || ' is ''' || comments || ''''; < < execute immediate sqlstmt; < end loop; < close colcommentscur; < end; --- > begin > CDBView_internal.create_cdbview(chk_upgrd, owner, oldview_name, > newview_name); > end; 137,140c12,19 < tablename dbms_id; < colname dbms_id; < stmt varchar2(400); < retval varchar2(4000); --- > tablename dbms_id; > colname dbms_id; > stmt varchar2(400); > retval varchar2(4000); > isuser_sys boolean := FALSE; > scr_priv number := 0; > insuff_priv exception; > pragma exception_init(insuff_priv, -1031); 141a21,47 > -- > -- The Current_User should either be SYS or be in possession of > -- SELECT_CATALOG_ROLE to be able to query view definitions. > -- This is how Dbms_Metadata.Get_DDL operates, so we are trying > -- to have the same privilege model here too. If none of this is > -- true, we will raise ORA-1031 error. > -- > -- We are doing a direct query against SESSION_ROLES dictionary table > -- instead of using Dbms_Priv_Capture.Ses_Has_Role_Priv function, as > -- the package Dbms_Priv_Capture may not be available by the time this > -- package CDBView gets created. Also use dyn SQL for the same reason. > -- > if (sys_context('userenv','current_userid') = 0) then > isuser_sys := TRUE; > else > begin > execute immediate 'select count(*) from sys.session_roles where > role = ''SELECT_CATALOG_ROLE''' into scr_priv; > exception > when others then NULL; > end; > end if; > > if NOT (isuser_sys OR scr_priv > 0) then > raise insuff_priv; > end if; > 157,207d62 < < -- 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 varchar2 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 owner; < if (cCheck = 0) then < RETURN NULL; < 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 oldview; < if (cCheck = 0) then < RETURN NULL; < end if; < < if (not REGEXP_LIKE(newview, '^[A-Za-z_][A-Za-z0-9_$#]*$')) then < RETURN NULL; < 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(oldview, < 'DBA|DATABASE|_| |HIST|ATTRIB(UTE)?S?|CDB|AWR_PDB|REDUCED'); < cleanNewview := REGEXP_REPLACE(newview, < 'CDB|DATABASE|_| |HIST|ATTRIB(UTE)?S?|AWRI\$'); < < if (cleanOldview <> cleanNewview) then < RETURN NULL; < end if; < < RETURN newview; < < end isLegalOwnerViewName; <
1c1,229 < package body CDBView_internal as --- > package body CDBView_Internal is > > type sens_tabs is table of boolean index by dbms_id; > -- > -- Initialize the list of SYS owned sensitive tables which should > -- not be allowed to be shadow-copied even within Create_CDBView. > -- > -- Once ER 24598663 gets implemented, these entries will be part of a > -- SYS owned metadata table and instead of hard-coding the list, we will > -- be populating the list by fetching it from the table. > -- > sens_table_list CONSTANT sens_tabs := sens_tabs('ENC$'=>TRUE, 'LINK$'=>TRUE, > 'USER$'=>TRUE, 'DEFAULT_PWD$'=>TRUE, > 'XS$VERIFIERS'=>TRUE, 'USER_HISTORY$'=>TRUE, > 'HIST_HEAD$'=>TRUE, 'HISTGRM$'=>TRUE, > 'CDB_LOCAL_ADMINAUTH$'=>TRUE, > 'PDB_CREATE$'=>TRUE, 'PDB_SYNC$'=>TRUE); > > function isLegalOwnerViewName(owner IN varchar2, oldview IN varchar2, > newview IN varchar2) return varchar2; > -- Create the cdb view > -- private helper procedure to create the cdb view > -- Note that quotes should not be added around owner, oldview_name and > -- newview_name before create_cdbview is invoked since all three are used > -- as literals to query dictionary views. > procedure create_cdbview(chk_upgrd IN boolean, owner IN varchar2, > oldview_name IN varchar2, newview_name IN varchar2) as > sqlstmt varchar2(4000); > col_name dbms_id; > comments varchar2(4000); > col_type number; > newview dbms_id := newview_name; > quoted_owner dbms_quoted_id; -- 2 more than size of owner > quoted_oldview dbms_quoted_id; -- 2 more than size of oldview_name > quoted_newview dbms_quoted_id; -- 2 more than size of newview_name > unsupp_col_condition varchar2(4000); > colcomments varchar2(4000); > unsupp_col_count number; > colcommentscur SYS_REFCURSOR; > table_not_found EXCEPTION; > PRAGMA exception_init(table_not_found, -942); > insuff_privilege EXCEPTION; > PRAGMA exception_init(insuff_privilege, -1031); > > cursor tblcommentscur is select c.comment$ > from sys.obj$ o, sys.user$ u, sys.com$ c > where o.name = oldview_name and u.name = owner > and o.obj# = c.obj# and o.owner#=u.user# > and (o.type# = 4 or o.type# = 2) > and c.col# is null; > > begin > > -- > -- No need to check for ownername, viewname correctness, if the Current_User > -- is SYS, as would be the case with most of Create_CDBView invocations > -- across RDBMS code. > -- > -- As per recommendation from Mark, it is not safe to give a free pass to > -- SYS user, in case CDBView.Create_CDBView gets invoked from within a SYS > -- owned definer rights PL/SQL code and the inputs to those could be user > -- provided and potentially malicious. > -- > newview := isLegalOwnerViewName(owner, oldview_name, newview_name); > if (newview is NULL) then > RAISE table_not_found; > end if; > > -- > -- Bug 27445727 : Certain SYS owned tables like USER$, LINK$ etc. are out > -- of bounds even for DBA level users. So we should not allow cloning such > -- tables into a Container_Data view and be able to bypass this protection. > -- > -- Since this procedure does not support quoted identifiers, we need not > -- worry about canonicalizing the owner or oldview_name arguments before > -- comparing against SYS and the array of sensitive tables. > -- > if (owner = 'SYS' and sens_table_list.exists(oldview_name)) then > RAISE insuff_privilege; > end if; > > quoted_owner := '"' || owner || '"'; > quoted_oldview := '"' || oldview_name || '"'; > quoted_newview := '"' || newview || '"'; > > -- Create cdb view > sqlstmt := 'CREATE OR REPLACE VIEW ' || > quoted_owner || '.' || quoted_newview || > ' CONTAINER_DATA AS' || > ' SELECT k.*, k.CON$NAME, k.CDB$NAME, k.CON$ERRNUM, k.CON$ERRMSG' || > ' FROM CONTAINERS(' || quoted_owner || '.' || quoted_oldview || ') k'; > > execute immediate sqlstmt; > > -- table and column comments > open tblcommentscur; > fetch tblcommentscur into comments; > comments := replace(comments, '''',''''''); > sqlstmt := 'comment on table ' || quoted_owner || '.' || quoted_newview || > ' is ''' || comments || ' in all containers'''; > execute immediate sqlstmt; > close tblcommentscur; > > sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || > '.CON_ID is ''container id'''; > execute immediate sqlstmt; > > sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || > '.CON$NAME is ''Container Name'''; > execute immediate sqlstmt; > > sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || > '.CDB$NAME is ''Database Name'''; > execute immediate sqlstmt; > > sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || > '.CON$ERRNUM is ''Error Number'''; > execute immediate sqlstmt; > > sqlstmt := 'comment on column ' || quoted_owner || '.' || quoted_newview || > '.CON$ERRMSG is ''Error Message'''; > execute immediate sqlstmt; > > colcomments := 'select c.name, co.comment$ ' || > 'from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co ' || > 'where o.name = :1 ' || > 'and u.name = :2 ' || > 'and o.owner# = u.user# and (o.type# = 4 or o.type# = 2) ' || > 'and o.obj# = c.obj# ' || > 'and c.obj# = co.obj# and c.intcol# = co.col# ' || > -- skip hidden column > 'and bitand(c.property, 32) = 0 '|| > -- skip null comment > 'and co.comment$ is not null'; > -- skip Long, Nested Table, Varray columns > unsupp_col_condition := 'c.type# = 8 or c.type# = 122 or c.type# = 123 ' || > -- skip ADT and REF columns > 'or c.type# = 121 or c.type# = 111 ' || > -- Bug 20683085: skip Opaque Type column except > -- xmltype stored as LOB. Check xmltype as lob using > -- property bit KQLDCOP2_XSLB. > -- Bug 23083309: if there are unsupported columns, > -- then XMLType column is skipped (hidden XMLType lob > -- column is already handled) > 'or (c.type# = 58 and ' || > '((bitand(c.property, ' || > 'power(2,32)*4194304)<>power(2,32)*4194304) '|| > 'or :3 > 0)) ' || > -- Bug 21785587: skip long raw > 'or c.type# = 24'; > > sqlstmt := colcomments || ' and (' || unsupp_col_condition || ')'; > > unsupp_col_count := 0; > EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || sqlstmt ||')' > INTO unsupp_col_count USING oldview_name, owner, unsupp_col_count; > > open colcommentscur for colcomments ||' and not ('|| > unsupp_col_condition ||')' > USING oldview_name, owner, unsupp_col_count; > loop > fetch colcommentscur into col_name, comments; > exit when colcommentscur%NOTFOUND; > > comments := replace(comments, '''',''''''); > sqlstmt := 'comment on column ' || > quoted_owner || '.' || quoted_newview || '.' || > col_name || ' is ''' || comments || ''''; > > execute immediate sqlstmt; > end loop; > close colcommentscur; > end; > > -- 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 varchar2 as > cCheck number; > ownerId number; > cleanOldview dbms_id; > cleanNewview dbms_id; > begin > > -- Check if owner already exist > -- USER$ contains both users and roles, exclude roles from the check > begin > execute immediate 'SELECT USER# FROM USER$ WHERE TYPE# = 1 AND NAME = :1' > into ownerId using owner; > exception > when no_data_found then return null; > end; > > -- Check if oldview already exist, qualify with owner# to avoid ORA-1422 > -- error,if two dictionary view with the same name exists across different > -- schemas like DBA_DV_STATUS which exists in both SYS and DVSYS schemas. > begin > execute immediate 'SELECT 1 FROM OBJ$ WHERE NAME = :1' || > ' AND TYPE# in (2, 4) and owner# = :2' > into cCheck using oldview, ownerId; > exception > when no_data_found then return null; > end; > > if (not REGEXP_LIKE(newview, '^[A-Za-z_][A-Za-z0-9_$#]*$')) then > RETURN NULL; > 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(oldview, > 'DBA|DATABASE|_| |HIST|ATTRIB(UTE)?S?|CDB|AWR_PDB|REDUCED'); > cleanNewview := REGEXP_REPLACE(newview, > 'CDB|DATABASE|_| |HIST|ATTRIB(UTE)?S?|AWRI\$'); > > if (cleanOldview <> cleanNewview) then > RETURN NULL; > end if; > > RETURN newview; > > end isLegalOwnerViewName; 17c245 < end; --- > end CDBView_Internal;
171a172,186 > > > > > > > > > PROCEDURE REFRESH_CLIENT_CONFIG IS > EXTERNAL LANGUAGE C > NAME "ketrcc_refresh_client_config" > WITH CONTEXT > PARAMETERS(CONTEXT) > LIBRARY DBMS_AUTOTASK_PRVT_LIB; >
122c122 < CATALOGVERSION CONSTANT VARCHAR2(11) := '18.04.00.00'; --- > CATALOGVERSION CONSTANT VARCHAR2(11) := '18.03.02.00'; 24766d24765 < END IF; 24768,24788c24767,24790 < < < < < < < < IF (AVAILABLE_FROMSCN_ACT IS NULL OR < (USABLE_INCR.DFNUMBER_OBJ = LOCAL.DFNUMBER_OBJ AND < USABLE_INCR.DFCREATIONSCN_OBJ = LOCAL.DFCREATIONSCN_OBJ AND < LOCAL.TOSCN_ACT < AVAILABLE_FROMSCN_ACT)) THEN < IF (AVAILABLE_FROMSCN_ACT IS NULL) THEN < DEB(DEB_PRINT, 'available_fromSCN_act set to ' || < AVAILABLE_FROMSCN_ACT || ' for file# ' || < LOCAL.DFNUMBER_OBJ || ', creation_scn '|| < LOCAL.DFCREATIONSCN_OBJ); < ELSE < DEB(DEB_PRINT, 'broken chain, available_fromSCN_act set to ' || < AVAILABLE_FROMSCN_ACT || ' for file ' || < LOCAL.DFNUMBER_OBJ || ', creation_scn '|| < LOCAL.DFCREATIONSCN_OBJ); --- > > > > > > > > IF (AVAILABLE_FROMSCN_ACT IS NULL OR > (USABLE_INCR.DFNUMBER_OBJ = LOCAL.DFNUMBER_OBJ AND > USABLE_INCR.DFCREATIONSCN_OBJ = LOCAL.DFCREATIONSCN_OBJ AND > LOCAL.TOSCN_ACT < AVAILABLE_FROMSCN_ACT)) THEN > IF (AVAILABLE_FROMSCN_ACT IS NULL) THEN > DEB(DEB_PRINT, 'available_fromSCN_act set to ' || > AVAILABLE_FROMSCN_ACT || ' for file# ' || > LOCAL.DFNUMBER_OBJ || ', creation_scn '|| > LOCAL.DFCREATIONSCN_OBJ); > ELSE > DEB(DEB_PRINT, 'broken chain, available_fromSCN_act set to ' || > AVAILABLE_FROMSCN_ACT || ' for file ' || > LOCAL.DFNUMBER_OBJ || ', creation_scn '|| > LOCAL.DFCREATIONSCN_OBJ); > END IF; > USABLE_INCR := LOCAL; > AVAILABLE_FROMSCN_ACT := LOCAL.FROMSCN_ACT; 24790,24791d24791 < USABLE_INCR := LOCAL; < AVAILABLE_FROMSCN_ACT := LOCAL.FROMSCN_ACT; 27206,27208d27205 < < < IF (DFREC.PDBID <= 1 OR TRANSLATEPDB2NAME(DFREC.PDBID) IS NOT NULL) THEN 27210d27206 < END IF; 29226c29222 < ,NBLOCKS OUT NUMBER --- > ,NBLOCKS OUT BINARY_INTEGER
122c122 < CATALOGVERSION CONSTANT VARCHAR2(11) := '18.04.00.00'; --- > CATALOGVERSION CONSTANT VARCHAR2(11) := '18.05.00.00'; 25510,25512c25510,25512 < IF (LBREC.DF_CKP_MOD_TIME < UNTILTIME OR < (UNTILTIME IS NULL AND < LBREC.DF_CHECKPOINT_CHANGE# <= UNTILSCN)) THEN --- > IF ((UNTILTIME IS NULL AND UNTILSCN IS NULL) OR > LBREC.DF_CKP_MOD_TIME < UNTILTIME OR > LBREC.DF_CHECKPOINT_CHANGE# <= UNTILSCN) THEN
122c122 < CATALOGVERSION CONSTANT VARCHAR2(11) := '18.04.00.00'; --- > CATALOGVERSION CONSTANT VARCHAR2(11) := '18.04.01.00';
53868a53869,53926 > > > > > > > > > > > > > > PROCEDURE INIT_ADVISOR_TASK_PARAMETER( > TASK_NAME IN VARCHAR2, > PARAMETER IN VARCHAR2, > VALUE IN VARCHAR2) > IS > IN_EXEC BOOLEAN := FALSE; > BEGIN > > > > BEGIN > DBMS_STATS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, PARAMETER, VALUE); > EXCEPTION > WHEN OTHERS THEN > IF (SQLCODE = -13647) THEN > IN_EXEC := TRUE; > ELSE > RAISE; > END IF; > END; > > > > > IF (IN_EXEC = TRUE) THEN > > BEGIN > DBMS_STATS_ADVISOR.CANCEL_TASK(TASK_NAME); > EXCEPTION > WHEN OTHERS THEN > > > > IF (IS_URGENT_ERROR) THEN > RAISE; > END IF; > END; > > DBMS_STATS_ADVISOR.SET_TASK_PARAMETER(TASK_NAME, PARAMETER, VALUE); > > END IF; > > END INIT_ADVISOR_TASK_PARAMETER; > > 53873a53932,53943 > > > > > > > > > > > > 53882a53953,53957 > ELSE > INIT_ADVISOR_TASK_PARAMETER(PRVT_ADVISOR.TASK_RESERVED_NAME_ASTAT, > 'DAYS_TO_EXPIRE', 'UNLIMITED'); > INIT_ADVISOR_TASK_PARAMETER(PRVT_ADVISOR.TASK_RESERVED_NAME_ASTAT, > 'EXECUTION_DAYS_TO_EXPIRE', 30); 53890a53966,53970 > ELSE > INIT_ADVISOR_TASK_PARAMETER(PRVT_ADVISOR.TASK_RESERVED_NAME_ISTAT, > 'DAYS_TO_EXPIRE', 'UNLIMITED'); > INIT_ADVISOR_TASK_PARAMETER(PRVT_ADVISOR.TASK_RESERVED_NAME_ISTAT, > 'EXECUTION_DAYS_TO_EXPIRE', 30);
3308a3309,3318 > > > > > DBMS_STATS.SET_ADVISOR_TASK_PARAMETER(TNAME, > 'DAYS_TO_EXPIRE', 'UNLIMITED'); > DBMS_STATS.SET_ADVISOR_TASK_PARAMETER(TNAME, > 'EXECUTION_DAYS_TO_EXPIRE', 30); > > 4583a4594,4600 > WHEN UPPER(PARAMETER) = 'DAYS_TO_EXPIRE' THEN > DBMS_ADVISOR.SET_TASK_PARAMETER(TNAMEU, PARAMETER, VALUE); > > WHEN UPPER(PARAMETER) = 'EXECUTION_DAYS_TO_EXPIRE' THEN > DBMS_ADVISOR.SET_TASK_PARAMETER(TNAMEU, PARAMETER, VALUE); > >
57,58c57,58 < --MEMBER FUNCTION put(key VARCHAR2, val CLOB) RETURN BOOLEAN, < --MEMBER FUNCTION put(key VARCHAR2, val BLOB) RETURN BOOLEAN, --- > MEMBER FUNCTION put(key VARCHAR2, val CLOB) RETURN BOOLEAN, > MEMBER FUNCTION put(key VARCHAR2, val BLOB) RETURN BOOLEAN, 83,84c83,84 < --MEMBER FUNCTION append(val CLOB) RETURN BOOLEAN, < --MEMBER FUNCTION append(val BLOB) RETURN BOOLEAN, --- > MEMBER FUNCTION append(val CLOB) RETURN BOOLEAN, > MEMBER FUNCTION append(val BLOB) RETURN BOOLEAN, 98,101c98,101 < --MEMBER FUNCTION put(pos NUMBER, val CLOB, overwrite BOOLEAN) < -- RETURN BOOLEAN, < --MEMBER FUNCTION put(pos NUMBER, val BLOB, overwrite BOOLEAN) < -- RETURN BOOLEAN, --- > MEMBER FUNCTION put(pos NUMBER, val CLOB, overwrite BOOLEAN) > RETURN BOOLEAN, > MEMBER FUNCTION put(pos NUMBER, val BLOB, overwrite BOOLEAN) > RETURN BOOLEAN,
49,50c49,50 < --MEMBER PROCEDURE append(val CLOB), < --MEMBER PROCEDURE append(val BLOB), --- > MEMBER PROCEDURE append(val CLOB), > MEMBER PROCEDURE append(val BLOB), 65,68c65,68 < --MEMBER PROCEDURE put(pos NUMBER, val CLOB, < -- overwrite BOOLEAN DEFAULT FALSE), < --MEMBER PROCEDURE put(pos NUMBER, val BLOB, < -- overwrite BOOLEAN DEFAULT FALSE), --- > MEMBER PROCEDURE put(pos NUMBER, val CLOB, > overwrite BOOLEAN DEFAULT FALSE), > MEMBER PROCEDURE put(pos NUMBER, val BLOB, > overwrite BOOLEAN DEFAULT FALSE),
35c35,36 < MEMBER PROCEDURE mergepatch(self IN OUT NOCOPY JSON_ELEMENT_T, patch VARCHAR2), --- > MEMBER PROCEDURE mergepatch(self IN OUT NOCOPY JSON_ELEMENT_T, > patch VARCHAR2),
57,58c57,58 < --MEMBER PROCEDURE put(key VARCHAR2, val CLOB), < --MEMBER PROCEDURE put(key VARCHAR2, val BLOB), --- > MEMBER PROCEDURE put(key VARCHAR2, val CLOB), > MEMBER PROCEDURE put(key VARCHAR2, val BLOB),
227,239c227,239 < --MEMBER FUNCTION put(key VARCHAR2, val CLOB) RETURN BOOLEAN IS EXTERNAL < --name "setClob_Obj" library DBMS_JDOM_LIB WITH CONTEXT < --parameters(context, self, self INDICATOR sb2, < -- key OCIString, key INDICATOR sb2, < -- val OCILobLocator, val INDICATOR sb2, < -- RETURN INDICATOR sb4, return); < < --MEMBER FUNCTION put(key VARCHAR2, val BLOB) RETURN BOOLEAN IS EXTERNAL < -- name "setBlob_Obj" library DBMS_JDOM_LIB WITH CONTEXT < -- parameters(context, self, self INDICATOR sb2, < -- key OCIString, key INDICATOR sb2, < -- val OCILobLocator, val INDICATOR sb2, < -- RETURN INDICATOR sb4, return); --- > MEMBER FUNCTION put(key VARCHAR2, val CLOB) RETURN BOOLEAN IS EXTERNAL > name "setClob_Obj" library DBMS_JDOM_LIB WITH CONTEXT > parameters(context, self, self INDICATOR sb2, > key OCIString, key INDICATOR sb2, > val OCILobLocator, val INDICATOR sb2, > RETURN INDICATOR sb4, return); > > MEMBER FUNCTION put(key VARCHAR2, val BLOB) RETURN BOOLEAN IS EXTERNAL > name "setBlob_Obj" library DBMS_JDOM_LIB WITH CONTEXT > parameters(context, self, self INDICATOR sb2, > key OCIString, key INDICATOR sb2, > val OCILobLocator, val INDICATOR sb2, > RETURN INDICATOR sb4, return); 373,383c373,383 < --MEMBER FUNCTION append(val CLOB) RETURN BOOLEAN is EXTERNAL < -- name "addClob_Arr" library DBMS_JDOM_LIB WITH CONTEXT < -- parameters(context, self, self INDICATOR sb2, < -- val OCILobLocator, val INDICATOR sb2, < -- RETURN INDICATOR sb4, return); < < --MEMBER FUNCTION append(val BLOB) RETURN BOOLEAN is EXTERNAL < -- name "addBlob_Arr" library DBMS_JDOM_LIB WITH CONTEXT < -- parameters(context, self, self INDICATOR sb2, < -- val OCILobLocator, val INDICATOR sb2, < -- RETURN INDICATOR sb4, return); --- > MEMBER FUNCTION append(val CLOB) RETURN BOOLEAN is EXTERNAL > name "addClob_Arr" library DBMS_JDOM_LIB WITH CONTEXT > parameters(context, self, self INDICATOR sb2, > val OCILobLocator, val INDICATOR sb2, > RETURN INDICATOR sb4, return); > > MEMBER FUNCTION append(val BLOB) RETURN BOOLEAN is EXTERNAL > name "addBlob_Arr" library DBMS_JDOM_LIB WITH CONTEXT > parameters(context, self, self INDICATOR sb2, > val OCILobLocator, val INDICATOR sb2, > RETURN INDICATOR sb4, return);
163,173c163,173 < --MEMBER PROCEDURE append(val CLOB) AS < -- hack boolean; < --BEGIN < -- hack := dom.append(val); < --END; < < --MEMBER PROCEDURE append(val BLOB) AS < -- hack boolean; < --BEGIN < -- hack := dom.append(val); < --END; --- > MEMBER PROCEDURE append(val CLOB) AS > hack boolean; > BEGIN > hack := dom.append(val); > END; > > MEMBER PROCEDURE append(val BLOB) AS > hack boolean; > BEGIN > hack := dom.append(val); > END; 223,235c223,235 < --MEMBER PROCEDURE put(pos NUMBER, val CLOB, < -- overwrite BOOLEAN DEFAULT FALSE) AS < -- hack boolean; < --BEGIN < -- hack := dom.put(pos, val, overwrite); < --END; < < --MEMBER PROCEDURE put(pos NUMBER, val BLOB, < -- overwrite BOOLEAN DEFAULT FALSE) AS < -- hack boolean; < --BEGIN < -- hack := dom.put(pos, val, overwrite); < --END; --- > MEMBER PROCEDURE put(pos NUMBER, val CLOB, > overwrite BOOLEAN DEFAULT FALSE) AS > hack boolean; > BEGIN > hack := dom.put(pos, val, overwrite); > END; > > MEMBER PROCEDURE put(pos NUMBER, val BLOB, > overwrite BOOLEAN DEFAULT FALSE) AS > hack boolean; > BEGIN > hack := dom.put(pos, val, overwrite); > END;
177c177,178 < MEMBER PROCEDURE mergepatch(self IN OUT NOCOPY JSON_ELEMENT_T, patch VARCHAR2) AS --- > MEMBER PROCEDURE mergepatch(self IN OUT NOCOPY JSON_ELEMENT_T, > patch VARCHAR2) AS
163,173c163,173 < --MEMBER PROCEDURE put(key VARCHAR2, val CLOB) AS < -- hack boolean; < --BEGIN < -- hack := dom.put(key, val); < --END; < < --MEMBER PROCEDURE put(key VARCHAR2, val BLOB) AS < -- hack boolean; < --BEGIN < -- hack := dom.put(key, val); < --END; --- > MEMBER PROCEDURE put(key VARCHAR2, val CLOB) AS > hack boolean; > BEGIN > hack := dom.put(key, val); > END; > > MEMBER PROCEDURE put(key VARCHAR2, val BLOB) AS > hack boolean; > BEGIN > hack := dom.put(key, val); > END;
86a87,90 > ERR_IDX_DESCENDANT_STEP CONSTANT NUMBER := -40756; > ERR_IDX_TRAIL_LEAD_STEP CONSTANT NUMBER := -40757; > ERR_IDX_SPL_CHAR_STEP CONSTANT NUMBER := -40758; > ERR_IDX_WILDCARD_STEP CONSTANT NUMBER := -40759; 436c440,444 < FUNCTION TRANSLATE_JSON_PATH(P_PATH IN VARCHAR2) RETURN VARCHAR2 --- > FUNCTION TRANSLATE_JSON_PATH(P_PATH IN VARCHAR2, > P_ALLOW_ARRAY_STEP BOOLEAN DEFAULT TRUE, > P_ALLOW_DESCENDANT_STEP BOOLEAN DEFAULT TRUE, > P_ALLOW_WILDCARD_STEP BOOLEAN DEFAULT TRUE) > RETURN VARCHAR2 452a461,463 > V_HAS_ARRAY_STEP BOOLEAN := FALSE; > V_HAS_DESCENDANT_STEP BOOLEAN := FALSE; > V_HAS_WILDCARD_STEP BOOLEAN := FALSE; 469c480,485 < IF (V_CHR = V_STEP_SEP) AND (NOT(V_IN_QUOTES)) THEN --- > IF (V_CHR = V_STEP_SEP AND (I = 1 OR I = V_PATH_LEN)) THEN > SYS.DBMS_SODA_UTIL.RAISE_SYSTEM_ERROR(ERR_IDX_TRAIL_LEAD_STEP); > ELSIF (V_CHR = V_STEP_SEP) AND (NOT(V_IN_QUOTES)) THEN > IF (V_NXT = V_STEP_SEP) THEN > V_HAS_DESCENDANT_STEP := TRUE; > END IF; 472c488 < V_NEED_QUOTES := TRUE; --- > V_NEED_QUOTES := FALSE; 506a523,536 > > IF NOT (V_IN_QUOTES) AND (V_CHR IN ('$','@')) THEN > IF (V_NXT = '.' OR I = V_PATH_LEN) THEN > SYS.DBMS_SODA_UTIL.RAISE_SYSTEM_ERROR(ERR_IDX_SPL_CHAR_STEP, > V_CHR, P_PATH, I); > END IF; > END IF; > > IF NOT (V_IN_QUOTES) AND (V_CHR IN ('*')) THEN > IF (V_NXT = '.' OR I = V_PATH_LEN) THEN > V_HAS_WILDCARD_STEP := TRUE; > END IF; > END IF; > 508c538 < SYS.DBMS_SODA_UTIL.RAISE_SYSTEM_ERROR(ERR_IDX_NO_ARRAYS); --- > V_HAS_ARRAY_STEP := TRUE; 511c541 < IF (INSTR('0123456789', V_CHR) > 0) THEN --- > IF (INSTR(DIGITS_CHARS, V_CHR) > 0) THEN 565a596,604 > IF NOT (P_ALLOW_ARRAY_STEP) AND V_HAS_ARRAY_STEP THEN > SYS.DBMS_SODA_UTIL.RAISE_SYSTEM_ERROR(ERR_IDX_NO_ARRAYS); > END IF; > IF NOT (P_ALLOW_DESCENDANT_STEP) AND V_HAS_DESCENDANT_STEP THEN > SYS.DBMS_SODA_UTIL.RAISE_SYSTEM_ERROR(ERR_IDX_DESCENDANT_STEP); > END IF; > IF NOT (P_ALLOW_WILDCARD_STEP) AND V_HAS_WILDCARD_STEP THEN > SYS.DBMS_SODA_UTIL.RAISE_SYSTEM_ERROR(ERR_IDX_WILDCARD_STEP); > END IF; 576c615 < V_NEED_QUOTES := TRUE; --- > V_NEED_QUOTES := FALSE; 1289c1328 < V_KEY_PATH := TRANSLATE_JSON_PATH(V_KEY_PATH); --- > V_KEY_PATH := TRANSLATE_JSON_PATH(V_KEY_PATH, FALSE, FALSE, FALSE); 1292c1331,1332 < V_PARTITION_PATH := TRANSLATE_JSON_PATH(V_PARTITION_PATH); --- > V_PARTITION_PATH := TRANSLATE_JSON_PATH(V_PARTITION_PATH, FALSE, FALSE, > FALSE); 2267c2307 < P_SPATIAL := TRANSLATE_JSON_PATH(P_SPATIAL); --- > P_SPATIAL := TRANSLATE_JSON_PATH(P_SPATIAL, FALSE, FALSE, TRUE); 2321c2361,2362 < P_PATHS(J) := TRANSLATE_JSON_PATH(P_PATHS(J)); --- > P_PATHS(J) := TRANSLATE_JSON_PATH(P_PATHS(J), FALSE, FALSE, > TRUE);
Useful Links:
- Download Assistant: MOS Note: 2118136.2
- 2019 January CPU: https://www.oracle.com/technetwork/security-advisory/cpujan2019-5072801.html