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
hi Mike i got a db with apex users set ORACLE_MAINTAINED = ‘N’, and i want to change it to ‘Y’.
Is it possible to change the value of ORACLE_MAINTAINED for apex users? I have not found documentation about it for days.
Hi Eliana,
why do you want to change the APEX user to ORACLE_MAINTAINED?
You should never manipulate the dictionary by yourself.
Can you tell me a bit more about your motivation please?
Cheers,
Mike
Hi Mike, I haven’t changed it. I am given a database in this state, with the apex users with the column value oracle_maintained = ‘N’.
The db is migrated from 11g to 18.c.
All the installations we have of Apex 19.2 on db 18.3, are installed with that column in ‘Y’ for these users. Default.
Looking for a reason why objects in the apex schemas are invalidated, I find that these users have been manipulated.
As a DBA, making apex have oracle_maintained = ‘Y’ as Oracle does by default, resolves no one to manipulate these users again.
I did not change them, someone did.
I try to reassure it by assigning ‘Y’.
Perhaps the only alternative is to remove apex and reinstall to get it right.
Is there another way to set the default value back to those users?
thanks for your answer
Hi Eliana,
I guess the only supported way – as you proposed – would be to remove APEX, and reinstall it afterwards again.
Make sure you export applications and images if there are any inside as they’d be lost when you remove APEX.
Cheers,
Mike
We did that, seeking to normalize and protect the environment. Thanks Mike for your response and your publications that contribute so much to us. Greetings!
Hi Mike,
in my company someone has wrongly created “normal users” as Oracle-managed users using
alter session set “_ORACLE_SCRIPT”=true;
Do you know I change this attribute for those users? (I want ORACLE_MAINTAINED = N instead of Y)
Thanks!
Hi Lor,
please check with Support on this.
https://mikedietrichde.com/2020/02/10/be-aware-when-you-use-_oracle_script-in-scripts/
This can lead to serious issues with the next upgrade. And I personally don’t know exactly which dictionary tables you need to update to revert this.
Mike