How to shrink Undo Tablespace datafile to smaller size by dropping and recreating new undo tablespace
You created undo tablespace using AUTOEXTEND ON MAXSIZE UNLIMITED to avoid error.
Your have just done puring (deleting millions of rows) on your production database and you noticed that your undo tablespace datafile is huge in size.
You tried to use “ALTER DATABASE DATAFILE .. RESIZE”, and this command failed with ORA-3297 error. , i.e.: “file contains ~~ blocks of data beyond requested RESIZE value”.
So you should drop and recreate undo tablespace using the following commands:
1, Connect to Oracle
sqlplus /nolog
connect sys/xxx@crm as sysdba
2, Find out which undo tablespace is being used:
select name,value from v$parameter where name in ('undo_management','undo_tablespace');
NAME VALUE
------------------- -------------------
undo_management AUTO
undo_tablespace UNDOTBS1
3, Create new undo tablespace UNDOTBS2 with smaller size.
SQL> create undo tablespace UNDOTBS2 datafile K:\oradata\CRM\UNDOTBS2_01.DBF' size 1000m reuse;
4, Tell Oracle to use new undo tablespace.
SQL> alter system set undo_tablespace=UNDOTBS2;
5, Now you can safely drop and recreate tablespace UNDOTBS1.
Tags: Oracle DBA
SQL> drop tablespace UNDOTBS1 including contents;
Tablespace dropped.SQL> create undo tablespace UNDOTBS1 datafile ‘K:\oradata\CRM\UNDOTBS1_01.DBF’ size 2000m reuse;
Tablespace created.
Malay said,
October 10, 2007 @ 2:45 am
GOOD
Erkki said,
June 17, 2008 @ 9:57 am
Thanks! All Oracle gurus had already left and I was desperately out of space. And it still works… (although I had to delete the datafile manually after drop)
mohamed mamdouh said,
June 24, 2008 @ 6:08 am
many thanks
Alek Enirevlow said,
December 6, 2008 @ 8:11 pm
Erkki, you’ll need to use:
drop tablespace UNDOTBS1 including contents and datafiles;
Aleks said,
January 28, 2009 @ 7:35 pm
“#
Alek Enirevlow said,
December 6, 2008 @ 8:11 pm
Erkki, you’ll need to use:
drop tablespace UNDOTBS1 including contents and datafiles;”
You don’t if you use the reuse command when re-creating the tablespace.