Be aware when you use _ORACLE_SCRIPT in scripts

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