[오라클]Undo Tablespace 변경

오라클9i 이상 filesystem UNDO TABLESPACE 변경
- All undo tablespaces are created permanent, read/write, and in logging mode.

 

1. 기존 UNDO TABLESPACE를 DROP 후 CREATE

    SQL> connect / as sysdba

    SQL> CREATE  UNDO TABLESPACE UNDOIMSI

                           DATAFILE '/oradata/undoimsi.dbf' SIZE 10M;
    SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOIMSI;

    SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
    SQL> CREATE  UNDO TABLESPACE "UNDOTBS1"
                           DATAFILE '/oradata/undotbs01.dbf' SIZE 500M
                           EXTENT MANAGEMENT LOCAL;
   SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1;

   SQL> DROP TABLESPACE UNDOIMSI INCLUDING CONTENTS AND DATAFILES;

 

2. UNDO datafile Resize
    SQL> connect / as sysdba

    SQL> alter database datafile '/oradata/UNDOTBS01.dbf' resize 4000M;