Saturday 1 September 2012

How to Empty undo tablespace & reclaim space?

Hi,
Over the period of time Undo tablespace size (OS level) grow but actual size of it is small
then How to Empty undo tablespace & reclaim space?
empty undo tablespace
--follow steps if undo tablespace growing

--Create New UNDO tablespace UNDOTBS2

create undo tablespace UNDOTBS2 datafile 'D:\app\Administrator\oradata\DMS\undotbs101.dbf' size 1024M;

--Set default undo_tablespace to new undo tbs

alter system set undo_tablespace='UNDOTBS2' scope=both;

--Drop UNDOTBS1

drop  tablespace UNDOTBS1 including contents and datafiles;

--Create UNDO tbs UNDOTBS1

create undo tablespace UNDOTBS1 datafile 'D:\app\Administrator\oradata\DMS\undotb101.dbf' size 1024M;

--Set default undo_tablespace to UNDOTBS1

alter system set undo_tablespace='UNDOTBS1' scope=both;

--Drop UNDOTBS2

drop  tablespace UNDOTBS2 including contents and datafiles;

1 comment:

  1. Thanks for your article. Well explained in a very easy and simple manner. Easily understandable by newbies :)

    ReplyDelete