undo tablespace 늘리기

database를 운영하다보면 undo tablespace가 예기치 못하게 커지게 된다.
undo tablespace는 다른 tablespace와는 다르게 쉽게 그 크기를 변경 할 수 없다.
새로운 undo tablespace를 생성하여 기존 것과 바꾸어 주어야 한다.

[oracle@localhost ~]$ sqlplus system/manager

# 현재 운영중인 undo tablespace name 확인

SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ---------
undo_tablespace string UNDOTBS1


# 임시의 새로운 undo tablespace 생성.

SQL> CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE
'C:\oracle\product\10.2.0\oradata\test\undotbs2.dbf'
SIZE 500M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

# 생성된 undo tablespace 확인 (undotbs2)

SQL> select tablespace_name, contents, extent_management
from dba_tablespaces
where contents = 'UNDO';


TABLESPACE_NAME CONTENTS EXTENT_MAN
------------------------------ --------- ----------
UNDOTBS1 UNDO LOCAL
UNDOTBS2 UNDO LOCAL


# undo tablespace에 설정된 rollback segment를 확인

SQL> select segment_name, tablespace_name, status
from dba_rollback_segs
order by 2;



SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ---------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 ONLINE
_SYSSMU10$ UNDOTBS1 ONLINE
_SYSSMU9$ UNDOTBS1 ONLINE
_SYSSMU8$ UNDOTBS1 ONLINE
_SYSSMU7$ UNDOTBS1 ONLINE
_SYSSMU6$ UNDOTBS1 ONLINE
_SYSSMU5$ UNDOTBS1 ONLINE
_SYSSMU4$ UNDOTBS1 ONLINE
_SYSSMU3$ UNDOTBS1 ONLINE
_SYSSMU2$ UNDOTBS1 ONLINE
_SYSSMU19$ UNDOTBS2 OFFLINE
_SYSSMU18$ UNDOTBS2 OFFLINE
_SYSSMU17$ UNDOTBS2 OFFLINE
_SYSSMU16$ UNDOTBS2 OFFLINE
_SYSSMU20$ UNDOTBS2 OFFLINE
_SYSSMU14$ UNDOTBS2 OFFLINE
_SYSSMU13$ UNDOTBS2 OFFLINE
_SYSSMU12$ UNDOTBS2 OFFLINE
_SYSSMU11$ UNDOTBS2 OFFLINE
_SYSSMU15$ UNDOTBS2 OFFLINE

# 기존 tablespace와 대체.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

# undotbs2가 ONLINE 모드로 변경되었는지 확인한다.

SQL> select segment_name, tablespace_name, status
from dba_rollback_segs
order by 2;

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ---------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU10$ UNDOTBS1 OFFLINE
_SYSSMU9$ UNDOTBS1 OFFLINE
_SYSSMU8$ UNDOTBS1 OFFLINE
_SYSSMU7$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU19$ UNDOTBS2 ONLINE
_SYSSMU18$ UNDOTBS2 ONLINE
_SYSSMU17$ UNDOTBS2 ONLINE
_SYSSMU16$ UNDOTBS2 ONLINE
_SYSSMU20$ UNDOTBS2 ONLINE
_SYSSMU14$ UNDOTBS2 ONLINE
_SYSSMU13$ UNDOTBS2 ONLINE
_SYSSMU12$ UNDOTBS2 ONLINE
_SYSSMU11$ UNDOTBS2 ONLINE
_SYSSMU15$ UNDOTBS2 ONLINE


# 기존 tablespace 삭제.

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;



**************************************************************************************************
현재 UNDOTBS1은 삭제되었고, UNDOTBS2로만 사용되고 있는 상황입니다.
다시 UNDOTBS1으로 사용하려면 아래 방법을 이용하시면 됩니다.
**************************************************************************************************

-- 새로운 undo tablespace 생성.
SQL> CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE
'C:\oracle\product\10.2.0\oradata\test\undotbs01.dbf'
SIZE 500M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

-- 임시 tablespace와 대체.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1;

-- 임시 tablespace 삭제.
SQL> DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;