• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Resize the Undo Tablespace in Oracle Database

By admin

This post provides the steps to resize the Undo tablespace.ie, to add space or shrink the current Undo tablespace.

Shrinking Undo Tablespace Size

Undo space once allocated will be available for reuse but will not be deallocated to the OS. The best way to shrink Undo tablespace is to switch to a new Undo tablespace and drop the old Undo tablespace. The steps are:

1. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\PRODUCT\11.2.0\ORADATA\ORCL\UNDOTBS02.DBF' size 5000M;

2. Switch to the new Undo tablespace:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;

3. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline.

sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

If there are Undo segments with a status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE. You may have to wait for the duration of the tuned_undoretention (from v$undostat) to ensure all Undo segments have become OFFLINE.

sql> select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name=[undo tablespace to be dropped];

For example:

sql> select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name='UNDOTBS1';

4. If all the Undo segments in the old Undo tablespace to the dropped is of status OFFLINE, then drop the tablespace.

sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

5. Verify and then drop:

sql> drop tablespace [tablespace_name] including contents and datafiles;

For example:

sql> drop tablespace UNDOTBS1 including contents and datafiles;

Add Space to the Undo Tablespace

For increasing / resize undo tablespace there are two options :

  1. Resize the existing undo datafile
  2. Add new undo datafile to the tablespace.

1. To resize the existing undo datafile:

col T_NAME for a23
col FILE_NAME for a65
select tablespace_name T_NAME,file_name, bytes/1024/1024 MB from dba_data_files where tablespace_name =(SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace') order by file_name;

alter database datafile '[COMPLETE_PATH_OF_UNDO_DBF_FILE]' resize [SIZE]M;

For example:

sql> alter database datafile 'D:\ORACLE_DB\TESTDB\TESTDB\UNDOTBS01.DBF' resize 1500M;

2. Step to add a new datafile:

sql> alter tablespace [UNDO tbs name] ADD DATAFILE '[COMPLETE_PATH_OF_UNDO_DBF_FILE]' size 20M;

For example:

sql> alter tablespace UNDOTBS1 ADD DATAFILE 'D:\ORACLE_DB\TESTDB\TESTDB\UNDOTBS02.DBF' size 20M;

Filed Under: oracle

Some more articles you might also be interested in …

  1. What are Oracle Data Guard Protection Modes (redo transport rules) and how to configure them
  2. Oracle RMAN : Block-Level Media Recovery (Basics and Example)
  3. How to Roll Forward a standby database using RMAN incremental backup in 11g
  4. RMAN ‘Duplicate From Active Database’ Feature in Oracle 11g
  5. How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command
  6. New Background Processes In Oracle Database 12c
  7. Understanding Oracle Database Automatic SGA Memory Tuning
  8. Running RMAN DUPLICATE / RESTORE on a different version than source database version.
  9. How to backup and delete archivelogs older than # number of days
  10. How to extend ASM disk from OS level in CentOS/RHEL

You May Also Like

Primary Sidebar

Recent Posts

  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • How to Capture More Logs in /var/log/dmesg for CentOS/RHEL
  • Unable to Start RDMA Services on CentOS/RHEL 7
  • How to rename a KVM VM with virsh
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary