DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, April 25, 2017

Oracle Database 12.2 New Features-Separate Undo Tablespace for each PDB

12.2 New Features-Separate Undo Tablespace for each PDB
*************************************************





SQL> select c.con_id, c.name  con_name, t.tablespace_name, t.contents, t.status
from v$containers c, cdb_tablespaces t
where c.con_id=t.con_id
and t.tablespace_name like '%UNDO%'
order by 1,2;  2    3    4    5

    CON_ID CON_NAME TABLESPACE_NAME       CONTENTS     STATUS
---------- -------------------- ------------------------------ --------------------- ---------
1 CDB$ROOT UNDOTBS1                      UNDO     ONLINE
3 ORCLPDB         UNDOTBS1                     UNDO     ONLINE



SQL> conn system/oracle@orclpdb
Connected.
SQL> show parameter undo

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled       boolean  FALSE
undo_management        string  AUTO
undo_retention        integer  900
undo_tablespace        string  UNDOTBS1

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/orclpdb/system01.dbf
/u01/app/oracle/oradata/orcl/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/orcl/orclpdb/undotbs01.dbf
/u01/app/oracle/oradata/orcl/orclpdb/users01.dbf


SQL> create undo tablespace undopdb1 datafile '/u01/app/oracle/oradata/orcl/orclpdb/undopdb1.dbf' size 2m;

SQL> alter system set undo_tablespace='undopdb1';
System altered.

SQL> show parameter undo
NAME           TYPE                  VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled     boolean          FALSE
undo_management              string                  AUTO
undo_retention     integer          900
undo_tablespace              string                  undopdb1

SQL> alter system set undo_tablespace='UNDOTBS1';
System altered.

SQL> drop tablespace undopdb1 including contents and datafiles;

Tablespace dropped.




No comments: