This is an example based on Oracle 12c 220.127.116.11 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:
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:
EXEC 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.
About the author
An Oracle technology geek and crazy long distance runner, DBA24 Owner
Senior Oracle DBA / Consultant / [OCP10g, OCP12c, OCE RAC 10g] / [experience: 9y+]
Currently working for Bluegarden (Oslo Norway) by Miratech Group
Past: Mastercard / Trevica by Britenet, Citi International PLC, PZU