한번 대용량 Transaction이 수행되고 나면 Undo Tablespace가 거대하게 증가하게 된다. 이후작업에서는 그만큼의 Undo 용량이 필요하지 않지만, 한번 증가한 Undo 사이즈는 유지 된다. 따라서 증가한 Undo Tablespace크기를 Shrink할 필요가 있다.
(Sometimes it happens that we accidentally or unconsciously run very big DML transactions in database. Especially in ones, which dedicated for development purposes. As change history must be saved somewhere to enable a rollback of transaction, memory is necessary for that. And this is purpose which undo tablespace is serving for in Oracle database. And after some huge transactions it can be autoextended to enormous size. Which basically in normal database run is not necessary. But it stays extended and occupy disk space. And now we want to shrink it and release some of disk space)
Undo Tablespace Shrink 순서 -
You can shrink the datafile of the UNDO tablespace on the primary database to 5G by using the following command:
alter database
datafile '/ou1/app/oracley/undo_ts01.dbf'
resize 2G;
You must Supply the correct file name in the above command.
|
For ASM, you must specify the correct ASM filename. The undo datafile on the standby
If you get this error:
ORA-03297: file contains used data beyond requested RESIZE value
This ORA-03297 error says that only space can be released if it is at the end of the datafile. The undo segments may be scattered all over the UNDO datafile.
In this case you have to do the following on the database:
-- Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size 2G;
-- Set the new tablespace as an undo_tablespace
SQL> alter system set undo_tablespace=undo_rbs1;
-- Drop the old tablespace.
SQL> drop tablespace undo_rbs0 including contents.
-- Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size 2G;
-- Set new tablespace as undo_tablespace
SQL> alter system set undo_tablespace=undo_rbs1;
-- Drop the old tablespace.
SQL> drop tablespace undo_rbs0 including contents.
Dropping the old tablespace may give the 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 existing transaction to commit or rollback.
