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

The Geek Diary

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

How to Shrink the datafile of Undo Tablespace in Oracle Database

by admin

Your production database has semiannual or annual purging programs which generate huge redo. Due to this requirement, your undo tablespace grows rapidly and occupies most of the space on the file system. The purging process is run only a few times a year. So would not like to keep the huge undo datafile in your database throughout the year. You don’t want to buy additional disks unnecessarily.

You have created an undo tablespace with datafiles as AUTOEXTEND ON MAXSIZE UNLIMITED to avoid the error:

ORA 1651 : unable to extend save undo segment by [num] in tablespace [name]

You have tried “alter database datafile .. resize” which always fails with error:

ORA 3297 : file contains [num] blocks of data beyond requested RESIZE value.

You want to shrink the datafile to utilize the disk space for other tablespaces or other purposes.

The Solution

The steps to accomplish the goal are:

1. Create a new undo tablespace with a smaller size:

SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size [new size];

2. Set the new tablespace as the undo tablespace to be used: (Note: If Data Guard Managed configuration is used, the below parameter modification needs to executed on any physical standbys serviced by this production database):

SQL> alter system set undo_tablespace=undo_rbs1;

3. Drop the old undo tablespace:

SQL> drop tablespace undo_rbs0 including contents;
NOTE: Dropping the old tablespace may give ORA-30013: undo tablespace ‘%s’ is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for an existing transaction to commit or rollback. Also be aware that on some platforms, disk space is not freed to the OS until the database is restarted. The disk space will remain “allocated” from the OS perspective until the database restart.

Points to Consider

– The value for UNDO_RETENTION also has a role in growth of undo tablespace. If there is no way to get the undo space for a new transaction, then the undo space (retention) will be reused. But, if the datafiles for undo tablespace are set to auto extensible, it will not reuse the space. In such scenarios, a new transaction will allocate space and your undo tablespace will start growing.

– Is big really bad? Overhead on larger file/tablespaces can theoretically impact the database and the OS. With a small file, the OS would have to do minimal I/O. Oracle would be able to cache the whole file and there would be fewer segments to manage. With AUM you get bitmapped files and all its (space management) performance benefits — (number of) undo segments are automatically managed and are not related to the size of the tablespace. With the bigger file/tablespace you will have other overhead — e.g. backup will take longer — but as far as the undo management there should be no performance impact just because the file/tablespace is bigger. That said, it is important to monitor systems (e.g. with StatsPack) and watch for environment-specific issues.

Filed Under: oracle

Some more articles you might also be interested in …

  1. Log/Trace files Generated during the EM Cloud Control OMS Startup
  2. Oracle Database : What Is The Search Order For The LDAP.ORA File
  3. Basics of Materialized Views in Oracle
  4. Oracleasm Service Fails to Start After Upgrade to oracleasm-support-2.1.11-1 RPM Package
  5. List of Operating System Processes Started by the OMS
  6. How to switch roles in Oracle Data Guard
  7. How to move ASM spfile to a different disk group
  8. Oracle GoldenGate: Replicat Sample Parameter File
  9. How To Change Timezone for Oracle Grid Infrastructure
  10. Adding Users to Oracle Passwordfile (Oracle Passwordfile Authentication)

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright