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.