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.

  • April 25, 2017 | 14 views
  • Comments