12c TEMP_UNDO_ENABLED limitations

I had blogged about 12c new feature of  "no redo for undo for Global temporary tables(GTT)". While trying to show the demo, i created a simple script to first

set AUTOTRACE TRACEONLY STATISTICS to show the redo generation during insert and update into GTT.

Then set TEMP_UNDO_ENABLED=FALSE to demo default redo generation numbers.

Next, when i set TEMP_UNDO_ENABLED=TRUE and reran the insert and update, i did not see much difference in amount of redo generation !!!

The reduction in redo was supposed to be more than 90% for large inserts and updates !!

After various trials, i found that the manual had hinted at a limitation.

 Only, that it is not clear. Below is the quote from the doc.

"When a session uses temporary objects for the first time, the current value of the TEMP_UNDO_ENABLED initialization parameter is set for the rest of the session. Therefore, if temporary undo is enabled for a session and the session uses temporary objects, then temporary undo cannot be disabled for the session. Similarly, if temporary undo is disabled for a session and the session uses temporary objects, then temporary undo cannot be enabled for the session."

What this means is if a session has started undo stream in UNDO tablespace, then it cannot switch to log undo in temporary tablespace in between. You need to set  TEMP_UNDO_ENABLED before accessing any GTTs.

So, in the demo, i simply reconnected (sql> scott/passwd) to the session and set TEMP_UNDO_ENABLED=TRUE to fix the problem.

Another test i conducted was to see if i could insert in GTTS after opening the PBDs in read only mode. I was not able to insert data. i got the following errors.

[oracle@localhost ~]$ sqlplus system/oracle@pdb2

SQL*Plus: Release 12.1.0.1.0 Production on Sun Sep 29 04:00:50 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Sun Sep 29 2013 03:36:08 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show pdbs

    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2  READ ONLY  NO
SQL> alter system set TEMP_UNDO_ENABLED=TRUE ;

System altered.

SQL> insert into vijay.ro_test values (4324) ;
commit;
insert into vijay.ro_test values (4324)
                  *
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access


SQL> 
Commit complete.


SQL>