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 22.214.171.124 we added a column to several dictionary views:
ORACLE_MAINTAINED. And of course others have blogged about it already a while ago:
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_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
- MOS Note:2114233.1 – Why Can an Object Not Be Exported? Expdp of SYSTEM User’s Table Returns ORA-39166 or ORA-31655
In addition Data Pump ignores objects in
KU_NOEXP_VIEW which is a UNION of
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.