How to Change Undo Tablespace in Oracle on AWS RDS

Select Language:

Changing the UNDO tablespace in an Oracle RDS multitenant database is definitely doable. If you’re working with a Container Database (CDB) setup, RDS offers specific procedures to change the UNDO tablespace for each Pluggable Database (PDB).

Before making any changes, it’s a good idea to test everything in a non-production environment. Once you’re confident everything works smoothly, you can proceed with the steps on your production database.

First, you’ll need to create a new UNDO tablespace. You can do this with a simple SQL command:

sql
create undo tablespace “NEW_UNDO”;

After creating the new UNDO tablespace, the next step is to switch your PDB to use this new space. You can do this with the following command:

sql
exec rdsadmin.rdsadmin_util.set_pdb_undo_tablespace(‘NEW_UNDO’);

Once you’ve confirmed that the new UNDO tablespace is being used properly, you can remove the old one. However, make sure that no active transactions are using the old UNDO space before doing this. You can delete the old UNDO tablespace with:

sql
drop tablespace “OLD_UNDO”;

Be cautious — if there are transactions still running or queries that need rollback data at the time you drop the tablespace, you might encounter errors. It’s best to perform the deletion when the database isn’t processing long-running queries or active transactions involving the old UNDO.

Compared to managing UNDO on a traditional on-premise Oracle database, RDS doesn’t have many restrictions. The main difference is the procedure, but the process remains fairly straightforward.

For additional details on managing UNDO in Oracle, you can refer to Oracle’s official documentation here.

Following these steps carefully will help you change your UNDO tablespace efficiently and safely.