Compiling a Public Synonym in a Pluggable Database (PDB) fails with ORA-65047...

刘泽宇

现象：
Trying to compile a public synonym in pluggable database (PDB):

alter public synonym <PACKAGE_NAME> compile;
fails in SQL*Plus with:

ORA-65047: Object PUBLIC.<PACKAGE_NAME> is invalid or compiled with errors
And the following error occurs in the alert log:

ORA-07445: exception encountered: core dump [csyalt()+608] [SIGSEGV] [ADDR:0x0] [PC:0x41BCC70] [Address not mapped to object] []


The Call Stack Trace in the associated incident trace file shows:

csyalt <- opiexe <- opiosq0 <- kpooprx <- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr
<- opidrv <- sou2o <- opimai_real <- ssthrdmain <- main


This can also occur when applying a datapatch.

Datapatch fails with error:
Patch XXX apply (pdb PDBWITH ERRORS
logfile: <log file locatoin>/<patch>_apply_<DB>_<PDB>_<timestamp>.log (errors)
Error at line 24145: ORA-65047: Object PUBLIC.APEX_PKG_APP_INSTALL_LOG is invalid or compiled with errors in root.
ORA-06512: at "SYS.DBMS_UTILITY", line 1314
ORA-06512: at line 8
ORA-06512: at line 8


原因：
The normal:

     ALTER <OBJECT_TYPE> <OBJECT_NAME> COMPILE;

command will not work on a PDB.

This issue is not caused by a product defect.



处理方法：
Use the DBMS_PDB.EXEC_AS_ORACLE_SCRIPT API to resolve this issue.  This procedure enables execution of certain restricted statements (most DDLs) on metadata-linked objects from within a PDB.

For example:

SQL> exec dbms_pdb.exec_as_oracle_script('CREATE OR REPLACE PUBLIC SYNONYM <PACKAGE_NAME> FOR SYS.<PACKAGE_NAME>');
PL/SQL procedure successfully completed.

SQL> select owner, object_name, object_type, status from dba_objects where object_name = '<PACKAGE_NAME>';

OWNER           OBJECT_NAME                           OBJECT_TYPE    STATUS
--------------- ------------------------------------- -------------- ----------
PUBLIC          <PACKAGE_NAME>                        SYNONYM        VALID
<OWNER>         <PACKAGE_NAME>                        VIEW           VALID