pdb_to_apppdb.sql returns ORA-65021- A workaround

While exploring Oracle Multitenant Application Containers, I learnt that in order to convert a regular PDB  to an application PDB

  • Clone a regular PDB  into an application root
  • Connect to the cloned  PDB and execute the $ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql to convert the cloned regular PDB  to an application PDB

However, when I connected to cloned PDB remotely using @… and executed the script pdb_to_apppdb.sql, I got ORA-65021 :

SQL>@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb

 

 

.

.

.

.

 

SQL>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, &sharing_bits),

5                &edl+&mdl, 'EDL', &dl, 'DL', 'MDL') sharing

6    from sys.obj$ o, sys.user$ u

7   where o.owner#=u.user# and bitand(o.flags, &sharing_bits) <> 0

8     and bitand(o.flags,&fedobjflag)=&fedobjflag;

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

old   4:        decode(bitand(o.flags, &sharing_bits),

new   4:        decode(bitand(o.flags, (65536+131072+4294967296)),

old   5:               &edl+&mdl, 'EDL', &dl, 'DL', 'MDL') sharing

new   5:               4294967296+65536, 'EDL', 131072, 'DL', 'MDL') sharing

old   7:  where o.owner#=u.user# and bitand(o.flags, &sharing_bits) <> 0

new   7:  where o.owner#=u.user# and bitand(o.flags, (65536+131072+4294967296)) <> 0

old   8:    and bitand(o.flags,&fedobjflag)=&fedobjflag

new   8:    and bitand(o.flags,134217728)=134217728

create or replace view sys.cdb$common_root_objects4 sharing=object as

*

ERROR at line 1:

ORA-65021: illegal use of SHARING clause

After various repeated trials, I realized that connecting to cloned PDB using “Alter session set container ..“ results in successful execution.

Here is the full article where I encountered this error and found out the workaround.

I faced similar issue while creating application seed from application root and resolved it by connecting to the  application seed  created from application root using Alter session set container …

Conclusion:  In order to execute the script $ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql to convert the cloned regular PDB  to an application PDB, connect to the target regular PDB by switching the container. (Do not connect remotely using @)

References:

Your comments and suggestions are welcome!