Lab

How to compile system objects in the PDB (12c)

This is an example based on Oracle 12c 12.1.0.2 with PSU July 2015 Single PDB configuration.
I found last week some objects in my PDBs related to the auditing that were not compiled and in INVALID state.

OBJECT_TYPE OWNER OBJECT_NAME
-------------- ------- -----------
VIEW SYS DBA_FGA_AUDIT_TRAIL 
VIEW SYS DBA_COMMON_AUDIT_TRAIL 
VIEW SYS FGA_LOG$FOR_EXPORT 
PUBLIC SYNONYM DBA_FGA_AUDIT_TRAIL 
PUBLIC SYNONYM DBA_COMMON_AUDIT_TRAIL

To my surprise there had been no INVALID objects like these in the CDB$ROOT container. I thought they are common for CDB and all the PDBs, it looks they are not entirely shared, some part of the information is being kept in the PDBs.

I decided to switch back to the PDB and compile the objects there and here another surprise:

SQL> alter view DBA_FGA_AUDIT_TRAIL compile;
ERROR at line 1: 
ORA-65040: operation not allowed from within a pluggable database

What to do, what to do? :). There is a way out. Notice the below undocumented procedure in DBMS_PDB package:

SQL> exec dbms_pdb.exec_as_oracle_script('alter view DBA_FGA_AUDIT_TRAIL compile;');

It did the magic and compiled successfully that view and other objects without any errors. Everything now is in VALID state in the PDBs and CDBs.

Even though this procedure hasn’t been documented, I was told that we can use it anytime for uncompiled objects in the PDB – without raising an SR each time.

It is possible for the procedure to be included in the next documentation update.


fb-profile-5545-4989030_50x50
    Maciej Tokar

 

 

Category: Lab
Posts created 2

Related Posts

Begin typing your search term above and press enter to search. Press ESC to cancel.

Back To Top