Skip to main content
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    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%.

    Misunderstanding of how expired UNDO records are handled..

    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