HomeOracle › Oracle, resize the undo tablespace

Oracle, resize the undo tablespace

Connect to Oracle as sysdba

  • sqlplus /nolog
  • connect sys/xxx@ORCL as sysdba

Query the V$Parameter to Find out which undo tablespace is in use

    select name,value from v$parameter where name in (‘undo_management’,’undo_tablespace’);NAME VALUE
    ——————- ——————-
    undo_management AUTO
    undo_tablespace UNDOTBS1

Temporally, Create a new undo tablespace UNDOTBS_temp

  • create undo tablespace UNDOTBS_temp datafile D:\oradata\ORCL\UNDOTBS_temp_01.DBF’ size 1000m reuse;

Alter the system to use the new undo tablespace.

    alter system set undo_tablespace=UNDOTBS_temp;

Drop and recreate the UNDOTBS1 tablespace with a smaller size.

    drop tablespace UNDOTBS1 including contents;
    Tablespace dropped.
    create undo tablespace UNDOTBS1 datafile ‘D:\oradata\ORCL\UNDOTBS1_01.DBF’ size 2000m reuse;
    Tablespace created.

Alter the system to use the new undo tablespace

    alter system set undo_tablespace=UNDOTBS1 ;

Drop the temporaly created tablespace UNDOTBS_temp.

    drop tablespace UNDOTBS1 including contents;

Comments are closed.