UNDOTBS1 tablespace 99% full.
- Article Type: General
- Product: Aleph
- Product Version: 20
Problem Symptoms:
util o/14/1 shows the UNDOTBS1 tablespace at 99% full. Additional datafiles have been added to the tablespace, but it continues to fill up to 99%.
Cause:
Misunderstanding of how expired UNDO records are handled..
Resolution:
The UNDO tablespace works as follows:
* The UNDO records are not deleted when they are expired. They stay and are overwritten only when a new UNDO record needs to be written and there is no free space. Thus, it is normal for UNDOTBS1 to appear at 99% full.
* When there are no EXPIRED extents left for reuse and it can not allocate more free extents and AUTOEXTEND is not in use, the "unable to extend segment by 8 in undo tablespace 'UNDOTBS1'" will occur. Only In this case does a new datafile need to be added to UNDOTBS1 (via util o/13/3).
* The appropriate size of the UNDOTBS1 is discussed in KB 16384-41121 ("How big should UNDOTBS1 be?")
Additional Information
In order to remove datafiles added unnecessarily to UNDOTBS1 and reduce its size:
1. Create a new undo tablespace as :
SQL> create undo tablespace UNDOTBS2 datafile
SQL> '/exlibris1/oradata/aleph20/aleph20_undotbs101.dbf' size 4G;
2. Change parameter UNDO_TABLESPACE
SQL> alter system set UNDO_TABLESPACE=UNDOTBS2;
3. Drop UNDOTBS1
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
-- The drop command might take some time waiting for transactions to be ended.
Or, if the space is not otherwise needed, the UNDOTBS1 can be left as it is.
Category: System Management (500)
Subject: Oracle (500)
- Article last edited: 10/8/2013