Resize tablespaces including UNDO workaround

July 30, 2012 admin Oracle

We can resize the database datafiles with:

SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs01.dbf’ RESIZE 500M;

Most of the times when I try to resize the undo tablespace I encounter ORA-3297 error: file contains used data beyond the requested RESIZE value. This means that some undo information stills stored above the datafile size we want to set. We can check the most high used block to check the minimum size that we can resize a particular datafile. For that we can query the dba_free_space dictionary view.

Another way to set our undo tablespace to the size that we want is to create another undo tablespace, set it the default one, take offline the old and then just drop the big old tablespace.

To check your undo tablespace info issue the following statement:

SQL> SELECT name,value FROM v$parameter WHERE name IN (‘undo_management’,’undo_tablespace’);

NAME VALUE
————————
undo_management AUTO
undo_tablespace UNDOTBS01SQL>
SQL> CREATE UNDO TABLESPACE undotbs02
DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs02.dbf’ SIZE 1024M
REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 10240M;

SQL> ALTER SYSTEM SET undo_tablespace = ‘UNDOTBS02′;
SQL> ALTER TABLESPACE undotbs01 OFFLINE;
SQL> DROP TABLESPACE undotbs01 INCLUDING CONTENTS AND DATAFILES;
SQL>

With these steps we created a new undo tablespace, set it as the system default undo tablespace and drop the old tablespace including the datafiles.

Test a Disk for I/O write timing. Get good idea for open_cursors value