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;
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;
Thanks for your article. Well explained in a very easy and simple manner. Easily understandable by newbies :)
ReplyDelete