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: