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
This behaviour is great and all for the regular use case where you want to protect people from harming themselves. But in the case that you know what you are doing this is severly limiting your options … there should be a least a switch to choose otherwise.
Dietmar,
not sure what you concern is here.
Actually we are using the attribute as well, e.g. during the preupgrade.jar checks.
Thanks,
Mike