Undo tablespace sizing
It's tricky because you cannot shrink the current undo log, you can only shrink those undo files that are not currently in-use.
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.
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:
1) Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_tbs1 datafile 'undorbs1.dbf' size 2G;
alter database
datafile '/ou1/app/oracley/undo_ts01.dbf'
resize 2G;
You must Supply the correct file name in the above command.
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:
1) Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_tbs1 datafile 'undorbs1.dbf' size 2G;
2)Set the new tablespace as an undo_tablespace
SQL> alter system set undo_tablespace=undo_tbs1;
3) Drop the old tablespace.
SQL> drop tablespace undo_tbs0 including contents.
Note-- 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.
