Non standard aproach to STANDARD

Last weekend, during a routine migration from a non-CDB Oracle database to a PDB, together with Kamil Stawiarski, we stumbled upon a charming little Oracle behaviour worth sharing.

The migration seemed straightforward at first. However, a subtle issue emerged related to how Oracle handles certain object names during the transition — specifically, a table named STANDARD. A very standard choice for a table name, one might say. After migrating to the PDB… well, the table was no longer interested in participating:

SQL> SELECT * from TEST.STANDARD;
select * from test.standard
                   *
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of TEST.STANDARD
ORA-65040: operation not allowed from within a pluggable database

and after checking table status:

SQL> select status
  from dba_objects
  where object_name='STANDARD'
  and   owner='TEST'
  and   object_type='TABLE';

STATUS
-------
INVALID

This behaviour can be reproduced quite easily with nonCDB in which some table exists, named: STANDARD.

SQL> create table test.standard(a number, b varchar2(30));
SQL> insert into test.standard values (1, 'a');
SQL> commit;

When creating a PDB database from such nonCDB database using for example DB Link, you will hit that unexpected collateral damage after running noncdb_to_pdb.sql script.

SQL> create database link clone_test connect to test identified by test using 'localhost:1521/orcl';
SQL> create pluggable database PDB from NONCDB@clone_test;
SQL> alter pluggable database PDB open;
SQL> alter session set container=PDB;
SQL> @?/rdbms/admin/noncdb_to_pdb

Following these steps results in the table becoming invalid within the new PDB environment.

Why?

During the migration, the script noncdb_to_pdb.sql contains a section which runs another helper script loc_to_common3.sql:

-- pass in 1 to indicate that we need to invalidate STANDARD and DBMS_STANDARD
@@?/rdbms/admin/loc_to_common3.sql 1

This script as description suggests:

Rem loc_to_common3.sql - helper script for converting local to common
Rem
Rem DESCRIPTION
Rem      Does the third set of operations needed to convert local to common.
Rem      Does utlip + utlrp + related tasks.

handles invalidating objects named STANDARD and DBMS_STANDARD by running this section:

alter session set "_enable_view_pdb"=false;

-- if requested, invalidate STANDARD and DBMS_STANDARD and mark them common
update obj$ set status=6, flags=flags-bitand(flags,196608)+65536
 where &&1=1 and name in ('STANDARD', 'DBMS_STANDARD');
commit;

Unfortunately, this invalidation is based purely on object name, not on object type or ownership. Essentially, any object named STANDARD is invalidated — including user-created tables. In our case, five objects were affected.

Thanks to Mike Dietrich Oracle has acknowledged the issue and logged it as a Bug 37834302. A fix has already been proposed and is awaiting confirmation from the maintainers of noncdb_to_pdb.sql. Once the fix has been merged into MAIN, everybody can request a backport on top of any given RU.

This is an excellent reminder of the intricacies involved in NONCDB/PDB migrations, especially regarding naming collisions with Oracle’s internal objects.

Conclusion

When performing non-CDB to PDB migrations, it’s essential to be aware of possible conflicts involving reserved object names. If you’re naming your tables after reserved words, maybe — just maybe — reconsider. STANDARD, although technically valid in a non-CDB environment, can cause unexpected issues once moved into a multitenant architecture.

This case highlights the importance of thorough validation and testing during migrations, even when the process appears routine.