Hi all,
Hope you’re doing well.
The Question
Today, a question came up on our internal distribution list:
That’s a good one, isn’t it?
Why does this happens?
Well, let’s first understand why this happens.
In the Oracle Database, there is a “super power special” mode when it’s performing changes on internal tables and dictionary views. This is performed by running the following statement:
alter session set "_ORACLE_SCRIPT"=TRUE;
Why you should avoid using it?
The usage of _ORACLE_SCRIPT must be done only by Oracle… Scripts. 🙂 Well, this is true, you some times can be asked to do it when explicitly stated in My Oracle Support Notes.
If you create objects when setting _ORACLE_SCRIPT=TRUE, you will be unable to export them, for example, as stated in the My Oracle Support Note: 2114233.1 – Why Can an Object Not Be Exported? Expdp of SYSTEM User’s Table Returns ORA-39166 or ORA-31655
It’s not just about exports – other operations can also be affected. For example, you might be unable to drop the user, as documented in My Oracle Support Note: 1566042.1 – ORA-28014: Cannot Drop Administrative Users
The Official solution: Rebuild the entire schema
I was told that client opened a Service Request and Support mentioned that the only supported way to fix that is by rebuilding the entire schema, which can be painful – and in this case, it’s specially painful given the size and criticality of the environment.
Building Our Lab
Let’s build our lab to show how this works.
First, let’s create our user after we set _ORACLE_SCRIPT=TRUE:
SQL> alter session set "_ORACLE_SCRIPT"=TRUE; Session altered. SQL> create user test identified by test; User created. SQL> grant create session,resource,create any directory to test; Grant succeeded.
OK, now, let’s connect with the user and create some objects here without setting _ORACLE_SCRIPT=TRUE:
SQL> conn test/test SQL> create directory test as '/u01'; Directory created. SQL> create table x (cod number, name varchar2(100)); Table created. SQL> create index x1 on x(cod); Index created.
Alright, now, let’s connect with the user and create some objects here setting _ORACLE_SCRIPT=TRUE:
SQL> conn test/test SQL> create table t (cod number, name varchar2(100)); Table created. SQL> create index t1 on t(cod); Index created.
Perfect! Let’s now check our user and the objects, pay attention to the highlighted lines below:
SQL> conn / as sysdba SQL> select username,oracle_maintained from dba_users where username='TEST'; USERNAME O ------------------------------ - TEST Y SQL> select owner,object_name,object_type,oracle_maintained from dba_objects where owner='TEST'; OWNER OBJECT_NAME OBJECT_TYPE O ---------- --------------- ------------------- - TEST T TABLE Y TEST X TABLE N TEST X1 INDEX N TEST T1 INDEX Y
Well, as you can see, we have the column ORACLE_MAINTAINED=Y for the user and for the objects we created under _ORACLE_SCRIPT=TRUE mode.
Let’s try to export the tables:
expdp test/test directory=test tables=x,t dumpfile=exp_test.dmp logfile=exp_test.log Export: Release 19.0.0.0.0 - Production on Wed Jul 2 17:29:22 2025 Version 19.22.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=test tables=x,t dumpfile=exp_test.dmp logfile=exp_test.log Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX . . exported "TEST"."X" 0 KB 0 rows ORA-39166: Object TEST.T was not found or could not be exported or imported. Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TEST.SYS_EXPORT_TABLE_01 is: /u01/exp_test.dmp Job "TEST"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Wed Jul 2 17:29:27 2025 elapsed 0 00:00:04
As you can see, we cannot export the table we created under _ORACLE_SCRIPT=TRUE mode.
Understanding the “Internals”
When we query the views DBA_USERS and DBA_OBJECTS, we have the column ORACLE_MAINTAINED, as explained above.
The view DBA_USERS is based on tables/views: SYS.USER$, SYS.RESOURCE_GROUP_MAPPING$, SYS.TS$, SYS.PROFNAME$, SYS.USER_ASTATUS_MAP, and SYS.PROFILE$. The column on view which identifies ORACLE_MAINTAINED is this:
decode(bitand(u.spare1, 256), 256, 'Y', 'N')
Perfect. Now, let’s run another query, directly on SYS.USER$. I will include in my query another user, but this one created without _ORACLE_SCRIPT=TRUE mode:
SQL> select u.user#,u.name,u.spare1, decode(bitand(u.spare1, 256), 256, 'Y', 'N')
from sys.user$ u
where u.name in ('MARCUS','TEST');
USER# NAME SPARE1 D
---------- ---------- ---------- -
111 MARCUS 0 N
112 TEST 384 YWe can see that the column SPARE1 has the value of 384 for the user we created under _ORACLE_SCRIPT=TRUE mode.
The view DBA_OBJECTS is based on tables/views: SYS.OBJ$, SYS.USER$,SYS.IND$,SYS.TAB$,SYS.SEQ$,SYS.LINK$,SYS.SUM$,SYS._CURRENT_EDITION_OBJ. The column on view which identifies ORACLE_MAINTAINED is this:
decode(bitand(o.flags, 4194304), 4194304, 'Y', 'N')
Now, let’s run another query, directly on SYS.OBJ$. I will include both the objects, created with _ORACLE_SCRIPT=TRUE and without _ORACLE_SCRIPT=TRUE:
SQL> select o.owner#,o.name,o.obj#,o.flags, decode(bitand(o.flags, 4194304), 4194304, 'Y', 'N')
from sys.obj$ o
where o.name in ('T','X', 'T1','X1') and o.owner# = 112;
OWNER# NAME OBJ# FLAGS D
---------- --------------- ---------- ---------- -
112 T 77506 4194304 Y
112 T1 77509 4194304 Y
112 X 77507 0 N
112 X1 77508 0 N
We can see that the column FLAGS has the value of 4194304 for the objects created under _ORACLE_SCRIPT=TRUE mode.
The Unofficial Solution: Updating the Internal Tables*:
Well, there is a faster solution – don’t let Oracle see this 🙂
You can update the internal tables directly, “fixing it” the ORACLE_MAINTAINED column.
First, I will fix all the objects that the schema owns and are with the “wrong” flag:
SQL> update sys.obj$ o set o.flags=0 where o.flags=4194304 and o.owner#=112; 2 rows updated. commit;
Running the query again to check:
SQL> select o.owner#,o.name,o.obj#,o.flags, decode(bitand(o.flags, 4194304), 4194304, 'Y', 'N')
from sys.obj$ o
where o.name in ('T','X', 'T1','X1') and o.owner# = 112;
OWNER# NAME OBJ# FLAGS D
---------- --------------- ---------- ---------- -
112 T 77506 0 N
112 T1 77509 0 N
112 X 77507 0 N
112 X1 77508 0 N
Great!
Now, let’s fix the user itself:
SQL> update sys.user$ set spare1=0 where user#=112; 1 row updated. commit;
Let’s check again:
SQL> select u.user#,u.name,u.spare1, decode(bitand(u.spare1, 256), 256, 'Y', 'N')
from sys.user$ u
where u.name in ('MARCUS','TEST');
USER# NAME SPARE1 D
---------- ---------- ---------- -
111 MARCUS 0 N
112 TEST 0 NGreat!
Let’s try to export the tables again:
expdp test/test directory=test tables=x,t dumpfile=exp_test.dmp logfile=exp_test.log Export: Release 19.0.0.0.0 - Production on Wed Jul 2 19:25:05 2025 Version 19.22.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** directory=test tables=x,t dumpfile=exp_test.dmp logfile=exp_test.log Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX . . exported "TEST"."T" 0 KB 0 rows . . exported "TEST"."X" 0 KB 0 rows Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for TEST.SYS_EXPORT_TABLE_01 is: /u01/exp_test.dmp Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jul 2 19:25:08 2025 elapsed 0 00:00:03
Great! Now we can export!
*Please be advised that if you look for Oracle Support’s approval on this approach, you will not get it.
This involves directly updating Oracle’s data dictionary tables (SYS.USER$ and SYS.OBJ$), which is not supported by Oracle and can lead to unexpected issues.
While I do not believe this is a harmful change when done carefully, you should proceed entirely at your own risk.
I strongly recommend performing this under a controlled maintenance window, with the application stopped and no sessions connected/using to/the target schema.
Note that you can also have ROLES with ORACLE_MAINTAINED=Y, which the solution is not covered on this blog post.
Always ensure you have a full database backup or guaranteed restore point before making any such modifications.
Conclusion:
- Avoid using _ORACLE_SCRIPT=TRUE. Only use it when explicitly requested by Oracle Support.
- You can’t export objects flagged as ORACLE_MAINTAINED=Y.
- You can’t drop users flagged as ORACLE_MAINTAINED=Y.
- The supported way to fix is to rebuild your schema.
- The unsupported way to fix is to update the internal tables.
- If you choose the unsupported way, do it entirely at your own risk.
- Always perform it during a maintenance window and ensure no one is connected to the schema.
- Even though unsupported, in my experience this approach has not been harmful.
- These opinions are my own and do not necessarily represent the views of my employer, Accenture.
Hope it helps.
Peace.
Vinicius
References:
- Mike Dietrich’s blog post: ORACLE_MAINTAINED attribute can be pretty helpful
- Mike Dietrich’s blog post: Be aware when you use _ORACLE_SCRIPT in scripts
- My Oracle Support Note: 2114233.1 – Why Can an Object Not Be Exported? Expdp of SYSTEM User’s Table Returns ORA-39166 or ORA-31655
- My Oracle Support Note: 1566042.1 – ORA-28014: Cannot Drop Administrative Users
