Even with all my good intentions, work and life challenges got in the way, and I have not been able to blog this year yet.
I still wanted to take part in the #JoelKallmanDay to honor Joel’s memory and to show my gratitude to the Oracle community.
In my (not so) new job at Oracle, I specialize in database migrations in the Cloud, with all sorts of databases and various migration strategies.
A quite common strategy is to use the powerful Oracle Data Pump utility. This blog post will not be about Data Pump, but rather about a very specific column in the dictionary views that I love very much, and that I use every minute of each of my work days (ok, I may be exaggerating a little): ORACLE_MAINTAINED

This column was introduced in Oracle Database 12c. Its value can be ‘Y’ or ‘N’ and is present in several dictionary views, including DBA_USERS and DBA_OBJECTS.

DBA_USERS

Denotes whether the user was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql). A user for which this column has the value Y must not be changed in any way except by running an Oracle-supplied script.

DBA_OBJECTS

Denotes whether the object was created, and is maintained, by Oracle-supplied scripts (such as catalog.sql or catproc.sql). An object for which this column has the value Y must not be changed in any way except by running an Oracle-supplied script.

It makes it very easy to identify default Oracle-supplied objects and distinguish which users or objects belong to your custom schemas. Indeed, it is extremely useful when you want to migrate a database using Data Pump, and be sure to only export the right schemas along with the right objects.
But also, it becomes even more useful when you face special cases, for example, if someone has created objects on SYS schema! And even worse, if these objects are absolutely necessary for the application to work properly … But how can you identify such objects on SYS schema, among all default and legitimate SYS objects?
You can take advantage of the following query:

select object_name, object_type from dba_objects where owner='SYS' and oracle_maintained='N';

Note that some objects marked with oracle_maintained='N' are not necessarily considered as “non-default” (for example WRH$_ or AQ$_ objects) but still, using this column will drastically decrease the number of objects that you’ll have to browse through.

Thanks to column ORACLE_MAINTAINED, it is much easier to identify those problematic objects … and it is also time to strongly recommend to your colleagues or customers to avoid creating anything in SYS schema, as stated in the official documentation: “no one should create any tables in the schema of user SYS.” 🙂