Recently I came across this when I gave a Multitenant workshop at a German customer. As part of the workshop I explained the _ORACLE_SCRIPT
statements you will find in almost all scripts in ?/rdbms/admin
. And one of the DBAs gave me a bright smile. He mentioned: “We use it already everywhere to avoid strange errors”. But be aware when you use _ORACLE_SCRIPT
in scripts. I will explain, why this can be dangerous.

Photo by Justin Chrn on Unsplash
What does _ORACLE_SCRIPT do?
First of all, this is an underscore parameter. Hence, you won’t find it in the Oracle Documentation. Underscores usually don’t get explained in the documentation. Even though this would make sense sometimes, for instance for the problematic _EXCLUDE_SEED_CDB_VIEW
parameter, you won’t find them there.
You may get an idea about what _ORACLE_SCRIPT
does when you scroll down to Workaround 1 in this blog post about Time Zone patches in CDBs.
But what does MOS say:
- MOS Note:2378735.1 – “_ORACLE_SCRIPT”=TRUE PARAMETER Should not be Invoked by Users
- Per Development, setting _ORACLE_SCRIPT to TRUE is something to be used only by Oracle internally. There may be a case where a note says to use it explicitly, but you should not use it outside the context of that note. In general is not to be set explicitly by users.
- MOS Note:1566042.1 – ORA-28014: Cannot Drop Administrative Users
Basically, when you read through the notes mentioning or using _ORACLE_SCRIPT
, you may get the impression that it gets used quite frequently – but is not explained anywhere.
And some notes describe clearly how you can open the PDB$SEED in read/write mode to adjust certain things. The _ORACLE_SCRIPT
parameter is necessary to do so.
Why you shouldn’t use _ORACLE_SCRIPT
Back to the smiling DBA. What is the danger when you put alter session set "_ORACLE_SCRIPT"=TRUE
in your scripts? Let me do a quick test.
I execute a very simple script in my PDB:
alter session set "_ORACLE_SCRIPT"=TRUE; drop table hugo.test01 purge; create table hugo.test01(col1 number);
This works fine of course. And I don’t even alter anything in the protected PDB$SEED.
But when you check DBA_OBJECTS
you may recognize one important flag set:
SQL> select object_name, object_type, ORACLE_MAINTAINED from dba_objects where object_name='TEST01'
OBJECT_NAME OBJECT_TYPE ORACLE_MAINTAINED
------------- ----------------------- -------------------
TEST01 TABLE Y
Ok – this shouldn’t happen. We use the ORACLE_MAINTAINED
flag in many different occasions. The upgrade checks use this flag quite often, too. We encountered an issue with the DMU a while ago because it didn’t protect the ORACLE_MAINTAINED
flag, and an upgrade check gave a false alarm.
Summary
There may be rare occasions where you want to use _ORACLE_SCRIPT
. You can read the official statement from Development mentioned above. Hence, please be careful – and don’t use alter session set "_oracle_script"=TRUE;
everywhere in your own scripts. As soon as you do DDLs, the objects will have an ORACLE_MAINTAINED=Y
flag. And this potentially leads to issues during your next upgrade.
–Mike
It is not just upgrades. Data Pump does not export tables where ORACLE_MAINTAINED was set to Y.
True – thanks for adding this important fact!
Cheers,
Mike
Hi Mike, Nice Post as usual.
As mentioned Balazs Datapump or traditional exp works. Now Is there a way to update that columns. I’m in a situation where the developer thinks ok with that parameter and now he wants export his schema. but as you can image nothing got’s imported.
Any advice.
Thanks in advance
Hi William,
uhhhh …
You can try to identify user supplied roles, privs and objects with these here:
https://mikedietrichde.com/2018/03/15/upuserxt-lst-lists-oracle-supplied-user-schemas/
https://mikedietrichde.com/2018/03/14/upobjxt-lst-lists-oracle-supplied-objects-and-users/
But basically, you would need to patch it in the dictionary.
Best would be to check with Oracle Support whether they have a recommended solution.
Thanks,
Mike
Thanks Mike – Very useful
welcome 🙂
Mike
Hi,
I have trouble with installing APEX 21.2. I need to delete APEX_210200 schema in pdb but I couldn’t. Th solution was to issue a statement:
alter session set “_ORACLE_SCRIPT”=TRUE;
After that the schema was successfully deleted.
Hi Stanisa,
I’m not familiar with the APEX deletion process – but usually to drop such things, the env variable is necessary.
Cheers
Mike