Alert: oracle 12\18\19\20c 不要滥用“_ORACLE_SCRIPT”=true

_ORACLE_SCRIPT”参数首先是个隐藏参数，所以很少有文档中描述他打开了哪些开关，因为它是oracle内部维护时使用，在ORACLE_HOME下的脚本中不少都有alter session set “_oracle_script”=true的SQL， 但是注意执行完后及时的再改回false. 千万不要为了突破oracle的默认限制而随意使用_oracle_script参数，生产库除了oracle要求更不建议修改，因为后期有可能会遇到不些不必要的麻烦。

修改了”_oracle_script”有可能在后面升级时因为破坏了oracle的内部约束而升级失败，或后面在业务数据导出(datapump)时而数据丢失。

因为在set “_oracle_script”=true后，创建的用户，用户属性oracle_maintained会标记为Y。我在之前blog 《Oracle 12c新特性：ORACLE自动维护的Schema或默认创建的USER》中有记录过oracle_maintained是标记是否为ORACLE 内部SCHEMA。 同时如果”_oracle_script”=true，那创建的对象oracle_maintained属性同样为Y, 这样在使用数据泵expdp导出时，认为是系统对象而不再导出，导致数据丢失。

demo
版本oracle 19.2

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create user anbob identified by oracle;
--fail show ORA-65096

SQL> ho oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause:  An attempt was made to create a common user or role with a name
//          that was not valid for common users or roles. In addition to the
//          usual rules for user and role names, common user and role names
//          must consist only of ASCII characters, and must contain the prefix
//          specified in common_user_prefix parameter.
// *Action: Specify a valid common user or role name.
//


SQL> show parameter common_user
PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
common_user_prefix                                           string      C##
SQL> alter session set "_oracle_script"=true;
Session altered.

SQL> create user anbob identified by oracle;
User created.

SQL> alter session set "_oracle_script"=false;
Session altered.
 
SQL> col username for a10
SQL> select username,account_status,oracle_maintained from dba_users where username='ANBOB';
USERNAME   ACCOUNT_STATUS                   O
---------- -------------------------------- -
ANBOB      OPEN                             Y

Note:
突破cdb中对于common user要求以common_user_prefix 开头的限制，但是用户oracle_maintained =Y.

SQL> alter user anbob quota unlimited on users;
User altered.

SQL> create table anbob.test as select 1 id from dual;
Table created.
 
SQL> COL OBJECT_NAME FOR A30
SQL> select object_name,object_type,ORACLE_MAINTAINED from dba_objects where owner='ANBOB';

OBJECT_NAME                    OBJECT_TYPE             O
------------------------------ ----------------------- -
TEST                           TABLE                   N

SQL> alter session set "_oracle_script"=true;
Session altered.

SQL> create table anbob.test1 as select 1 id from dual;
Table created.

SQL> alter session set "_oracle_script"=false;
Session altered.

SQL> select object_name,object_type,ORACLE_MAINTAINED from dba_objects where owner='ANBOB';

OBJECT_NAME                    OBJECT_TYPE             O
------------------------------ ----------------------- -
TEST                           TABLE                   N
TEST1                          TABLE                   Y

note:
上面在cdb中创建的common user anbob 创建的table默认ORACLE_MAINTAINED =N，但是在 “_oracle_script”=true后创建的表ORACLE_MAINTAINED =Y， 另外测试在”_oracle_script”=true下，alter table add column 不会修改ORACLE_MAINTAINED值。

[oracle@anbob19 admin]$ exp anbob/oracle file=anbob.dmp

Export: Release 19.0.0.0.0 - Production on Sat Mar 7 13:34:27 2020
Version 19.2.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
Version 19.2.0.0.0
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ANBOB
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ANBOB
About to export ANBOB's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ANBOB's tables via Conventional Path ...
. . exporting table                           TEST          1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                          TEST1          1 rows exported
EXP-00091: Exporting questionable statistics.
...


[oracle@anbob19 admin]$ expdp \'\/ as sysdba\' directory=oracle_base schemas=anbob dumpfile=anbob.dump

Export: Release 19.0.0.0.0 - Production on Sat Mar 7 13:39:38 2020
Version 19.2.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=oracle_base schemas=anbob dumpfile=anbob.dump
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "ANBOB"."TEST"                              5.046 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/anbob.dump
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Mar 7 13:42:13 2020 elapsed 0 00:02:15

NOTE:
exp 可以导出common user的所有表， expdp未导到ORACLE_MAINTAINED =Y的表。

SQL> drop user anbob cascade;
drop user anbob cascade
*
ERROR at line 1:
ORA-28014: cannot drop administrative user or role

SQL> ho oerr ora 28014
28014, 00000, "cannot drop administrative user or role"
// *Cause:    An attempt was made to drop an administrative user or role.
//            An administrative user or role can be dropped only by SYS during
//            migration mode.
// *Action:   Drop the administrative user or role during migration mode.
//

SQL> alter session set "_oracle_script"=true;
Session altered.

SQL> drop user anbob cascade;
User dropped.

SQL> alter session set "_oracle_script"=false;
Session altered.

Note:
对于CDB用户下创建的common user删除是提示ORA-28014， 需要set “_oracle_script”=true删除。

–enjoy –