undo表空间的缩小、计算、移动方法

缩小方法：
--Try to reduce the size of the undo tablespace directly
SQL> alter database datafile "file_name" resize 100M;
--If this succeeds, then nothing else needs to be done.
--If this fails(ORA-03297), please use the following method.
-- Create new undo tablespace with smaller size.
SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size 100m;
-- 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.

NOTE:
Dropping the old tablespace may give 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.

计算方法：
Sizing an UNDO tablespace requires three pieces of data.

(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

Two can be obtained from the initialization file: UNDO_RETENTION and DB_BLOCK_SIZE.
The third piece of the formula requires a query against the database. The number of undo blocks generated per second can be acquired from V$UNDOSTAT.

The following formula calculates the total number of blocks generated and divides it by the amount of time monitored, in seconds:

SQL>SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat;

Column END_TIME and BEGIN_TIME are DATE data types. When DATE data types are subtracted, the result is in days. To convert days to seconds, you multiply by 86400, the number of seconds in a day.
The result of the query returns the number of undo blocks per second. This value needs to be multiplied by the size of an undo block, which is the same size as the database block defined in DB_BLOCK_SIZE.

The following query calculates the number of bytes needed:
SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), (SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat), (select block_size as DBS from dba_tablespaces where tablespace_name= (select value from v$parameter where name = 'undo_tablespace'));

移动方法：
1. If the database is up, shut it down.
2. Copy the datafile to the new name/location at operating system level.
3. Mount the database.
4. Rename the file inside Oracle.
5. Open the database.
6. Query v$dbfile to confirm that the changes made were correct.
7. Remove the datafile(s) from the old location at the operating system level.


Reference:
Doc 268870.1 on MetaLink "How to Shrink the datafile of Undo Tablespace"
Doc 262066.1 on MetaLink "How To Size UNDO Tablespace For Automatic Undo Management"
Doc 620383.995 on MetaLink "Move Undo tablespace"


另一种OverHead的计算方法：
db_block_size：
2K : 32 + V$UNDOSTAT.MAXCONCURRENCY * 2
4K : 16 + V$UNDOSTAT.MAXCONCURRENCY * 2
8K : 8 + V$UNDOSTAT.MAXCONCURRENCY * 2
16K : 4 + V$UNDOSTAT.MAXCONCURRENCY * 2