Resizing the Undo tablespace

As your database grows and evolves, its undo tablespace needs might change too. In this post, we’ll guide you through the process of modifying the undo tablespace in Oracle 19c. We’ll cover the practical steps of resizing it or switching to a new undo tablespace, ensuring a smooth transition for your database.

There is only one active Undo tablespace in a database.

Checking our current undo tablespace.

Let’s start by checking our current undo data configuration. The parameter UNDO_TABLESPACE holds the current active undo tablespace. It’s worth noticing that in each database there is only one active undo tablespace.

show parameter UNDO_TABLESPACE

NAME            TYPE   VALUE    
--------------- ------ -------- 
undo_tablespace string UNDOTBS1 


column name format a10
column datafile format a50

SQL> select t.ts#, t.name, d.name datafile, d.status, d.enabled, d.bytes/1024/1024 MB, d.con_id
from v$tablespace t, v$datafile d
where upper(t.name) like 'UNDOTBS%'
  and d.ts# = t.ts#;

       TS# NAME       DATAFILE                                           STATUS  ENABLED            MB     CON_ID
---------- ---------- -------------------------------------------------- ------- ---------- ---------- ----------
         2 UNDOTBS1   /opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf        ONLINE  READ WRITE        130          3

Resizing the existing undo datafile.

To change the size of the undo tablespace in Oracle, you can either resize an existing datafile or add a new datafile to the tablespace.

-- Resizing datafile.
SQL> ALTER DATABASE DATAFILE '/opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf' RESIZE 500M;

Database altered.


column name format a10
column datafile format a50

SQL> select t.ts#, t.name, d.name datafile, d.status, d.enabled, d.bytes/1024/1024 MB, d.con_id
from v$tablespace t, v$datafile d
where upper(t.name) like 'UNDOTBS%'
  and d.ts# = t.ts#;


       TS# NAME       DATAFILE                                           STATUS  ENABLED            MB     CON_ID
---------- ---------- -------------------------------------------------- ------- ---------- ---------- ----------
         7 UNDOTBS1   /opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf        ONLINE  READ WRITE        500          3






-- Adding a new datafile.
SQL> ALTER TABLESPACE undotbs1 ADD DATAFILE '/opt/oracle/oradata/XE/XEPDB1/undotbs01b.dbf' SIZE 300M;

TABLESPACE UNDOTBS1 altered.


column name format a10
column datafile format a50

SQL> select t.ts#, t.name, d.name datafile, d.status, d.enabled, d.bytes/1024/1024 MB, d.con_id
from v$tablespace t, v$datafile d
where upper(t.name) like 'UNDOTBS%'
  and d.ts# = t.ts#;


       TS# NAME       DATAFILE                                           STATUS  ENABLED            MB     CON_ID
---------- ---------- -------------------------------------------------- ------- ---------- ---------- ----------
         7 UNDOTBS1   /opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf        ONLINE  READ WRITE        500          3
         7 UNDOTBS1   /opt/oracle/oradata/XE/XEPDB1/undotbs01b.dbf       ONLINE  READ WRITE        300          3

Switching to a new datafile.

This time we opt for creating a new undo tablespace and switch to it. Let’s see the steps.

SQL> CREATE UNDO TABLESPACE undotbs2 
DATAFILE '/opt/oracle/oradata/XE/XEPDB1/undotbs02.dbf'
SIZE 1G AUTOEXTEND ON;

TABLESPACE UNDOTBS2 created.


SQL> select t.ts#, t.name, d.name datafile, d.status, d.enabled, d.bytes/1024/1024 MB, d.con_id
from v$tablespace t, v$datafile d
where upper(t.name) like 'UNDOTBS%'
  and d.ts# = t.ts#;

       TS# NAME       DATAFILE                                           STATUS  ENABLED            MB     CON_ID
---------- ---------- -------------------------------------------------- ------- ---------- ---------- ----------
         2 UNDOTBS1   /opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf        ONLINE  READ WRITE        130          3
         7 UNDOTBS2   /opt/oracle/oradata/XE/XEPDB1/undotbs02.dbf        ONLINE  READ WRITE       1024          3



SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

System SET altered.


SQL> DROP TABLESPACE undotbS1 INCLUDING CONTENTS AND DATAFILES;

TABLESPACE UNDOTBS1 dropped.