ORA-65023: active transaction exists in container CDB$ROOT

I received this error message in the following scenario:

– Connect to CDB

SQL> conn / as sysdba
sho con_name

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

– Start a transaction

SQL> create table test(c number);
     insert into test values(1) ;

– Change the container to PDB without completing the transaction

SQL> alter session set container=pdb2;

– Try to shutdown the pluggable database

– It causes ORA-65023 because  on switching the container (not   making a connection to  PDB using connect), pending transactions in the original container are still in a pending state

SQL> shu immediate;
ORA-65023: active transaction exists in container CDB$ROOT

That’s what oracle documentation says for this error:

Cause

A statement attempted to create a new transaction in the current container while there was an active transaction in another container.

Action

Switch to the container with the active transaction and commit, rollback or detach the active transaction before attempting to issue any statement that will attempt to create a new transaction in another container.

– Switch back to original container (cdb$root) and commit the transaction

SQL> alter session set container=cdb$root;
commit;

– Switch the container to PDB and close the PDB

— The attempt succeeds as there is no pending transaction in the parent container.

SQL> alter session set container=pdb2;
shu immediate;
Pluggable Database closed.

SQL> sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB2                           MOUNTED

References:

Oracle Documentation

http://www.oracle-base.com/articles/12c/multitenant-runing-scripts-cdb-and-pdb-12cr1.php

 

——————————————————————————————–

Related Links:

Home

Database 12c

 

 

Your comments and suggestions are welcome!