DBAHUB: Plugging 12c NON-CDB to 12c CDB

A container is either a PDB or the root container (also called the root). The root is a collection of schemas, schema objects, and non-schema objects to which all PDBs belong.

Every CDB has the following containers:

One root Container and it stores Oracle-supplied metadata i.e. Oracle supplied PL/SQL Packages and users that is known to every database called as common users. The root container is named CDB$ROOT

One seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. You cannot add or modify objects in PDB$SEED.

Zero or more user-created PDBs: A PDB is a user-created entity that contains the data and code required for a specific set of features.PDB can support a specific application. No PDBs exist at creation of the CDB. You can add PDBs based on your business requirements.

1. Steps for plugging non-CDB to CDB

Collecting non-CDB information before Plugging

2. Tablespace info for PDB2

3. PDB2 Schema Status

4. Tempfile PDB2

5. Checking CDB_PDB View

6. Shutting Down PDB and open in READ ONLY MODE

To convert it to be a PDB we first need to get the database in a consistent state and then run DBMS_PDB.DESCRIBE to create an XML file to describe the database.

Shutdown the database cleanly and open it in read only mode

7 Running DBMS_PDB in PDB Database

The DBMS_PDB package provides an interface to examine and manipulate data about pluggable databases. This procedure generates an XML file describing the specified pluggable database (PDB). This file can then be passed to the CHECK_PLUG_COMPATIBILITY Function to determine if the PDB described by the XML file may be plugged into a given multitenant container database (CDB).

8 Shutdown the PDB [PDB2]

9. Connect to CDB and Check whether non-cdb (PDB2) can be plugged into CDB (CDB12c)

This function uses an XML file describing a pluggable database (PDB) to determine whether it may be plugged into a given multitenant container database (CDB).

SET SERVEROUTPUT ON

DECLARE

compatible CONSTANT VARCHAR2(3) :=

CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

pdb_descr_file => 'D:\app\hcl\product\12.1.0\oradata\PDB2\PDB2.xml',

pdb_name => 'PDB2')

WHEN TRUE THEN 'YES'

ELSE 'NO'

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(compatible);

END;

10. Check PDB_PLUG_IN_VIOLATIONS view

set line 300

col cause for a20

col name for a10

col message for a80

select name, cause, type, message, status from PDB_PLUG_IN_VIOLATIONS where name='PDB2';

These warnings can be ignored. Please do not ignore Errors if any got in Type Column. Please fix before we proceed

11. Plug-in Non-CDB (PDB2) as PDB (PDB2):

Plugging the database in to a CDB on the same server with NOCOPY clause

CREATE PLUGGABLE DATABASE PDB2 USING 'D:\app\hcl\product\12.1.0\oradata\PDB2\PDB2.xml'

NOCOPY

TEMPFILE REUSE;

12. Checking status for new PDB [PDB2]

check that status of newly created PDB PDB2 is NEW

col pdb_name for a15

select pdb_name, status from dba_pdbs where pdb_name = 'PDB2';

13. Run noncdb_to_pdb.sql script, complete the following steps:

Access the newly creatd PDB.

SQL> alter session set container=PDB2;

14. Run the noncdb_to_pdb.sql script:

@D:\app\hcl\product\12.1.0\dbhome_2\RDBMS\ADMIN\noncdb_to_pdb.sql

You must open the new PDB in read/write mode for Oracle Database to complete the integration of the new PDB into the CDB. An error is returned if you attempt to open the PDB in read-only mode. After the PDB is opened in read/write mode, its status is NORMAL.

15. Open the new PDB in read/write mode.

15. Verify that warning for requires noncdb_to_pdb.sql be run has also been resolved

## B

efore PDB [PDB2] was started

## After PDB [PDB2] was started/opened

If an error is returned during PDB creation, then the PDB being created might be in an UNUSABLE state. You can check a PDB’s state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped, and it must be dropped before a PDB with the same name as the unusable PDB can be created.

16. Verify your data

Check the schema ‘Vijay’

LOG noncdb_to_pdb.sql

CDB12C >
CDB12C >@D:\app\hcl\product\12.1.0\dbhome_2\RDBMS\ADMIN\noncdb_to_pdb.sql
CDB12C >SET SERVEROUTPUT ON
CDB12C >SET FEEDBACK 1
CDB12C >SET NUMWIDTH 10
CDB12C >SET LINESIZE 80
CDB12C >SET TRIMSPOOL ON
CDB12C >SET TAB OFF
CDB12C >SET PAGESIZE 100
CDB12C >
CDB12C >WHENEVER SQLERROR EXIT;
CDB12C >
CDB12C >DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01403: no data found"
DOC> error if we're not in a PDB.
DOC> This script is intended to be run right after plugin of a PDB,
DOC> while inside the PDB.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
CDB12C >
CDB12C >VARIABLE cdbname VARCHAR2(128)
CDB12C >VARIABLE pdbname VARCHAR2(128)
CDB12C >BEGIN
2 SELECT sys_context('USERENV', 'CDB_NAME')
3 INTO :cdbname
4 FROM dual
5 WHERE sys_context('USERENV', 'CDB_NAME') is not null;
6 SELECT sys_context('USERENV', 'CON_NAME')
7 INTO :pdbname
8 FROM dual
9 WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';
10 END;
11 /

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >COLUMN pdbname NEW_VALUE pdbname
CDB12C >COLUMN pdbid NEW_VALUE pdbid
CDB12C >
CDB12C >select :pdbname pdbname from dual;

PDBNAME
--------------------------------------------------------------------------------
PDB2

1 row selected.

CDB12C >
CDB12C >select TO_CHAR(con_id) pdbid from v$pdbs where name='&pdbname';
old 1: select TO_CHAR(con_id) pdbid from v$pdbs where name='&pdbname'
new 1: select TO_CHAR(con_id) pdbid from v$pdbs where name='PDB2'

PDBID
----------------------------------------
4

1 row selected.

CDB12C >
CDB12C >-- save pluggable database open mode
CDB12C >COLUMN open_state_col NEW_VALUE open_sql;
CDB12C >COLUMN restricted_col NEW_VALUE restricted_state;
CDB12C >SELECT decode(open_mode,
2 'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',
3 'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN', '')
4 open_state_col,
5 decode(restricted, 'YES', 'RESTRICTED', '')
6 restricted_col
7 from v$pdbs where name='&pdbname';
old 2: 'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',
new 2: 'READ ONLY', 'ALTER PLUGGABLE DATABASE PDB2 OPEN READ ONLY',
old 3: 'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN', '')
new 3: 'READ WRITE', 'ALTER PLUGGABLE DATABASE PDB2 OPEN', '')
old 7: from v$pdbs where name='&pdbname'
new 7: from v$pdbs where name='PDB2'

OPEN_STATE_COL RESTRICTED
-------------------------------------------- ----------

1 row selected.

CDB12C >
CDB12C >-- save value for _system_trig_enabled parameter
CDB12C >COLUMN sys_trig NEW_VALUE sys_trig_enabled NOPRINT;
CDB12C >SELECT parm_values.ksppstvl as sys_trig
2 FROM sys.x$ksppi parms, sys.x$ksppsv parm_values
3 WHERE parms.ksppinm = '_system_trig_enabled' AND
4 parms.inst_id = USERENV('Instance') AND
5 parms.indx = parm_values.indx;

1 row selected.

CDB12C >
CDB12C >-- if pdb was already closed, don't exit on error
CDB12C >WHENEVER SQLERROR CONTINUE;
CDB12C >
CDB12C >alter pluggable database "&pdbname" close;
old 1: alter pluggable database "&pdbname" close
new 1: alter pluggable database "PDB2" close
alter pluggable database "PDB2" close
*
ERROR at line 1:
ORA-65020: pluggable database PDB2 already closed

CDB12C >
CDB12C >WHENEVER SQLERROR EXIT;
CDB12C >
CDB12C >alter session set container = CDB$ROOT;

Session altered.

CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >alter session set container = "&pdbname";
old 1: alter session set container = "&pdbname"
new 1: alter session set container = "PDB2"

Session altered.

CDB12C >
CDB12C >alter pluggable database "&pdbname" open restricted;
old 1: alter pluggable database "&pdbname" open restricted
new 1: alter pluggable database "PDB2" open restricted

.....
old 1: select TO_CHAR(con_id) pdbid from v$pdbs where name='&pdbname'
new 1: select TO_CHAR(con_id) pdbid from v$pdbs where name='PDB2'

PDBID
----------------------------------------
4

1 row selected.

CDB12C >
CDB12C >-- save pluggable database open mode
CDB12C >COLUMN open_state_col NEW_VALUE open_sql;
CDB12C >COLUMN restricted_col NEW_VALUE restricted_state;
CDB12C >SELECT decode(open_mode,
2 'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',
3 'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN', '')
4 open_state_col,
5 decode(restricted, 'YES', 'RESTRICTED', '')
6 restricted_col
7 from v$pdbs where name='&pdbname';
old 2: 'READ ONLY', 'ALTER PLUGGABLE DATABASE &pdbname OPEN READ ONLY',
new 2: 'READ ONLY', 'ALTER PLUGGABLE DATABASE PDB2 OPEN READ ONLY',
old 3: 'READ WRITE', 'ALTER PLUGGABLE DATABASE &pdbname OPEN', '')
new 3: 'READ WRITE', 'ALTER PLUGGABLE DATABASE PDB2 OPEN', '')
old 7: from v$pdbs where name='&pdbname'
new 7: from v$pdbs where name='PDB2'

OPEN_STATE_COL RESTRICTED
-------------------------------------------- ----------

1 row selected.

CDB12C >
CDB12C >-- save value for _system_trig_enabled parameter
CDB12C >COLUMN sys_trig NEW_VALUE sys_trig_enabled NOPRINT;
CDB12C >SELECT parm_values.ksppstvl as sys_trig
2 FROM sys.x$ksppi parms, sys.x$ksppsv parm_values
3 WHERE parms.ksppinm = '_system_trig_enabled' AND
4 parms.inst_id = USERENV('Instance') AND
5 parms.indx = parm_values.indx;

1 row selected.
CDB12C >
CDB12C >-- if pdb was already closed, don't exit on error
CDB12C >WHENEVER SQLERROR CONTINUE;
CDB12C >
CDB12C >alter pluggable database "&pdbname" close;
old 1: alter pluggable database "&pdbname" close
new 1: alter pluggable database "PDB2" close
alter pluggable database "PDB2" close
*
ERROR at line 1:
ORA-65020: pluggable database PDB2 already closed

CDB12C >
CDB12C >WHENEVER SQLERROR EXIT;
CDB12C >
CDB12C >alter session set container = CDB$ROOT;

Session altered.

CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >alter session set container = "&pdbname";
old 1: alter session set container = "&pdbname"
new 1: alter session set container = "PDB2"

Session altered.

CDB12C >
CDB12C >alter pluggable database "&pdbname" open restricted;
old 1: alter pluggable database "&pdbname" open restricted
new 1: alter pluggable database "PDB2" open restricted

Warning: PDB altered with errors.

CDB12C >
CDB12C >-- initial setup before beginning the script
CDB12C >alter session set "_ORACLE_SCRIPT"=true;

Session altered.

CDB12C >alter session set "_NONCDB_TO_PDB"=true;

Session altered.

CDB12C >ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

Session altered.

CDB12C >ALTER SYSTEM SET "_system_trig_enabled"=FALSE SCOPE=MEMORY;

System altered.

CDB12C >exec dbms_pdb.noncdb_to_pdb(1);

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >-- if we're plugging in a database that had been upgraded, we need to:
CDB12C >-- 1) generate signatures for common tables
CDB12C >-- 2) patch up tables that with column order differences. This can happen due
CDB12C >-- to db creation scripts adding columns to the middle of a table vs
CDB12C >-- upgrade scripts adding to the end via ALTER TABLE ADD
CDB12C >
CDB12C >alter session set container=CDB$ROOT;

Session altered.

CDB12C >
CDB12C >-- create temporary object-linked view to get list of objects marked as common
CDB12C >-- in CDB$ROOT
CDB12C >create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
2 select u.name owner, o.name object_name, o.type# object_type, o.namespace nsp,
3 o.subname object_subname, o.signature object_sig,
4 decode(bitand(o.flags, 65536), 65536, 'MDL', 'OBL') sharing
5 from sys.obj$ o, sys.user$ u where
6 o.owner#=u.user# and bitand(o.flags, 196608) <> 0;
old 1: create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
new 1: create or replace view sys.cdb$common_root_objects4 sharing=object as

View created.
CDB12C >
CDB12C >-- object-linked view for list of common users
CDB12C >create or replace view sys.cdb$common_users&pdbid sharing=object as
2 select name from sys.user$ where bitand(spare1, 128) <> 0;
old 1: create or replace view sys.cdb$common_users&pdbid sharing=object as
new 1: create or replace view sys.cdb$common_users4 sharing=object as

View created.

CDB12C >
CDB12C >-- object-linked view for accessing dependency$
CDB12C >create or replace view sys.cdb$rootdeps&pdbid sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace
,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$
do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#;
old 1: create or replace view sys.cdb$rootdeps&pdbid sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespac
e,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$
do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#
new 1: create or replace view sys.cdb$rootdeps4 sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace,pu.
name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$ do,
sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#

View created.

CDB12C >
CDB12C >-- switch into PDB
CDB12C >alter session set container="&pdbname";
old 1: alter session set container="&pdbname"
new 1: alter session set container="PDB2"

Session altered.

CDB12C >
CDB12C >create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
2 select u.name owner, o.name object_name, o.type# object_type, o.namespace nsp,
3 o.subname object_subname, o.signature object_sig,
4 decode(bitand(o.flags, 65536), 65536, 'MDL', 'OBL') sharing
5 from sys.obj$ o, sys.user$ u where
6 o.owner#=u.user# and bitand(o.flags, 196608) <> 0;
old 1: create or replace view sys.cdb$common_root_objects&pdbid sharing=object as
new 1: create or replace view sys.cdb$common_root_objects4 sharing=object as

View created.

CDB12C >
CDB12C >create or replace view sys.cdb$common_users&pdbid sharing=object as
2 select name from sys.user$ where bitand(spare1, 128) <> 0;
old 1: create or replace view sys.cdb$common_users&pdbid sharing=object as
new 1: create or replace view sys.cdb$common_users4 sharing=object as

View created.

CDB12C >
CDB12C >create or replace view sys.cdb$rootdeps&pdbid sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace
,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$
do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#;
old 1: create or replace view sys.cdb$rootdeps&pdbid sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespac
e,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$
do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#
new 1: create or replace view sys.cdb$rootdeps4 sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace,pu.
name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$ do,
sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#

View created.

CDB12C >
CDB12C >create or replace view sys.cdb$objects&pdbid sharing=none as
2 select u.name owner, o.name object_name, o.signature object_sig, o.namespace nsp,
3 o.subname object_subname, o.obj# object_id, o.type# object_type, o.flags flags
4 from sys.obj$ o, sys.user$ u
5 where o.owner#=u.user#;
old 1: create or replace view sys.cdb$objects&pdbid sharing=none as
new 1: create or replace view sys.cdb$objects4 sharing=none as

View created.

CDB12C >
CDB12C >create or replace view sys.cdb$tables&pdbid sharing=none as
2 select * from sys.cdb$objects&pdbid where object_type=2;
old 1: create or replace view sys.cdb$tables&pdbid sharing=none as
new 1: create or replace view sys.cdb$tables4 sharing=none as
old 2: select * from sys.cdb$objects&pdbid where object_type=2
new 2: select * from sys.cdb$objects4 where object_type=2

View created.

CDB12C >
CDB12C >
CDB12C >---------------------------------------------------------------------------
CDB12C >-- PRE-SCRIPT CHECKS GO HERE:
CDB12C >
CDB12C >set serveroutput on
CDB12C >
CDB12C >-- Check that we have no invalid table data
CDB12C >DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error, if the database contains invalid data as a result of type
DOC> evolution which was performed without the data being converted.
DOC>
DOC> To resolve this specific "ORA-01722: invalid number" error:
DOC> Perform the data conversion (details below) in the pluggable database.
DOC>
DOC> Please refer to Oracle Database Object-Relational Developer's Guide
DOC> for more information about type evolution.
DOC>
DOC> Data in columns of evolved types must be converted before the
DOC> database can be converted.
DOC>
DOC> The following commands, run inside the PDB, will perform the data
DOC> conversion for Oracle supplied data:
DOC>
DOC> @?/rdbms/admin/utluppkg.sql
DOC> SET SERVEROUTPUT ON;
DOC> exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA');
DOC> SET SERVEROUTPUT OFF;
DOC>
DOC> You should then confirm that any non-Oracle supplied data is also
DOC> converted. You should review the data and determine if it needs
DOC> to be converted or removed.
DOC>
DOC> To view the data that is affected by type evolution, execute the
DOC> following inside the PDB:
DOC>
DOC> SELECT rpad(u.name,128) TABLENAME, rpad(o.name,128) OWNER,
DOC> rpad(c.name,128) COLNAME FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t,
DOC> SYS.USER$ u
DOC> WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# AND c.COL# = t.COL#
DOC> AND t.INTCOL# = c.INTCOL# AND BITAND(t.FLAGS, 256) = 256
DOC> AND o.OWNER# = u.USER# AND o.OWNER# NOT IN
DOC> (SELECT UNIQUE (d.USER_ID) FROM SYS.DBA_USERS d, SYS.REGISTRY$ r
DOC> WHERE d.USER_ID = r.SCHEMA# and r.NAMESPACE='SERVER');
DOC>
DOC> Once the data is confirmed, the following commands, run inside the PDB,
DOC> will convert the data returned by the above query.
DOC>
DOC> @?/rdbms/admin/utluppkg.sql
DOC> SET SERVEROUTPUT ON;
DOC> exec dbms_preup.run_fixup_and_report('INVALID_USR_TABLEDATA');
DOC> SET SERVEROUTPUT OFF;
DOC>
DOC> Depending on the amount of data involved, converting the evolved type
DOC> data can take a significant amount of time.
DOC>
DOC> After this is complete, please rerun noncdb_to_pdb.sql.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
CDB12C >
CDB12C >declare
2 do_abort boolean := false;
3 begin
4 if dbms_preup.condition_exists ('INVALID_SYS_TABLEDATA') then
5 -- dump out the info
6 dbms_preup.run_check('INVALID_SYS_TABLEDATA');
7 do_abort := TRUE;
8 end if;
9 if dbms_preup.condition_exists ('INVALID_USR_TABLEDATA') THEN
10 -- dump out the info
11 dbms_preup.run_check('INVALID_USR_TABLEDATA');
12 do_abort := TRUE;
13 END IF;
14 If do_abort THEN
15 dbms_output.put_line ('Invalid table data.');
16 dbms_output.put_line ('Non-CDB conversion aborting.');
17 dbms_output.put_line ('For instructions, look for ORA-01722 in this script.');
18 dbms_output.put_line ('Please resolve these and rerun noncdb_to_pdb.sql.');
19 RAISE INVALID_NUMBER;
20 end if;
21 end;
22 /

...

2 select name from sys.user$ where bitand(spare1, 128) <> 0;
old 1: create or replace view sys.cdb$common_users&pdbid sharing=object as
new 1: create or replace view sys.cdb$common_users4 sharing=object as

View created.

CDB12C >
CDB12C >create or replace view sys.cdb$rootdeps&pdbid sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace
,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$
do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#;
old 1: create or replace view sys.cdb$rootdeps&pdbid sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespac
e,pu.name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$
do, sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#
new 1: create or replace view sys.cdb$rootdeps4 sharing=object as select du.name as owner, do.name as name, do.type# as d_type#, do.namespace as d_namespace,pu.
name as referenced_owner, po.name as referenced_name, po.type# as p_type#, po.namespace as p_namespace,d.order#,d.property,d.d_attrs,d.d_reason from sys.obj$ do,
sys.obj$ po, sys.user$ du, sys.user$ pu, sys.dependency$ d where du.user#=do.owner# and pu.user#=po.owner# and do.obj#=d_obj# and po.obj#=p_obj#

View created.

CDB12C >
CDB12C >create or replace view sys.cdb$objects&pdbid sharing=none as
2 select u.name owner, o.name object_name, o.signature object_sig, o.namespace nsp,
3 o.subname object_subname, o.obj# object_id, o.type# object_type, o.flags flags
4 from sys.obj$ o, sys.user$ u
5 where o.owner#=u.user#;
old 1: create or replace view sys.cdb$objects&pdbid sharing=none as
new 1: create or replace view sys.cdb$objects4 sharing=none as

View created.

CDB12C >
CDB12C >create or replace view sys.cdb$tables&pdbid sharing=none as
2 select * from sys.cdb$objects&pdbid where object_type=2;
old 1: create or replace view sys.cdb$tables&pdbid sharing=none as
new 1: create or replace view sys.cdb$tables4 sharing=none as
old 2: select * from sys.cdb$objects&pdbid where object_type=2
new 2: select * from sys.cdb$objects4 where object_type=2

View created.

CDB12C >
CDB12C >
CDB12C >---------------------------------------------------------------------------
CDB12C >-- PRE-SCRIPT CHECKS GO HERE:
CDB12C >
CDB12C >set serveroutput on
CDB12C >
CDB12C >-- Check that we have no invalid table data
CDB12C >DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error, if the database contains invalid data as a result of type
DOC> evolution which was performed without the data being converted.
DOC>
DOC> To resolve this specific "ORA-01722: invalid number" error:
DOC> Perform the data conversion (details below) in the pluggable database.
DOC>
DOC> Please refer to Oracle Database Object-Relational Developer's Guide
DOC> for more information about type evolution.
DOC>
DOC> Data in columns of evolved types must be converted before the
DOC> database can be converted.
DOC>
DOC> The following commands, run inside the PDB, will perform the data
DOC> conversion for Oracle supplied data:
DOC>
DOC> @?/rdbms/admin/utluppkg.sql
DOC> SET SERVEROUTPUT ON;
DOC> exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA');
DOC> SET SERVEROUTPUT OFF;
DOC>
DOC> You should then confirm that any non-Oracle supplied data is also
DOC> converted. You should review the data and determine if it needs
DOC> to be converted or removed.
DOC>
DOC> To view the data that is affected by type evolution, execute the
DOC> following inside the PDB:
DOC>
DOC> SELECT rpad(u.name,128) TABLENAME, rpad(o.name,128) OWNER,
DOC> rpad(c.name,128) COLNAME FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t,
DOC> SYS.USER$ u
DOC> WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# AND c.COL# = t.COL#
DOC> AND t.INTCOL# = c.INTCOL# AND BITAND(t.FLAGS, 256) = 256
DOC> AND o.OWNER# = u.USER# AND o.OWNER# NOT IN
DOC> (SELECT UNIQUE (d.USER_ID) FROM SYS.DBA_USERS d, SYS.REGISTRY$ r
DOC> WHERE d.USER_ID = r.SCHEMA# and r.NAMESPACE='SERVER');
DOC>
DOC> Once the data is confirmed, the following commands, run inside the PDB,
DOC> will convert the data returned by the above query.
DOC>
DOC> @?/rdbms/admin/utluppkg.sql
DOC> SET SERVEROUTPUT ON;
DOC> exec dbms_preup.run_fixup_and_report('INVALID_USR_TABLEDATA');
DOC> SET SERVEROUTPUT OFF;
DOC>
DOC> Depending on the amount of data involved, converting the evolved type
DOC> data can take a significant amount of time.
DOC>
DOC> After this is complete, please rerun noncdb_to_pdb.sql.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
CDB12C >
CDB12C >declare
2 do_abort boolean := false;
3 begin
4 if dbms_preup.condition_exists ('INVALID_SYS_TABLEDATA') then
5 -- dump out the info
6 dbms_preup.run_check('INVALID_SYS_TABLEDATA');
7 do_abort := TRUE;
8 end if;
9 if dbms_preup.condition_exists ('INVALID_USR_TABLEDATA') THEN
10 -- dump out the info
11 dbms_preup.run_check('INVALID_USR_TABLEDATA');
12 do_abort := TRUE;
13 END IF;
14 If do_abort THEN
15 dbms_output.put_line ('Invalid table data.');
16 dbms_output.put_line ('Non-CDB conversion aborting.');
17 dbms_output.put_line ('For instructions, look for ORA-01722 in this script.');
18 dbms_output.put_line ('Please resolve these and rerun noncdb_to_pdb.sql.');
19 RAISE INVALID_NUMBER;
20 end if;
21 end;
22 /

PL/SQL procedure successfully completed.
CDB12C >
CDB12C >-- END PRE-SCRIPT CHECKS
CDB12C >---------------------------------------------------------------------------
CDB12C >
CDB12C >-- mark users and roles in our PDB as common if they exist as common in ROOT
CDB12C >DECLARE
2 cursor c is
3 select p.user# from sys.cdb$common_users&pdbid r, sys.user$ p
4 where r.name=p.name and bitand(p.spare1, 128)=0;
5 BEGIN
6 FOR u in c
7 LOOP
8 BEGIN
9 execute immediate 'update sys.user$ set spare1=spare1+128 where user#=' ||
10 u.user#;
11 END;
12 END LOOP;
13 commit;
14 END;
15 /
old 3: select p.user# from sys.cdb$common_users&pdbid r, sys.user$ p
new 3: select p.user# from sys.cdb$common_users4 r, sys.user$ p

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DYDD-MON-YYYYHH24:MI:SS')
------------------------------------------
Fri 25-Oct-2013 15:43:41

1 row selected.

CDB12C >
CDB12C >-- mark objects in our PDB as common if they exist as common in ROOT
CDB12C >DECLARE
2 cursor c is
3 select p.object_id, p.flags-bitand(p.flags, 196608) flags,
4 decode(r.sharing, 'MDL', 65536, 131072) sharing_flag
5 from sys.cdb$common_root_objects&pdbid r, sys.cdb$objects&pdbid p
6 where r.owner=p.owner and r.object_name=p.object_name
7 and r.object_type=p.object_type and r.nsp=p.nsp
8 and (p.object_subname is null and r.object_subname is null
9 or r.object_subname=p.object_subname)
10 and decode(bitand(p.flags, 196608), 65536, 'MDL', 131072, 'OBL', 'NONE')<>r.sharing;
11 BEGIN
12 FOR obj in c
13 LOOP
14 BEGIN
15 execute immediate 'update sys.obj$ set flags=' || (obj.flags + obj.sharing_flag) ||
16 ' where obj#=' || obj.object_id;
17 END;
18 END LOOP;
19 commit;
20 END;
21 /
old 5: from sys.cdb$common_root_objects&pdbid r, sys.cdb$objects&pdbid p
new 5: from sys.cdb$common_root_objects4 r, sys.cdb$objects4 p

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DYDD-MON-YYYYHH24:MI:SS')
------------------------------------------
Fri 25-Oct-2013 15:43:44

1 row selected.

CDB12C >
CDB12C >-- generate signatures for the common tables which don't have them
CDB12C >DECLARE
2 cursor c is
3 select r.owner, r.object_name
4 from sys.cdb$common_root_objects&pdbid r, sys.cdb$tables&pdbid p
5 where r.owner=p.owner and r.object_name=p.object_name
6 and r.object_type=2 and p.object_sig is null
7 and p.object_name not in ('OBJ$', 'USER$');
8 BEGIN
9 FOR tab in c
10 LOOP
11 BEGIN
12 execute immediate 'ALTER TABLE ' || tab.owner || '."' ||
13 tab.object_name || '" UPGRADE';
14 EXCEPTION
15 WHEN OTHERS THEN
16 BEGIN
17 IF (sqlcode = -600 or sqlcode = -602 or sqlcode = -603) THEN
18 raise;
19 END IF;
20 END;
21 END;
22 END LOOP;
23 commit;
24 END;
25 /
old 4: from sys.cdb$common_root_objects&pdbid r, sys.cdb$tables&pdbid p
new 4: from sys.cdb$common_root_objects4 r, sys.cdb$tables4 p

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >-- for each table whose signature doesn't match ROOT's, mark its PL/SQL
CDB12C >-- dependents for local MCode
CDB12C >DECLARE
2 cursor c is
3 select obj#
4 from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects&pdbid ro
5 where o.type# <> 4 and u.name=ro.owner and u.user#=o.owner#
6 and o.name=ro.object_name and o.type#=ro.object_type and obj# in
7 (select d_obj# from sys.dependency$ where p_obj# in
8 (select p.object_id from sys.CDB$common_root_objects&pdbid r,
9 sys.cdb$tables&pdbid p
10 where r.owner=p.owner and r.object_name=p.object_name
11 and r.object_type=2 and r.object_sig <> p.object_sig));
12 BEGIN
13 FOR obj in c
14 LOOP
15 execute immediate
16 'update sys.obj$ set flags=flags+33554432-bitand(flags, 33554432) where obj#=' || obj.obj#;
17 END LOOP;
18 commit;
19 END;
20 /
old 4: from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects&pdbid ro
new 4: from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects4 ro
old 8: (select p.object_id from sys.CDB$common_root_objects&pdbid r,
new 8: (select p.object_id from sys.CDB$common_root_objects4 r,
old 9: sys.cdb$tables&pdbid p
new 9: sys.cdb$tables4 p

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >select owner#, name from sys.obj$ where bitand(flags, 33554432)=33554432
2 order by 1, 2;

no rows selected
CDB12C >
CDB12C >-- Step (II)
CDB12C >--
CDB12C >-- Mark all metadata links as status 6
CDB12C >-- skip types w/ non-null subname
CDB12C >update sys.obj$ set status = 6
2 where (type# not in (2, 28, 29, 30, 56))
3 and (type# <> 13 or subname is null)
4 and status not in (5,6)
5 and bitand(flags, 65536)=65536;

49546 rows updated.

CDB12C >
CDB12C >commit
2 /

Commit complete.

CDB12C >
CDB12C >-- Invalidate all synonym dependents of dbms_standard. If not we will end up
CDB12C >-- with a timestamp mismatch between dependency and obj
CDB12C >
CDB12C >update sys.obj$ set status=6 where obj# in
2 (select d_obj# from sys.dependency$
3 where p_obj# in (select obj# from sys.obj$ where name='DBMS_STANDARD' and
4 type# in ( 9, 11) and owner#=0)
5 ) and type#=5
6 /

1 row updated.

CDB12C >commit
2 /

Commit complete.

CDB12C >
CDB12C >alter system flush shared_pool
2 /

...
CDB12C >
CDB12C >-- END PRE-SCRIPT CHECKS
CDB12C >---------------------------------------------------------------------------
CDB12C >
CDB12C >-- mark users and roles in our PDB as common if they exist as common in ROOT
CDB12C >DECLARE
2 cursor c is
3 select p.user# from sys.cdb$common_users&pdbid r, sys.user$ p
4 where r.name=p.name and bitand(p.spare1, 128)=0;
5 BEGIN
6 FOR u in c
7 LOOP
8 BEGIN
9 execute immediate 'update sys.user$ set spare1=spare1+128 where user#=' ||
10 u.user#;
11 END;
12 END LOOP;
13 commit;
14 END;
15 /
old 3: select p.user# from sys.cdb$common_users&pdbid r, sys.user$ p
new 3: select p.user# from sys.cdb$common_users4 r, sys.user$ p

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DYDD-MON-YYYYHH24:MI:SS')
------------------------------------------
Fri 25-Oct-2013 15:43:41

1 row selected.

CDB12C >
CDB12C >-- mark objects in our PDB as common if they exist as common in ROOT
CDB12C >DECLARE
2 cursor c is
3 select p.object_id, p.flags-bitand(p.flags, 196608) flags,
4 decode(r.sharing, 'MDL', 65536, 131072) sharing_flag
5 from sys.cdb$common_root_objects&pdbid r, sys.cdb$objects&pdbid p
6 where r.owner=p.owner and r.object_name=p.object_name
7 and r.object_type=p.object_type and r.nsp=p.nsp
8 and (p.object_subname is null and r.object_subname is null
9 or r.object_subname=p.object_subname)
10 and decode(bitand(p.flags, 196608), 65536, 'MDL', 131072, 'OBL', 'NONE')<>r.sharing;
11 BEGIN
12 FOR obj in c
13 LOOP
14 BEGIN
15 execute immediate 'update sys.obj$ set flags=' || (obj.flags + obj.sharing_flag) ||
16 ' where obj#=' || obj.object_id;
17 END;
18 END LOOP;
19 commit;
20 END;
21 /
old 5: from sys.cdb$common_root_objects&pdbid r, sys.cdb$objects&pdbid p
new 5: from sys.cdb$common_root_objects4 r, sys.cdb$objects4 p

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DYDD-MON-YYYYHH24:MI:SS')
------------------------------------------
Fri 25-Oct-2013 15:43:44

1 row selected.

CDB12C >
CDB12C >-- generate signatures for the common tables which don't have them
CDB12C >DECLARE
2 cursor c is
3 select r.owner, r.object_name
4 from sys.cdb$common_root_objects&pdbid r, sys.cdb$tables&pdbid p
5 where r.owner=p.owner and r.object_name=p.object_name
6 and r.object_type=2 and p.object_sig is null
7 and p.object_name not in ('OBJ$', 'USER$');
8 BEGIN
9 FOR tab in c
10 LOOP
11 BEGIN
12 execute immediate 'ALTER TABLE ' || tab.owner || '."' ||
13 tab.object_name || '" UPGRADE';
14 EXCEPTION
15 WHEN OTHERS THEN
16 BEGIN
17 IF (sqlcode = -600 or sqlcode = -602 or sqlcode = -603) THEN
18 raise;
19 END IF;
20 END;
21 END;
22 END LOOP;
23 commit;
24 END;
25 /
old 4: from sys.cdb$common_root_objects&pdbid r, sys.cdb$tables&pdbid p
new 4: from sys.cdb$common_root_objects4 r, sys.cdb$tables4 p

PL/SQL procedure successfully completed.
CDB12C >
CDB12C >-- for each table whose signature doesn't match ROOT's, mark its PL/SQL
CDB12C >-- dependents for local MCode
CDB12C >DECLARE
2 cursor c is
3 select obj#
4 from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects&pdbid ro
5 where o.type# <> 4 and u.name=ro.owner and u.user#=o.owner#
6 and o.name=ro.object_name and o.type#=ro.object_type and obj# in
7 (select d_obj# from sys.dependency$ where p_obj# in
8 (select p.object_id from sys.CDB$common_root_objects&pdbid r,
9 sys.cdb$tables&pdbid p
10 where r.owner=p.owner and r.object_name=p.object_name
11 and r.object_type=2 and r.object_sig <> p.object_sig));
12 BEGIN
13 FOR obj in c
14 LOOP
15 execute immediate
16 'update sys.obj$ set flags=flags+33554432-bitand(flags, 33554432) where obj#=' || obj.obj#;
17 END LOOP;
18 commit;
19 END;
20 /
old 4: from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects&pdbid ro
new 4: from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects4 ro
old 8: (select p.object_id from sys.CDB$common_root_objects&pdbid r,
new 8: (select p.object_id from sys.CDB$common_root_objects4 r,
old 9: sys.cdb$tables&pdbid p
new 9: sys.cdb$tables4 p

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >select owner#, name from sys.obj$ where bitand(flags, 33554432)=33554432
2 order by 1, 2;

no rows selected

CDB12C >
CDB12C >-- Step (II)
CDB12C >--
CDB12C >-- Mark all metadata links as status 6
CDB12C >-- skip types w/ non-null subname
CDB12C >update sys.obj$ set status = 6
2 where (type# not in (2, 28, 29, 30, 56))
3 and (type# <> 13 or subname is null)
4 and status not in (5,6)
5 and bitand(flags, 65536)=65536;

49546 rows updated.

CDB12C >
CDB12C >commit
2 /

Commit complete.

CDB12C >
CDB12C >-- Invalidate all synonym dependents of dbms_standard. If not we will end up
CDB12C >-- with a timestamp mismatch between dependency and obj
CDB12C >
CDB12C >update sys.obj$ set status=6 where obj# in
2 (select d_obj# from sys.dependency$
3 where p_obj# in (select obj# from sys.obj$ where name='DBMS_STANDARD' and
4 type# in ( 9, 11) and owner#=0)
5 ) and type#=5
6 /

1 row updated.

CDB12C >commit
2 /

Commit complete.

CDB12C >
CDB12C >alter system flush shared_pool
2 /

System altered.

CDB12C >
CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >
CDB12C >-- Step (II)
CDB12C >--
CDB12C >-- Recreate package standard and dbms_standard. This is needed to execute
CDB12C >-- subsequent anonymous blocks
CDB12C >SET ECHO OFF

Session altered.

Session altered.

Package created.

Session altered.

Session altered.

Package body created.

Session altered.

Grant succeeded.

Session altered.

Session altered.

Package created.

Synonym created.

Grant succeeded.

Session altered.

CDB12C >-- Step (III)
CDB12C >--
CDB12C >-- Invalidate views and synonyms which depend (directly or indirectly) on
CDB12C >-- invalid objects.
CDB12C >begin
2 loop
3 update sys.obj$ o_outer set status = 6
4 where type# in (4, 5)
5 and status not in (5, 6)
6 and linkname is null
7 and ((subname is null) or (subname <> 'DBMS_DBUPGRADE_BABY'))
8 and exists (select o.obj# from sys.obj$ o, sys.dependency$ d
9 where d.d_obj# = o_outer.obj#
10 and d.p_obj# = o.obj#
11 and (bitand(d.property, 1) = 1)
12 and o.status > 1);
13 exit when sql%notfound;
14 end loop;
15 end;
16 /

PL/SQL procedure successfully completed.

CDB12C >commit;

Commit complete.
CDB12C >
CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >
CDB12C >-- normalize dependencies for classes.bin objects
CDB12C >delete from sys.dependency$ where d_obj# in (select obj# from sys.obj$ where bitand(flags,65600)=65600);

3054 rows deleted.

CDB12C >
CDB12C >insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ po
,sys.user$ pu,sys.cdb$rootdeps&pdbid rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=ref
erenced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600);
old 1: insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ p
o,sys.user$ pu,sys.cdb$rootdeps&pdbid rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=re
ferenced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600)
new 1: insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ p
o,sys.user$ pu,sys.cdb$rootdeps4 rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=referen
ced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600)

3054 rows created.

CDB12C >
CDB12C >commit;

Commit complete.

CDB12C >
CDB12C >-- get rid of idl_ub1$ rows for MDL java objects
CDB12C >delete from sys.idl_ub1$ where obj# in (select obj# from sys.obj$ where bitand(flags, 65536)=65536 and type# in (28,29,30,56));

....

old 5: from sys.cdb$common_root_objects&pdbid r, sys.cdb$objects&pdbid p
new 5: from sys.cdb$common_root_objects4 r, sys.cdb$objects4 p

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DYDD-MON-YYYYHH24:MI:SS')
------------------------------------------
Fri 25-Oct-2013 15:43:44

1 row selected.

CDB12C >
CDB12C >-- generate signatures for the common tables which don't have them
CDB12C >DECLARE
2 cursor c is
3 select r.owner, r.object_name
4 from sys.cdb$common_root_objects&pdbid r, sys.cdb$tables&pdbid p
5 where r.owner=p.owner and r.object_name=p.object_name
6 and r.object_type=2 and p.object_sig is null
7 and p.object_name not in ('OBJ$', 'USER$');
8 BEGIN
9 FOR tab in c
10 LOOP
11 BEGIN
12 execute immediate 'ALTER TABLE ' || tab.owner || '."' ||
13 tab.object_name || '" UPGRADE';
14 EXCEPTION
15 WHEN OTHERS THEN
16 BEGIN
17 IF (sqlcode = -600 or sqlcode = -602 or sqlcode = -603) THEN
18 raise;
19 END IF;
20 END;
21 END;
22 END LOOP;
23 commit;
24 END;
25 /
old 4: from sys.cdb$common_root_objects&pdbid r, sys.cdb$tables&pdbid p
new 4: from sys.cdb$common_root_objects4 r, sys.cdb$tables4 p

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >-- for each table whose signature doesn't match ROOT's, mark its PL/SQL
CDB12C >-- dependents for local MCode
CDB12C >DECLARE
2 cursor c is
3 select obj#
4 from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects&pdbid ro
5 where o.type# <> 4 and u.name=ro.owner and u.user#=o.owner#
6 and o.name=ro.object_name and o.type#=ro.object_type and obj# in
7 (select d_obj# from sys.dependency$ where p_obj# in
8 (select p.object_id from sys.CDB$common_root_objects&pdbid r,
9 sys.cdb$tables&pdbid p
10 where r.owner=p.owner and r.object_name=p.object_name
11 and r.object_type=2 and r.object_sig <> p.object_sig));
12 BEGIN
13 FOR obj in c
14 LOOP
15 execute immediate
16 'update sys.obj$ set flags=flags+33554432-bitand(flags, 33554432) where obj#=' || obj.obj#;
17 END LOOP;
18 commit;
19 END;
20 /
old 4: from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects&pdbid ro
new 4: from sys.obj$ o, sys.user$ u, sys.cdb$common_root_objects4 ro
old 8: (select p.object_id from sys.CDB$common_root_objects&pdbid r,
new 8: (select p.object_id from sys.CDB$common_root_objects4 r,
old 9: sys.cdb$tables&pdbid p
new 9: sys.cdb$tables4 p

PL/SQL procedure successfully completed.
CDB12C >
CDB12C >select owner#, name from sys.obj$ where bitand(flags, 33554432)=33554432
2 order by 1, 2;

no rows selected

CDB12C >
CDB12C >-- Step (II)
CDB12C >--
CDB12C >-- Mark all metadata links as status 6
CDB12C >-- skip types w/ non-null subname
CDB12C >update sys.obj$ set status = 6
2 where (type# not in (2, 28, 29, 30, 56))
3 and (type# <> 13 or subname is null)
4 and status not in (5,6)
5 and bitand(flags, 65536)=65536;

49546 rows updated.

CDB12C >
CDB12C >commit
2 /

Commit complete.

CDB12C >
CDB12C >-- Invalidate all synonym dependents of dbms_standard. If not we will end up
CDB12C >-- with a timestamp mismatch between dependency and obj
CDB12C >
CDB12C >update sys.obj$ set status=6 where obj# in
2 (select d_obj# from sys.dependency$
3 where p_obj# in (select obj# from sys.obj$ where name='DBMS_STANDARD' and
4 type# in ( 9, 11) and owner#=0)
5 ) and type#=5
6 /

1 row updated.

CDB12C >commit
2 /

Commit complete.

CDB12C >
CDB12C >alter system flush shared_pool
2 /

System altered.

CDB12C >
CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >
CDB12C >-- Step (II)
CDB12C >--
CDB12C >-- Recreate package standard and dbms_standard. This is needed to execute
CDB12C >-- subsequent anonymous blocks
CDB12C >SET ECHO OFF

Session altered.

Session altered.

Package created.

Session altered.

Session altered.

Package body created.

Session altered.

Grant succeeded.

Session altered.

Session altered.

Package created.

Synonym created.

Grant succeeded.

Session altered.

CDB12C >-- Step (III)
CDB12C >--
CDB12C >-- Invalidate views and synonyms which depend (directly or indirectly) on
CDB12C >-- invalid objects.
CDB12C >begin
2 loop
3 update sys.obj$ o_outer set status = 6
4 where type# in (4, 5)
5 and status not in (5, 6)
6 and linkname is null
7 and ((subname is null) or (subname <> 'DBMS_DBUPGRADE_BABY'))
8 and exists (select o.obj# from sys.obj$ o, sys.dependency$ d
9 where d.d_obj# = o_outer.obj#
10 and d.p_obj# = o.obj#
11 and (bitand(d.property, 1) = 1)
12 and o.status > 1);
13 exit when sql%notfound;
14 end loop;
15 end;
16 /

PL/SQL procedure successfully completed.

CDB12C >commit;

Commit complete.

CDB12C >
CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >
CDB12C >-- normalize dependencies for classes.bin objects
CDB12C >delete from sys.dependency$ where d_obj# in (select obj# from sys.obj$ where bitand(flags,65600)=65600);

3054 rows deleted.

CDB12C >
CDB12C >insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ po
,sys.user$ pu,sys.cdb$rootdeps&pdbid rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=ref
erenced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600);
old 1: insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ p
o,sys.user$ pu,sys.cdb$rootdeps&pdbid rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=re
ferenced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600)
new 1: insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ p
o,sys.user$ pu,sys.cdb$rootdeps4 rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=referen
ced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600)

3054 rows created.

CDB12C >
CDB12C >commit;

Commit complete.
CDB12C >
CDB12C >-- get rid of idl_ub1$ rows for MDL java objects
CDB12C >delete from sys.idl_ub1$ where obj# in (select obj# from sys.obj$ where bitand(flags, 65536)=65536 and type# in (28,29,30,56));

32729 rows deleted.

CDB12C >commit;

Commit complete.

CDB12C >
CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >
CDB12C >-- explicitly compile these now, before close/reopen. Otherwise they would
CDB12C >-- be used/validated within PDB Open, where such patching (clearing of dict
CDB12C >-- rows) can't be done.
CDB12C >alter public synonym ALL_OBJECTS compile;

Synonym altered.

CDB12C >alter view V_$PARAMETER compile;

View altered.

CDB12C >
CDB12C >WHENEVER SQLERROR CONTINUE;
CDB12C >alter type ANYDATA compile;

Type altered.

CDB12C >WHENEVER SQLERROR EXIT;
CDB12C >
CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >
CDB12C >-- reopen the PDB
CDB12C >alter pluggable database "&pdbname" close;
old 1: alter pluggable database "&pdbname" close
new 1: alter pluggable database "PDB2" close
....

12 and o.status > 1);
13 exit when sql%notfound;
14 end loop;
15 end;
16 /

PL/SQL procedure successfully completed.

CDB12C >commit;

Commit complete.

CDB12C >
CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >
CDB12C >-- normalize dependencies for classes.bin objects
CDB12C >delete from sys.dependency$ where d_obj# in (select obj# from sys.obj$ where bitand(flags,65600)=65600);

3054 rows deleted.

CDB12C >
CDB12C >insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ po
,sys.user$ pu,sys.cdb$rootdeps&pdbid rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=ref
erenced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600);
old 1: insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ p
o,sys.user$ pu,sys.cdb$rootdeps&pdbid rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=re
ferenced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600)
new 1: insert into sys.dependency$ (select do.obj#,do.stime,order#,po.obj#,po.stime,do.owner#,property,d_attrs,d_reason from sys.obj$ do,sys.user$ du,sys.obj$ p
o,sys.user$ pu,sys.cdb$rootdeps4 rd where du.user#=do.owner# and pu.user#=po.owner# and do.name=rd.name and du.name=owner and do.type#=d_type# and po.name=referen
ced_name and pu.name=referenced_owner and po.type#=p_type# and bitand(do.flags,65600)=65600)

3054 rows created.

CDB12C >
CDB12C >commit;

Commit complete.

CDB12C >
CDB12C >-- get rid of idl_ub1$ rows for MDL java objects
CDB12C >delete from sys.idl_ub1$ where obj# in (select obj# from sys.obj$ where bitand(flags, 65536)=65536 and type# in (28,29,30,56));

32729 rows deleted.

CDB12C >commit;

Commit complete.

CDB12C >
CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >
CDB12C >-- explicitly compile these now, before close/reopen. Otherwise they would
CDB12C >-- be used/validated within PDB Open, where such patching (clearing of dict
CDB12C >-- rows) can't be done.
CDB12C >alter public synonym ALL_OBJECTS compile;

Synonym altered.

CDB12C >alter view V_$PARAMETER compile;

View altered.

CDB12C >
CDB12C >WHENEVER SQLERROR CONTINUE;
CDB12C >alter type ANYDATA compile;

Type altered.

CDB12C >WHENEVER SQLERROR EXIT;
CDB12C >
CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >
CDB12C >-- reopen the PDB
CDB12C >alter pluggable database "&pdbname" close;
old 1: alter pluggable database "&pdbname" close
new 1: alter pluggable database "PDB2" close

Pluggable database altered.

CDB12C >alter pluggable database "&pdbname" open restricted;
old 1: alter pluggable database "&pdbname" open restricted
new 1: alter pluggable database "PDB2" open restricted

Warning: PDB altered with errors.

CDB12C >ALTER SYSTEM SET "_system_trig_enabled"=FALSE SCOPE=MEMORY;

System altered.
CDB12C >
CDB12C >alter session set "_ORACLE_SCRIPT"=false;

Session altered.

CDB12C >
CDB12C >select status, count(*) from sys.obj$ where status in (5, 6) group by status order by 1;

STATUS COUNT(*)
---------- ----------
5 1
6 50054

2 rows selected.

CDB12C >select count(*) from sys.view$;

COUNT(*)
----------
6208

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
2 (select obj# from sys.view$);

COUNT(*)
----------
6030

1 row selected.

CDB12C >select count(*) from sys.procedure$;

COUNT(*)
----------
3624

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
2 (select obj# from sys.procedure$);

COUNT(*)
----------
3601

1 row selected.

CDB12C >select count(*) from sys.dir$;

COUNT(*)
----------
15

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
2 (select obj# from sys.dir$);

COUNT(*)
----------
6

1 row selected.

CDB12C >
CDB12C >@@utlrp
CDB12C >Rem
CDB12C >Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $
CDB12C >Rem
CDB12C >Rem utlrp.sql
CDB12C >Rem
CDB12C >Rem Copyright (c) 1998, 2003, Oracle Corporation. All rights reserved.
CDB12C >Rem
CDB12C >Rem NAME
CDB12C >Rem utlrp.sql - Recompile invalid objects
CDB12C >Rem
CDB12C >Rem DESCRIPTION
CDB12C >Rem This script recompiles invalid objects in the database.
CDB12C >Rem
CDB12C >Rem When run as one of the last steps during upgrade or downgrade,
CDB12C >Rem this script will validate all remaining invalid objects. It will
CDB12C >Rem also run a component validation procedure for each component in
CDB12C >Rem the database. See the README notes for your current release and
CDB12C >Rem the Oracle Database Upgrade book for more information about
CDB12C >Rem using utlrp.sql
CDB12C >Rem
CDB12C >Rem Although invalid objects are automatically re-validated when used,
CDB12C >Rem it is useful to run this script after an upgrade or downgrade and
CDB12C >Rem after applying a patch. This minimizes latencies caused by
CDB12C >Rem on-demand recompilation. Oracle strongly recommends running this
CDB12C >Rem script after upgrades, downgrades and patches.
CDB12C >Rem
CDB12C >Rem NOTES
CDB12C >Rem * This script must be run using SQL*PLUS.
CDB12C >Rem * You must be connected AS SYSDBA to run this script.
CDB12C >Rem * There should be no other DDL on the database while running the
CDB12C >Rem script. Not following this recommendation may lead to deadlocks.
CDB12C >Rem
CDB12C >Rem MODIFIED (MM/DD/YY)
CDB12C >Rem gviswana 06/26/03 - Switch default to parallel if appropriate
CDB12C >Rem gviswana 06/12/03 - Switch default back to serial
CDB12C >Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning
CDB12C >Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics
CDB12C >Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc
CDB12C >Rem gviswana 06/25/02 - Add documentation
CDB12C >Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial
CDB12C >Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
CDB12C >Rem rburns 11/12/01 - validate all components after compiles
CDB12C >Rem rburns 11/06/01 - fix invalid CATPROC call
CDB12C >Rem rburns 09/29/01 - use 9.2.0
CDB12C >Rem rburns 09/20/01 - add check for CATPROC valid
CDB12C >Rem rburns 07/06/01 - get version from instance view
CDB12C >Rem rburns 05/09/01 - fix for use with 8.1.x
CDB12C >Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33
CDB12C >Rem skabraha 09/25/00 - validate is now a keyword
CDB12C >Rem kosinski 06/14/00 - Persistent parameters
CDB12C >Rem skabraha 06/05/00 - validate tables also
CDB12C >Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.
CDB12C >Rem rshaikh 09/22/99 - quote name for recompile
CDB12C >Rem ncramesh 08/04/98 - change for sqlplus
CDB12C >Rem usundara 06/03/98 - merge from 8.0.5
CDB12C >Rem usundara 04/29/98 - creation (split from utlirp.sql).
CDB12C >Rem Mark Ramacher (mramache) was the original
CDB12C >Rem author of this script.
CDB12C >Rem
CDB12C >
CDB12C >Rem ===========================================================================
CDB12C >Rem BEGIN utlrp.sql
CDB12C >Rem ===========================================================================
CDB12C >
CDB12C >@@utlprp.sql 0
CDB12C >Rem Copyright (c) 2003, 2011, Oracle and/or its affiliates.
CDB12C >Rem All rights reserved.
CDB12C >Rem
CDB12C >Rem NAME
CDB12C >Rem utlprp.sql - Recompile invalid objects in the database
CDB12C >Rem
CDB12C >Rem DESCRIPTION
CDB12C >Rem This script recompiles invalid objects in the database.
CDB12C >Rem
CDB12C >Rem This script is typically used to recompile invalid objects
CDB12C >Rem remaining at the end of a database upgrade or downgrade.
CDB12C >Rem
CDB12C >Rem Although invalid objects are automatically recompiled on demand,
CDB12C >Rem running this script ahead of time will reduce or eliminate
CDB12C >Rem latencies due to automatic recompilation.
CDB12C >Rem
CDB12C >Rem This script is a wrapper based on the UTL_RECOMP package.
CDB12C >Rem UTL_RECOMP provides a more general recompilation interface,
CDB12C >Rem including options to recompile objects in a single schema. Please
CDB12C >Rem see the documentation for package UTL_RECOMP for more details.
CDB12C >Rem
CDB12C >Rem INPUTS
CDB12C >Rem The degree of parallelism for recompilation can be controlled by
CDB12C >Rem providing a parameter to this script. If this parameter is 0 or
CDB12C >Rem NULL, UTL_RECOMP will automatically determine the appropriate
CDB12C >Rem level of parallelism based on Oracle parameters cpu_count and
CDB12C >Rem parallel_threads_per_cpu. If the parameter is 1, sequential
CDB12C >Rem recompilation is used. Please see the documentation for package
CDB12C >Rem UTL_RECOMP for more details.
CDB12C >Rem
CDB12C >Rem NOTES
CDB12C >Rem * You must be connected AS SYSDBA to run this script.
CDB12C >Rem * There should be no other DDL on the database while running the
CDB12C >Rem script. Not following this recommendation may lead to deadlocks.
CDB12C >Rem
CDB12C >Rem MODIFIED (MM/DD/YY)
CDB12C >Rem kquinn 11/01/11 - 13059165: amend 'OBJECTS WITH ERRORS' SQL
CDB12C >Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name
CDB12C >Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes
CDB12C >Rem not needed.
CDB12C >Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270
CDB12C >Rem cdilling 01/21/08 - add support for ORA-30552
CDB12C >Rem cdilling 08/27/07 - check disabled indexes only
CDB12C >Rem cdilling 05/22/07 - add support for ORA-38301
CDB12C >Rem cdilling 02/19/07 - 5530085 - renable invalid indexes
CDB12C >Rem rburns 03/17/05 - use dbms_registry_sys
CDB12C >Rem gviswana 02/07/05 - Post-compilation diagnostics
CDB12C >Rem gviswana 09/09/04 - Auto tuning and diagnosability
CDB12C >Rem rburns 09/20/04 - fix validate_components
CDB12C >Rem gviswana 12/09/03 - Move functional-index re-enable here
CDB12C >Rem gviswana 06/04/03 - gviswana_bug-2814808
CDB12C >Rem gviswana 05/28/03 - Created
CDB12C >Rem
CDB12C >
CDB12C >SET VERIFY OFF;
CDB12C >
CDB12C >SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;

....

CDB12C >-- get rid of idl_ub1$ rows for MDL java objects
CDB12C >delete from sys.idl_ub1$ where obj# in (select obj# from sys.obj$ where bitand(flags, 65536)=65536 and type# in (28,29,30,56));

32729 rows deleted.

CDB12C >commit;

Commit complete.

CDB12C >
CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >
CDB12C >-- explicitly compile these now, before close/reopen. Otherwise they would
CDB12C >-- be used/validated within PDB Open, where such patching (clearing of dict
CDB12C >-- rows) can't be done.
CDB12C >alter public synonym ALL_OBJECTS compile;

Synonym altered.

CDB12C >alter view V_$PARAMETER compile;

View altered.

CDB12C >
CDB12C >WHENEVER SQLERROR CONTINUE;
CDB12C >alter type ANYDATA compile;

Type altered.

CDB12C >WHENEVER SQLERROR EXIT;
CDB12C >
CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.
CDB12C >
CDB12C >-- reopen the PDB
CDB12C >alter pluggable database "&pdbname" close;
old 1: alter pluggable database "&pdbname" close
new 1: alter pluggable database "PDB2" close

Pluggable database altered.

CDB12C >alter pluggable database "&pdbname" open restricted;
old 1: alter pluggable database "&pdbname" open restricted
new 1: alter pluggable database "PDB2" open restricted

Warning: PDB altered with errors.

CDB12C >ALTER SYSTEM SET "_system_trig_enabled"=FALSE SCOPE=MEMORY;

System altered.

CDB12C >
CDB12C >alter session set "_ORACLE_SCRIPT"=false;

Session altered.

CDB12C >
CDB12C >select status, count(*) from sys.obj$ where status in (5, 6) group by status order by 1;

STATUS COUNT(*)
---------- ----------
5 1
6 50054

2 rows selected.

CDB12C >select count(*) from sys.view$;

COUNT(*)
----------
6208

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
2 (select obj# from sys.view$);

COUNT(*)
----------
6030

1 row selected.

CDB12C >select count(*) from sys.procedure$;

COUNT(*)
----------
3624

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
2 (select obj# from sys.procedure$);

COUNT(*)
----------
3601

1 row selected.

CDB12C >select count(*) from sys.dir$;

COUNT(*)
----------
15

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in
2 (select obj# from sys.dir$);

COUNT(*)
----------
6

1 row selected.

CDB12C >
CDB12C >@@utlrp
CDB12C >Rem
CDB12C >Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $
CDB12C >Rem
CDB12C >Rem utlrp.sql
CDB12C >Rem
CDB12C >Rem Copyright (c) 1998, 2003, Oracle Corporation. All rights reserved.
CDB12C >Rem
CDB12C >Rem NAME
CDB12C >Rem utlrp.sql - Recompile invalid objects
CDB12C >Rem
CDB12C >Rem DESCRIPTION
CDB12C >Rem This script recompiles invalid objects in the database.
CDB12C >Rem
CDB12C >Rem When run as one of the last steps during upgrade or downgrade,
CDB12C >Rem this script will validate all remaining invalid objects. It will
CDB12C >Rem also run a component validation procedure for each component in
CDB12C >Rem the database. See the README notes for your current release and
CDB12C >Rem the Oracle Database Upgrade book for more information about
CDB12C >Rem using utlrp.sql
CDB12C >Rem
CDB12C >Rem Although invalid objects are automatically re-validated when used,
CDB12C >Rem it is useful to run this script after an upgrade or downgrade and
CDB12C >Rem after applying a patch. This minimizes latencies caused by
CDB12C >Rem on-demand recompilation. Oracle strongly recommends running this
CDB12C >Rem script after upgrades, downgrades and patches.
CDB12C >Rem
CDB12C >Rem NOTES
CDB12C >Rem * This script must be run using SQL*PLUS.
CDB12C >Rem * You must be connected AS SYSDBA to run this script.
CDB12C >Rem * There should be no other DDL on the database while running the
CDB12C >Rem script. Not following this recommendation may lead to deadlocks.
CDB12C >Rem
CDB12C >Rem MODIFIED (MM/DD/YY)
CDB12C >Rem gviswana 06/26/03 - Switch default to parallel if appropriate
CDB12C >Rem gviswana 06/12/03 - Switch default back to serial
CDB12C >Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning
CDB12C >Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics
CDB12C >Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc
CDB12C >Rem gviswana 06/25/02 - Add documentation
CDB12C >Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial
CDB12C >Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
CDB12C >Rem rburns 11/12/01 - validate all components after compiles
CDB12C >Rem rburns 11/06/01 - fix invalid CATPROC call
CDB12C >Rem rburns 09/29/01 - use 9.2.0
CDB12C >Rem rburns 09/20/01 - add check for CATPROC valid
CDB12C >Rem rburns 07/06/01 - get version from instance view
CDB12C >Rem rburns 05/09/01 - fix for use with 8.1.x
CDB12C >Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33
CDB12C >Rem skabraha 09/25/00 - validate is now a keyword
CDB12C >Rem kosinski 06/14/00 - Persistent parameters
CDB12C >Rem skabraha 06/05/00 - validate tables also
CDB12C >Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.
CDB12C >Rem rshaikh 09/22/99 - quote name for recompile
CDB12C >Rem ncramesh 08/04/98 - change for sqlplus
CDB12C >Rem usundara 06/03/98 - merge from 8.0.5
CDB12C >Rem usundara 04/29/98 - creation (split from utlirp.sql).
CDB12C >Rem Mark Ramacher (mramache) was the original
CDB12C >Rem author of this script.
CDB12C >Rem
CDB12C >
CDB12C >Rem ===========================================================================
CDB12C >Rem BEGIN utlrp.sql
CDB12C >Rem ===========================================================================
CDB12C >
CDB12C >@@utlprp.sql 0
CDB12C >Rem Copyright (c) 2003, 2011, Oracle and/or its affiliates.
CDB12C >Rem All rights reserved.
CDB12C >Rem
CDB12C >Rem NAME
CDB12C >Rem utlprp.sql - Recompile invalid objects in the database
CDB12C >Rem
CDB12C >Rem DESCRIPTION
CDB12C >Rem This script recompiles invalid objects in the database.
CDB12C >Rem
CDB12C >Rem This script is typically used to recompile invalid objects
CDB12C >Rem remaining at the end of a database upgrade or downgrade.
CDB12C >Rem
CDB12C >Rem Although invalid objects are automatically recompiled on demand,
CDB12C >Rem running this script ahead of time will reduce or eliminate
CDB12C >Rem latencies due to automatic recompilation.
CDB12C >Rem
CDB12C >Rem This script is a wrapper based on the UTL_RECOMP package.
CDB12C >Rem UTL_RECOMP provides a more general recompilation interface,
CDB12C >Rem including options to recompile objects in a single schema. Please
CDB12C >Rem see the documentation for package UTL_RECOMP for more details.
CDB12C >Rem
CDB12C >Rem INPUTS
CDB12C >Rem The degree of parallelism for recompilation can be controlled by
CDB12C >Rem providing a parameter to this script. If this parameter is 0 or
CDB12C >Rem NULL, UTL_RECOMP will automatically determine the appropriate
CDB12C >Rem level of parallelism based on Oracle parameters cpu_count and
CDB12C >Rem parallel_threads_per_cpu. If the parameter is 1, sequential
CDB12C >Rem recompilation is used. Please see the documentation for package
CDB12C >Rem UTL_RECOMP for more details.
CDB12C >Rem
CDB12C >Rem NOTES
CDB12C >Rem * You must be connected AS SYSDBA to run this script.
CDB12C >Rem * There should be no other DDL on the database while running the
CDB12C >Rem script. Not following this recommendation may lead to deadlocks.
CDB12C >Rem
CDB12C >Rem MODIFIED (MM/DD/YY)
CDB12C >Rem kquinn 11/01/11 - 13059165: amend 'OBJECTS WITH ERRORS' SQL
CDB12C >Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name
CDB12C >Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes
CDB12C >Rem not needed.
CDB12C >Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270
CDB12C >Rem cdilling 01/21/08 - add support for ORA-30552
CDB12C >Rem cdilling 08/27/07 - check disabled indexes only
CDB12C >Rem cdilling 05/22/07 - add support for ORA-38301
CDB12C >Rem cdilling 02/19/07 - 5530085 - renable invalid indexes
CDB12C >Rem rburns 03/17/05 - use dbms_registry_sys
CDB12C >Rem gviswana 02/07/05 - Post-compilation diagnostics
CDB12C >Rem gviswana 09/09/04 - Auto tuning and diagnosability
CDB12C >Rem rburns 09/20/04 - fix validate_components
CDB12C >Rem gviswana 12/09/03 - Move functional-index re-enable here
CDB12C >Rem gviswana 06/04/03 - gviswana_bug-2814808
CDB12C >Rem gviswana 05/28/03 - Created
CDB12C >Rem
CDB12C >
CDB12C >SET VERIFY OFF;
CDB12C >
CDB12C >SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2013-10-25 15:45:36

1 row selected.
CDB12C >
CDB12C >DOC
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
CDB12C >
CDB12C >DECLARE
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;
6 /
....

CDB12C >Rem * You must be connected AS SYSDBA to run this script.
CDB12C >Rem * There should be no other DDL on the database while running the
CDB12C >Rem script. Not following this recommendation may lead to deadlocks.
CDB12C >Rem
CDB12C >Rem MODIFIED (MM/DD/YY)
CDB12C >Rem gviswana 06/26/03 - Switch default to parallel if appropriate
CDB12C >Rem gviswana 06/12/03 - Switch default back to serial
CDB12C >Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning
CDB12C >Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics
CDB12C >Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc
CDB12C >Rem gviswana 06/25/02 - Add documentation
CDB12C >Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial
CDB12C >Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
CDB12C >Rem rburns 11/12/01 - validate all components after compiles
CDB12C >Rem rburns 11/06/01 - fix invalid CATPROC call
CDB12C >Rem rburns 09/29/01 - use 9.2.0
CDB12C >Rem rburns 09/20/01 - add check for CATPROC valid
CDB12C >Rem rburns 07/06/01 - get version from instance view
CDB12C >Rem rburns 05/09/01 - fix for use with 8.1.x
CDB12C >Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33
CDB12C >Rem skabraha 09/25/00 - validate is now a keyword
CDB12C >Rem kosinski 06/14/00 - Persistent parameters
CDB12C >Rem skabraha 06/05/00 - validate tables also
CDB12C >Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.
CDB12C >Rem rshaikh 09/22/99 - quote name for recompile
CDB12C >Rem ncramesh 08/04/98 - change for sqlplus
CDB12C >Rem usundara 06/03/98 - merge from 8.0.5
CDB12C >Rem usundara 04/29/98 - creation (split from utlirp.sql).
CDB12C >Rem Mark Ramacher (mramache) was the original
CDB12C >Rem author of this script.
CDB12C >Rem
CDB12C >
CDB12C >Rem ===========================================================================
CDB12C >Rem BEGIN utlrp.sql
CDB12C >Rem ===========================================================================
CDB12C >
CDB12C >@@utlprp.sql 0
CDB12C >Rem Copyright (c) 2003, 2011, Oracle and/or its affiliates.
CDB12C >Rem All rights reserved.
CDB12C >Rem
CDB12C >Rem NAME
CDB12C >Rem utlprp.sql - Recompile invalid objects in the database
CDB12C >Rem
CDB12C >Rem DESCRIPTION
CDB12C >Rem This script recompiles invalid objects in the database.
CDB12C >Rem
CDB12C >Rem This script is typically used to recompile invalid objects
CDB12C >Rem remaining at the end of a database upgrade or downgrade.
CDB12C >Rem
CDB12C >Rem Although invalid objects are automatically recompiled on demand,
CDB12C >Rem running this script ahead of time will reduce or eliminate
CDB12C >Rem latencies due to automatic recompilation.
CDB12C >Rem
CDB12C >Rem This script is a wrapper based on the UTL_RECOMP package.
CDB12C >Rem UTL_RECOMP provides a more general recompilation interface,
CDB12C >Rem including options to recompile objects in a single schema. Please
CDB12C >Rem see the documentation for package UTL_RECOMP for more details.
CDB12C >Rem
CDB12C >Rem INPUTS
CDB12C >Rem The degree of parallelism for recompilation can be controlled by
CDB12C >Rem providing a parameter to this script. If this parameter is 0 or
CDB12C >Rem NULL, UTL_RECOMP will automatically determine the appropriate
CDB12C >Rem level of parallelism based on Oracle parameters cpu_count and
CDB12C >Rem parallel_threads_per_cpu. If the parameter is 1, sequential
CDB12C >Rem recompilation is used. Please see the documentation for package
CDB12C >Rem UTL_RECOMP for more details.
CDB12C >Rem
CDB12C >Rem NOTES
CDB12C >Rem * You must be connected AS SYSDBA to run this script.
CDB12C >Rem * There should be no other DDL on the database while running the
CDB12C >Rem script. Not following this recommendation may lead to deadlocks.
CDB12C >Rem
CDB12C >Rem MODIFIED (MM/DD/YY)
CDB12C >Rem kquinn 11/01/11 - 13059165: amend 'OBJECTS WITH ERRORS' SQL
CDB12C >Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name
CDB12C >Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes
CDB12C >Rem not needed.
CDB12C >Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270
CDB12C >Rem cdilling 01/21/08 - add support for ORA-30552
CDB12C >Rem cdilling 08/27/07 - check disabled indexes only
CDB12C >Rem cdilling 05/22/07 - add support for ORA-38301
CDB12C >Rem cdilling 02/19/07 - 5530085 - renable invalid indexes
CDB12C >Rem rburns 03/17/05 - use dbms_registry_sys
CDB12C >Rem gviswana 02/07/05 - Post-compilation diagnostics
CDB12C >Rem gviswana 09/09/04 - Auto tuning and diagnosability
CDB12C >Rem rburns 09/20/04 - fix validate_components
CDB12C >Rem gviswana 12/09/03 - Move functional-index re-enable here
CDB12C >Rem gviswana 06/04/03 - gviswana_bug-2814808
CDB12C >Rem gviswana 05/28/03 - Created
CDB12C >Rem
CDB12C >
CDB12C >SET VERIFY OFF;
CDB12C >
CDB12C >SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2013-10-25 15:45:36

1 row selected.

CDB12C >
CDB12C >DOC
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
CDB12C >
CDB12C >DECLARE
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;
6 /

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2013-10-25 16:00:05

1 row selected.

CDB12C >
CDB12C >Rem #(8264899): The code to Re-enable functional indexes, which used to exist
CDB12C >Rem here, is no longer needed.
CDB12C >
CDB12C >DOC
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
CDB12C >select COUNT(DISTINCT(obj#)) "OBJECTS WITH ERRORS" from utl_recomp_errors;

OBJECTS WITH ERRORS
-------------------
0

1 row selected.
CDB12C >
CDB12C >
CDB12C >DOC
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
CDB12C >select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;

ERRORS DURING RECOMPILATION
---------------------------
0

1 row selected.

CDB12C >
CDB12C >
CDB12C >Rem
CDB12C >Rem Declare function local_enquote_name to pass FALSE
CDB12C >Rem into underlying dbms_assert.enquote_name function
CDB12C >Rem
CDB12C >CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2)
2 return varchar2 is
3 begin
4 return dbms_assert.enquote_name(str, FALSE);
5 end local_enquote_name;
6 /

Function created.

CDB12C >Rem
CDB12C >Rem If sys.enabled$index table exists, then re-enable
CDB12C >Rem list of functional indexes that were enabled prior to upgrade
CDB12C >Rem The table sys.enabled$index table is created in catupstr.sql
CDB12C >Rem
CDB12C >SET serveroutput on
CDB12C >DECLARE
2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 commands tab_char;
4 p_null CHAR(1);
5 p_schemaname VARCHAR2(30);
6 p_indexname VARCHAR2(30);
7 rebuild_idx_msg BOOLEAN := FALSE;
8 non_existent_index exception;
9 recycle_bin_objs exception;
10 cannot_change_obj exception;
11 no_such_table exception;
12 pragma exception_init(non_existent_index, -1418);
13 pragma exception_init(recycle_bin_objs, -38301);
14 pragma exception_init(cannot_change_obj, -30552);
15 pragma exception_init(no_such_table, -942);
16 type cursor_t IS REF CURSOR;
17 reg_cursor cursor_t;
18
19 BEGIN
20 -- Check for existence of the table marking disabled functional indices
21
22 SELECT NULL INTO p_null FROM DBA_OBJECTS
23 WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and
24 object_type = 'TABLE' and rownum <=1;
25
26 -- Select indices to be re-enabled
27 EXECUTE IMMEDIATE q'+
28 SELECT 'ALTER INDEX ' ||
29 local_enquote_name(e.schemaname) || '.' ||
30 local_enquote_name(e.indexname) || ' ENABLE'
31 FROM enabled$indexes e, ind$ i
32 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND
33 bitand(i.property, 16) != 0+'
34 BULK COLLECT INTO commands;
35
36 IF (commands.count() > 0) THEN
37 FOR i IN 1 .. commands.count() LOOP
38 BEGIN
39 EXECUTE IMMEDIATE commands(i);
40 EXCEPTION
41 WHEN NON_EXISTENT_INDEX THEN NULL;
42 WHEN RECYCLE_BIN_OBJS THEN NULL;
43 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;
44 END;
45 END LOOP;
46 END IF;
47
48 -- Output any indexes in the table that could not be re-enabled
49 -- due to ORA-30552 during ALTER INDEX...ENBLE command
50
51 IF rebuild_idx_msg THEN
52 BEGIN
53 DBMS_OUTPUT.PUT_LINE
54 ('The following indexes could not be re-enabled and may need to be rebuilt:');
55
56 OPEN reg_cursor FOR
57 'SELECT e.schemaname, e.indexname
58 FROM enabled$indexes e, ind$ i
59 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';
60
61 LOOP
62 FETCH reg_cursor INTO p_schemaname, p_indexname;
63 EXIT WHEN reg_cursor%NOTFOUND;
64 DBMS_OUTPUT.PUT_LINE
65 ('.... INDEX ' || p_schemaname || '.' || p_indexname);
66 END LOOP;
67 CLOSE reg_cursor;
68
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;
71 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;
72 WHEN OTHERS THEN CLOSE reg_cursor; raise;
73 END;
74
75 END IF;
76
77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';
78
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN NULL;
81
82 END;
83 /

PL/SQL procedure successfully completed.

CDB12C >
CDB12C >DROP function local_enquote_name;

Function dropped.

CDB12C >
CDB12C >Rem =====================================================================
CDB12C >Rem Run component validation procedure
CDB12C >Rem =====================================================================
CDB12C >
CDB12C >EXECUTE dbms_registry_sys.validate_components;
.....

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC> objects in the database. Recompilation time is proportional to the

DOC> number of invalid objects in the database, so this command may take

DOC> a long time to execute on a database with a large number of invalid

DOC> objects.

DOC>

DOC> Use the following queries to track recompilation progress:

DOC>

DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.

DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC> This script automatically chooses serial or parallel recompilation

DOC> based on the number of CPUs available (parameter cpu_count) multiplied

DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC> On RAC, this number is added across all RAC nodes.

DOC>

DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC> recompilation. Jobs are created without instance affinity so that they

DOC> can migrate across RAC nodes. Use the following queries to verify

DOC> whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

CDB12C >

CDB12C >DECLARE

2 threads pls_integer := &&1;

3 BEGIN

4 utl_recomp.recomp_parallel(threads);

5 END;

6 /

PL/SQL procedure successfully completed.

CDB12C >

CDB12C >SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END 2013-10-25 16:00:05

1 row selected.

CDB12C >

CDB12C >Rem #(8264899): The code to Re-enable functional indexes, which used to exist

CDB12C >Rem here, is no longer needed.

CDB12C >

CDB12C >DOC

DOC> The following query reports the number of objects that have compiled

DOC> with errors.

DOC>

DOC> If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

CDB12C >select COUNT(DISTINCT(obj#)) "OBJECTS WITH ERRORS" from utl_recomp_errors;

OBJECTS WITH ERRORS

-------------------

1 row selected.

CDB12C >

CDB12C >DOC

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

CDB12C >select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;

ERRORS DURING RECOMPILATION

---------------------------

1 row selected.

CDB12C >

CDB12C >Rem

CDB12C >Rem Declare function local_enquote_name to pass FALSE

CDB12C >Rem into underlying dbms_assert.enquote_name function

CDB12C >Rem

CDB12C >CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2)

2 return varchar2 is

3 begin

4 return dbms_assert.enquote_name(str, FALSE);

5 end local_enquote_name;

6 /

Function created.

CDB12C >Rem

CDB12C >Rem If sys.enabled$index table exists, then re-enable

CDB12C >Rem list of functional indexes that were enabled prior to upgrade

CDB12C >Rem The table sys.enabled$index table is created in catupstr.sql

CDB12C >Rem

CDB12C >SET serveroutput on

CDB12C >DECLARE

2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

3 commands tab_char;

4 p_null CHAR(1);

5 p_schemaname VARCHAR2(30);

6 p_indexname VARCHAR2(30);

7 rebuild_idx_msg BOOLEAN := FALSE;

8 non_existent_index exception;

9 recycle_bin_objs exception;

10 cannot_change_obj exception;

11 no_such_table exception;

12 pragma exception_init(non_existent_index, -1418);

13 pragma exception_init(recycle_bin_objs, -38301);

14 pragma exception_init(cannot_change_obj, -30552);

15 pragma exception_init(no_such_table, -942);

16 type cursor_t IS REF CURSOR;

17 reg_cursor cursor_t;

19 BEGIN

20 -- Check for existence of the table marking disabled functional indices

22 SELECT NULL INTO p_null FROM DBA_OBJECTS

23 WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and

24 object_type = 'TABLE' and rownum <=1;

26 -- Select indices to be re-enabled

27 EXECUTE IMMEDIATE q'+

28 SELECT 'ALTER INDEX ' ||

29 local_enquote_name(e.schemaname) || '.' ||

30 local_enquote_name(e.indexname) || ' ENABLE'

31 FROM enabled$indexes e, ind$ i

32 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND

33 bitand(i.property, 16) != 0+'

34 BULK COLLECT INTO commands;

36 IF (commands.count() > 0) THEN

37 FOR i IN 1 .. commands.count() LOOP

38 BEGIN

39 EXECUTE IMMEDIATE commands(i);

40 EXCEPTION

41 WHEN NON_EXISTENT_INDEX THEN NULL;

42 WHEN RECYCLE_BIN_OBJS THEN NULL;

43 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;

44 END;

45 END LOOP;

46 END IF;

48 -- Output any indexes in the table that could not be re-enabled

49 -- due to ORA-30552 during ALTER INDEX...ENBLE command

51 IF rebuild_idx_msg THEN

52 BEGIN

53 DBMS_OUTPUT.PUT_LINE

54 ('The following indexes could not be re-enabled and may need to be rebuilt:');

56 OPEN reg_cursor FOR

57 'SELECT e.schemaname, e.indexname

58 FROM enabled$indexes e, ind$ i

59 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';

61 LOOP

62 FETCH reg_cursor INTO p_schemaname, p_indexname;

63 EXIT WHEN reg_cursor%NOTFOUND;

64 DBMS_OUTPUT.PUT_LINE

65 ('.... INDEX ' || p_schemaname || '.' || p_indexname);

66 END LOOP;

67 CLOSE reg_cursor;

69 EXCEPTION

70 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;

71 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;

72 WHEN OTHERS THEN CLOSE reg_cursor; raise;

73 END;

75 END IF;

77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';

79 EXCEPTION

80 WHEN NO_DATA_FOUND THEN NULL;

82 END;

83 /

PL/SQL procedure successfully completed.

CDB12C >

CDB12C >DROP function local_enquote_name;

Function dropped.

CDB12C >

CDB12C >Rem =====================================================================

CDB12C >Rem Run component validation procedure

CDB12C >Rem =====================================================================

CDB12C >

CDB12C >EXECUTE dbms_registry_sys.validate_components;

...Database user "SYS", database schema "APEX_040200", user# "98" 16:00:48

...Compiled 0 out of 2998 objects considered, 0 failed compilation 16:00:49

...263 packages

...255 package bodies

...453 tables

...11 functions

...16 procedures

...3 sequences

...458 triggers

...1322 indexes

...207 views

...0 libraries

...6 types

...0 type bodies

...0 operators

...0 index types

...Begin key object existence check 16:00:49

...Completed key object existence check 16:00:50

...Setting DBMS Registry 16:00:50

...Setting DBMS Registry Complete 16:00:50

...Exiting validate 16:00:50

PL/SQL procedure successfully completed.

CDB12C >SET serveroutput off

CDB12C >

CDB12C >Rem ===========================================================================

CDB12C >Rem END utlrp.sql

CDB12C >Rem ===========================================================================

CDB12C >

CDB12C >select status, count(*) from sys.obj$ where status in (5, 6) group by status order by 1;

STATUS COUNT(*)

---------- ----------

6 18

1 row selected.

CDB12C >select count(*) from sys.view$;

COUNT(*)

----------

181

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in

2 (select obj# from sys.view$);

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.procedure$;

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in

2 (select obj# from sys.procedure$);

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.dir$;

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in

2 (select obj# from sys.dir$);

COUNT(*)

----------

1 row selected.

CDB12C >

CDB12C >-- mark old version types as valid, as utlrp skips these

CDB12C >update sys.obj$ set status = 1

2 where type#=13 and subname is not null and status > 1;

18 rows updated.

CDB12C >commit;

Commit complete.

CDB12C >

CDB12C >alter pluggable database "&pdbname" close;

...

CDB12C >Rem

CDB12C >Rem If sys.enabled$index table exists, then re-enable

CDB12C >Rem list of functional indexes that were enabled prior to upgrade

CDB12C >Rem The table sys.enabled$index table is created in catupstr.sql

CDB12C >Rem

CDB12C >SET serveroutput on

CDB12C >DECLARE

2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

3 commands tab_char;

4 p_null CHAR(1);

5 p_schemaname VARCHAR2(30);

6 p_indexname VARCHAR2(30);

7 rebuild_idx_msg BOOLEAN := FALSE;

8 non_existent_index exception;

9 recycle_bin_objs exception;

10 cannot_change_obj exception;

11 no_such_table exception;

12 pragma exception_init(non_existent_index, -1418);

13 pragma exception_init(recycle_bin_objs, -38301);

14 pragma exception_init(cannot_change_obj, -30552);

15 pragma exception_init(no_such_table, -942);

16 type cursor_t IS REF CURSOR;

17 reg_cursor cursor_t;

19 BEGIN

20 -- Check for existence of the table marking disabled functional indices

22 SELECT NULL INTO p_null FROM DBA_OBJECTS

23 WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and

24 object_type = 'TABLE' and rownum <=1;

26 -- Select indices to be re-enabled

27 EXECUTE IMMEDIATE q'+

28 SELECT 'ALTER INDEX ' ||

29 local_enquote_name(e.schemaname) || '.' ||

30 local_enquote_name(e.indexname) || ' ENABLE'

31 FROM enabled$indexes e, ind$ i

32 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND

33 bitand(i.property, 16) != 0+'

34 BULK COLLECT INTO commands;

36 IF (commands.count() > 0) THEN

37 FOR i IN 1 .. commands.count() LOOP

38 BEGIN

39 EXECUTE IMMEDIATE commands(i);

40 EXCEPTION

41 WHEN NON_EXISTENT_INDEX THEN NULL;

42 WHEN RECYCLE_BIN_OBJS THEN NULL;

43 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;

44 END;

45 END LOOP;

46 END IF;

48 -- Output any indexes in the table that could not be re-enabled

49 -- due to ORA-30552 during ALTER INDEX...ENBLE command

51 IF rebuild_idx_msg THEN

52 BEGIN

53 DBMS_OUTPUT.PUT_LINE

54 ('The following indexes could not be re-enabled and may need to be rebuilt:');

56 OPEN reg_cursor FOR

57 'SELECT e.schemaname, e.indexname

58 FROM enabled$indexes e, ind$ i

59 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';

61 LOOP

62 FETCH reg_cursor INTO p_schemaname, p_indexname;

63 EXIT WHEN reg_cursor%NOTFOUND;

64 DBMS_OUTPUT.PUT_LINE

65 ('.... INDEX ' || p_schemaname || '.' || p_indexname);

66 END LOOP;

67 CLOSE reg_cursor;

69 EXCEPTION

70 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;

71 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;

72 WHEN OTHERS THEN CLOSE reg_cursor; raise;

73 END;

75 END IF;

77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';

79 EXCEPTION

80 WHEN NO_DATA_FOUND THEN NULL;

82 END;

83 /

PL/SQL procedure successfully completed.

CDB12C >

CDB12C >DROP function local_enquote_name;

Function dropped.

CDB12C >

CDB12C >Rem =====================================================================

CDB12C >Rem Run component validation procedure

CDB12C >Rem =====================================================================

CDB12C >

CDB12C >EXECUTE dbms_registry_sys.validate_components;

...Database user "SYS", database schema "APEX_040200", user# "98" 16:00:48

...Compiled 0 out of 2998 objects considered, 0 failed compilation 16:00:49

...263 packages

...255 package bodies

...453 tables

...11 functions

...16 procedures

...3 sequences

...458 triggers

...1322 indexes

...207 views

...0 libraries

...6 types

...0 type bodies

...0 operators

...0 index types

...Begin key object existence check 16:00:49

...Completed key object existence check 16:00:50

...Setting DBMS Registry 16:00:50

...Setting DBMS Registry Complete 16:00:50

...Exiting validate 16:00:50

PL/SQL procedure successfully completed.

CDB12C >SET serveroutput off

CDB12C >

CDB12C >Rem ===========================================================================

CDB12C >Rem END utlrp.sql

CDB12C >Rem ===========================================================================

CDB12C >

CDB12C >select status, count(*) from sys.obj$ where status in (5, 6) group by status order by 1;

STATUS COUNT(*)

---------- ----------

6 18

1 row selected.

CDB12C >select count(*) from sys.view$;

COUNT(*)

----------

181

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in

2 (select obj# from sys.view$);

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.procedure$;

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in

2 (select obj# from sys.procedure$);

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.dir$;

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in

2 (select obj# from sys.dir$);

COUNT(*)

----------

1 row selected.

CDB12C >

CDB12C >-- mark old version types as valid, as utlrp skips these

CDB12C >update sys.obj$ set status = 1

2 where type#=13 and subname is not null and status > 1;

18 rows updated.

CDB12C >commit;

Commit complete.

CDB12C >

CDB12C >alter pluggable database "&pdbname" close;

Pluggable database altered.

CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >alter pluggable database "&pdbname" open restricted;

Warning: PDB altered with errors.

CDB12C >ALTER SYSTEM SET "_system_trig_enabled"=FALSE SCOPE=MEMORY;

System altered.

CDB12C >

CDB12C >alter session set "_ORACLE_SCRIPT"=true;

Session altered.

CDB12C >drop view sys.cdb$tables&pdbid;

View dropped.

CDB12C >drop view sys.cdb$objects&pdbid;

View dropped.

CDB12C >drop view sys.cdb$common_root_objects&pdbid;

View dropped.

CDB12C >drop view sys.cdb$common_users&pdbid;

View dropped.

CDB12C >drop view sys.cdb$rootdeps&pdbid;

View dropped.

CDB12C >

CDB12C >alter session set container=CDB$ROOT;

Session altered.

CDB12C >drop view sys.cdb$common_root_objects&pdbid;

View dropped.

CDB12C >drop view sys.cdb$common_users&pdbid;

View dropped.

CDB12C >drop view sys.cdb$rootdeps&pdbid;

View dropped.

CDB12C >

CDB12C >alter session set container="&pdbname";

Session altered.

CDB12C >

CDB12C >-- handle Resource Manager plan conversions

CDB12C >exec dbms_rmin.rm$_noncdb_to_pdb;

PL/SQL procedure successfully completed.

CDB12C >

CDB12C >-- delete SYS$BACKGROUND and SYS$USERS from service$

CDB12C >delete from sys.service$ where name in ('SYS$BACKGROUND', 'SYS$USERS');

2 rows deleted.

CDB12C >commit;

Commit complete.

CDB12C >

CDB12C >-- reset the parameters at the end of the script

CDB12C >exec dbms_pdb.noncdb_to_pdb(2);

PL/SQL procedure successfully completed.

CDB12C >alter session set "_ORACLE_SCRIPT"=false;

Session altered.

CDB12C >alter session set "_NONCDB_TO_PDB"=false;

Session altered.

CDB12C >ALTER SYSTEM SET "_system_trig_enabled"=&sys_trig_enabled SCOPE=MEMORY;

System altered.

CDB12C >

CDB12C >alter pluggable database "&pdbname" close;

....

36 IF (commands.count() > 0) THEN

37 FOR i IN 1 .. commands.count() LOOP

38 BEGIN

39 EXECUTE IMMEDIATE commands(i);

40 EXCEPTION

41 WHEN NON_EXISTENT_INDEX THEN NULL;

42 WHEN RECYCLE_BIN_OBJS THEN NULL;

43 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;

44 END;

45 END LOOP;

46 END IF;

48 -- Output any indexes in the table that could not be re-enabled

49 -- due to ORA-30552 during ALTER INDEX...ENBLE command

51 IF rebuild_idx_msg THEN

52 BEGIN

53 DBMS_OUTPUT.PUT_LINE

54 ('The following indexes could not be re-enabled and may need to be rebuilt:');

56 OPEN reg_cursor FOR

57 'SELECT e.schemaname, e.indexname

58 FROM enabled$indexes e, ind$ i

59 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';

61 LOOP

62 FETCH reg_cursor INTO p_schemaname, p_indexname;

63 EXIT WHEN reg_cursor%NOTFOUND;

64 DBMS_OUTPUT.PUT_LINE

65 ('.... INDEX ' || p_schemaname || '.' || p_indexname);

66 END LOOP;

67 CLOSE reg_cursor;

69 EXCEPTION

70 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;

71 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;

72 WHEN OTHERS THEN CLOSE reg_cursor; raise;

73 END;

75 END IF;

77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';

79 EXCEPTION

80 WHEN NO_DATA_FOUND THEN NULL;

82 END;

83 /

PL/SQL procedure successfully completed.

CDB12C >

CDB12C >DROP function local_enquote_name;

Function dropped.

CDB12C >

CDB12C >Rem =====================================================================

CDB12C >Rem Run component validation procedure

CDB12C >Rem =====================================================================

CDB12C >

CDB12C >EXECUTE dbms_registry_sys.validate_components;

...Database user "SYS", database schema "APEX_040200", user# "98" 16:00:48

...Compiled 0 out of 2998 objects considered, 0 failed compilation 16:00:49

...263 packages

...255 package bodies

...453 tables

...11 functions

...16 procedures

...3 sequences

...458 triggers

...1322 indexes

...207 views

...0 libraries

...6 types

...0 type bodies

...0 operators

...0 index types

...Begin key object existence check 16:00:49

...Completed key object existence check 16:00:50

...Setting DBMS Registry 16:00:50

...Setting DBMS Registry Complete 16:00:50

...Exiting validate 16:00:50

PL/SQL procedure successfully completed.

CDB12C >SET serveroutput off

CDB12C >

CDB12C >Rem ===========================================================================

CDB12C >Rem END utlrp.sql

CDB12C >Rem ===========================================================================

CDB12C >

CDB12C >select status, count(*) from sys.obj$ where status in (5, 6) group by status order by 1;

STATUS COUNT(*)

---------- ----------

6 18

1 row selected.

CDB12C >select count(*) from sys.view$;

COUNT(*)

----------

181

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in

2 (select obj# from sys.view$);

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.procedure$;

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in

2 (select obj# from sys.procedure$);

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.dir$;

COUNT(*)

----------

1 row selected.

CDB12C >select count(*) from sys.obj$ where bitand(flags, 65536)=65536 and obj# in

2 (select obj# from sys.dir$);

COUNT(*)

----------

1 row selected.

CDB12C >

CDB12C >-- mark old version types as valid, as utlrp skips these

CDB12C >update sys.obj$ set status = 1

2 where type#=13 and subname is not null and status > 1;

18 rows updated.

CDB12C >commit;

Commit complete.

CDB12C >

CDB12C >alter pluggable database "&pdbname" close;

Pluggable database altered.

CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >alter pluggable database "&pdbname" open restricted;

Warning: PDB altered with errors.

CDB12C >ALTER SYSTEM SET "_system_trig_enabled"=FALSE SCOPE=MEMORY;

System altered.

CDB12C >

CDB12C >alter session set "_ORACLE_SCRIPT"=true;

Session altered.

CDB12C >drop view sys.cdb$tables&pdbid;

View dropped.

CDB12C >drop view sys.cdb$objects&pdbid;

View dropped.

CDB12C >drop view sys.cdb$common_root_objects&pdbid;

View dropped.

CDB12C >drop view sys.cdb$common_users&pdbid;

View dropped.

CDB12C >drop view sys.cdb$rootdeps&pdbid;

View dropped.

CDB12C >

CDB12C >alter session set container=CDB$ROOT;

Session altered.

CDB12C >drop view sys.cdb$common_root_objects&pdbid;

View dropped.

CDB12C >drop view sys.cdb$common_users&pdbid;

View dropped.

CDB12C >drop view sys.cdb$rootdeps&pdbid;

View dropped.

CDB12C >

CDB12C >alter session set container="&pdbname";

Session altered.

CDB12C >

CDB12C >-- handle Resource Manager plan conversions

CDB12C >exec dbms_rmin.rm$_noncdb_to_pdb;

PL/SQL procedure successfully completed.

CDB12C >

CDB12C >-- delete SYS$BACKGROUND and SYS$USERS from service$

CDB12C >delete from sys.service$ where name in ('SYS$BACKGROUND', 'SYS$USERS');

2 rows deleted.

CDB12C >commit;

Commit complete.

CDB12C >

CDB12C >-- reset the parameters at the end of the script

CDB12C >exec dbms_pdb.noncdb_to_pdb(2);

PL/SQL procedure successfully completed.

CDB12C >alter session set "_ORACLE_SCRIPT"=false;

Session altered.

CDB12C >alter session set "_NONCDB_TO_PDB"=false;

Session altered.

CDB12C >ALTER SYSTEM SET "_system_trig_enabled"=&sys_trig_enabled SCOPE=MEMORY;

System altered.

CDB12C >

CDB12C >alter pluggable database "&pdbname" close;

Pluggable database altered.

CDB12C >alter session set container = CDB$ROOT;

Session altered.

CDB12C >alter system flush shared_pool;

System altered.

CDB12C >/

System altered.

CDB12C >/

System altered.

CDB12C >alter session set container = "&pdbname";

Session altered.

CDB12C >

CDB12C >-- leave the PDB in the same state it was when we started

CDB12C >BEGIN

2 execute immediate '&open_sql &restricted_state';

3 EXCEPTION

4 WHEN OTHERS THEN

5 BEGIN

6 IF (sqlcode <> -900) THEN

7 RAISE;

8 END IF;

9 END;

10 END;

11 /

PL/SQL procedure successfully completed.

CDB12C >

CDB12C >WHENEVER SQLERROR CONTINUE;

CDB12C >

Check the Status of PDB and open it

DBAHUB

Sunday, September 6, 2015

Plugging 12c NON-CDB to 12c CDB

LOG noncdb_to_pdb.sql

No comments:

Post a Comment

Followers