You may have seen my two blog posts about Oracle supplied objects and Oracle supplied user schema and roles a few days ago, One person commented asking about what to do with these. And I agree: It looks a bit harsh to deal with two external files to query Oracle supplied stuff. But actually (as Patrick Hurley commented as well) there is a much easier way since Oracle Database 12c to deal with objects created and supplied by Oracle: the ORACLE_MAINTAINED attribute can be pretty helpful.
ORACLE_MAINTAINED attribute can be pretty helpful
In Oracle Database 12.1.0.1 we added a column to several dictionary views: ORACLE_MAINTAINED. And of course others have blogged about it already a while ago:
- https://martincarstenbach.wordpress.com/2013/07/17/dba_users-oracle_maintained-in-12c/
- https://www.markusdba.net/?p=185
These tables have this helpful ORACLE_MAINTAINED column attribute in common. We mark objects and schemas and roles and users maintained by Oracle with it. You find a full list of dictionary views containing the column below:
CDB_OBJECTSDBA_OBJECTSALL_OBJECTSUSER_OBJECTSCDB_INVALID_OBJECTSDBA_INVALID_OBJECTSALL_PROBE_OBJECTSCDB_USERSDBA_USERSALL_USERSUSER_USERSCDB_ROLESDBA_ROLESCDB_OBJECTS_AE (for EBR)DBA_OBJECTS_AE (for EBR)ALL_OBJECTS_AE (for EBR)USER_OBJECTS_AE (for EBR)
At the moment we use this ORACLE_MAINTAINED column in a transportable tablespace project. With ORACLE_MAINTAINED we determine whether we want to gather the object definition with DBMS_METADATA.GET_DDL or not. If yes, we generate the creation scripts.
Things to be aware of
Please be aware that for instance a Data Pump export may not export certain objects – those who are marked as ORACLE_MAINTAINED:
In addition Data Pump ignores objects in KU_NOEXP_VIEW which is a UNION of KU_NOEXP_TAB and NOEXP$.
Be aware (I received this question just a week ago) that APEX is classified as ORACLE_MAINTAINED as well. In case you’ll do an expdp/impdp migration you’ll have to migrate your APEX applications with the APEX interface.
–Mike