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.
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.
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.