Saturday, November 30, 2013

temp_undo_enabled does not work as expected in non-standby database

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for Solaris: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> select file_id,file_name from dba_data_files where file_id=4;

   FILE_ID FILE_NAME
---------- --------------------------------------------------
         4 /u01/app/oracle/oradata/orcl/undotbs01.dbf

SQL> select file_id,file_name from dba_temp_files where file_id=1;

   FILE_ID FILE_NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/orcl/temp01.dbf

SQL> select sum(blocks) from dba_segments where owner='SH' and segment_name='SALES';

SUM(BLOCKS)
-----------
      16384

Scenario 1:  temp_undo_enabled=false (default behavior)

    
SQL> create global temporary table sales as select * from sh.sales where 1=2;

Table created.

SQL> alter session set temp_undo_enabled=false;

Session altered.

SQL> select used_ublk,start_ubafil from v$transaction where ses_addr=(select saddr from v$session where sid = sys_context('userenv','sid'));

no rows selected

SQL> insert into sales select * from sh.sales;

918843 rows created.

SQL> select used_ublk,start_ubafil from v$transaction where ses_addr=(select saddr from v$session where sid = sys_context('userenv','sid'));

USED_UBLK START_UBAFIL
---------- ------------
       300            4

SQL> delete from sales;

918843 rows deleted.

SQL> select used_ublk,start_ubafil from v$transaction where ses_addr=(select saddr from v$session where sid = sys_context('userenv','sid'));

USED_UBLK START_UBAFIL
---------- ------------
     17398            4
    
SQL> select segtype,sum(blocks) from v$tempseg_usage where session_addr=(select saddr from v$session where sid=userenv('sid')) group by segtype;

SEGTYPE   SUM(BLOCKS)
--------- -----------
DATA             4608

SQL> rollback;

Rollback complete.

SQL> select segtype,sum(blocks) from v$tempseg_usage where session_addr=(select saddr from v$session where sid=userenv('sid')) group by segtype;

SEGTYPE   SUM(BLOCKS)
--------- -----------
DATA              128

SQL> select used_ublk,start_ubafil from v$transaction where ses_addr=(select saddr from v$session where sid = sys_context('userenv','sid'));

no rows selected

Scenario 2:  temp_undo_enabled=true


SQL> drop table sales;

Table dropped.

SQL> create global temporary table sales as select * from sh.sales where 1=2;

Table created.

SQL> alter session set temp_undo_enabled=true;

Session altered.

SQL> select used_ublk,start_ubafil from v$transaction where ses_addr=(select saddr from v$session where sid = sys_context('userenv','sid'));

no rows selected

SQL> select segtype,sum(blocks) from v$tempseg_usage where session_addr=(select saddr from v$session where sid=userenv('sid')) group by segtype;

SEGTYPE   SUM(BLOCKS)
--------- -----------
DATA              128

SQL> insert into sales select * from sh.sales;

918843 rows created.

SQL> select used_ublk,start_ubafil from v$transaction where ses_addr=(select saddr from v$session where sid = sys_context('userenv','sid'));

USED_UBLK START_UBAFIL
---------- ------------
       300            4 <--- undo segment still using datafile 4

SQL> select segtype,sum(blocks) from v$tempseg_usage where session_addr=(select saddr from v$session where sid=userenv('sid')) group by segtype;

SEGTYPE   SUM(BLOCKS)
--------- -----------
DATA             4608

SQL> delete from sales;

918843 rows deleted.

SQL> select used_ublk,start_ubafil from v$transaction where ses_addr=(select saddr from v$session where sid = sys_context('userenv','sid'));

USED_UBLK START_UBAFIL
---------- ------------
     17397            4

SQL> select segtype,sum(blocks) from v$tempseg_usage where session_addr=(select saddr from v$session where sid=userenv('sid')) group by segtype;

SEGTYPE   SUM(BLOCKS)
--------- -----------
DATA             4608

SQL> commit;

Commit complete.

Scenario 3:  Readonly database

SQL> startup mount
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size                  2361496 bytes
Variable Size            1040189288 bytes
Database Buffers          520093696 bytes
Redo Buffers                7364608 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL> conn /
Connected.
SQL> insert into sales select * from sh.sales;
insert into sales select * from sh.sales
            *
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access