1.undo tablespace 확인
>SHOW PARAMETER UNDO_TABLESPACE NAME TYPE VALUE ------------------------------------ ----------- --------- undo_tablespace string UNDOTBS1
2.새로운 undo tablespace 생성
>CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/u01/app/oracle/oradata/XE/undotbs2.dbf' SIZE 500M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;
3.새로 생성된 undo tablespace 확인
>SELECT TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO'; TABLESPACE_NAME CONTENTS EXTENT_MAN ------------------------------ --------- ---------- UNDOTBS1 UNDO LOCAL UNDOTBS2 UNDO LOCAL
4.기존 tablespace 새로 생성된 undo tablespace 로 변경
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;
5. undo tablespace rollbacksegment를 확인
>SELECT SEGMENT_NAME, TABLESPACE_NAME, STATUS FROM DBA_ROLLBACK_SEGS ORDER BY 2; SEGMENT_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ --------- SYSTEM SYSTEM ONLINE _SYSSMU10_2569484742$ UNDOTBS1 OFFLINE _SYSSMU11_3803757380$ UNDOTBS1 OFFLINE _SYSSMU12_4174379080$ UNDOTBS1 OFFLINE _SYSSMU13_3916421791$ UNDOTBS1 OFFLINE _SYSSMU14_2300278307$ UNDOTBS1 OFFLINE _SYSSMU15_4033947069$ UNDOTBS1 OFFLINE _SYSSMU16_4062637427$ UNDOTBS1 OFFLINE _SYSSMU17_1453808188$ UNDOTBS1 OFFLINE _SYSSMU18_3467042317$ UNDOTBS1 OFFLINE _SYSSMU19_1248800793$ UNDOTBS2 ONLINE _SYSSMU1_3789641169$ UNDOTBS1 OFFLINE _SYSSMU20_312634753$ UNDOTBS2 ONLINE _SYSSMU21_729298621$ UNDOTBS2 ONLINE _SYSSMU22_1719680222$ UNDOTBS2 ONLINE _SYSSMU23_3302642205$ UNDOTBS2 ONLINE _SYSSMU24_2544391359$ UNDOTBS2 ONLINE _SYSSMU25_2185594594$ UNDOTBS2 ONLINE _SYSSMU26_1297638036$ UNDOTBS2 ONLINE _SYSSMU27_3593652258$ UNDOTBS2 ONLINE _SYSSMU28_1996597891$ UNDOTBS2 ONLINE _SYSSMU2_1827203912$ UNDOTBS1 OFFLINE _SYSSMU3_3346129149$ UNDOTBS1 OFFLINE _SYSSMU4_655887589$ UNDOTBS1 OFFLINE _SYSSMU5_3703078872$ UNDOTBS1 OFFLINE _SYSSMU6_725569783$ UNDOTBS1 OFFLINE _SYSSMU7_3583332791$ UNDOTBS1 OFFLINE _SYSSMU8_4175076471$ UNDOTBS1 OFFLINE _SYSSMU9_1317495879$ UNDOTBS1 OFFLINE
6.기존 tablespace 삭제
>DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;