[undo tablespace 변경 작업]언두 테이블스페이스 변경작업
-- 먼저 현재
시스템의 undo tablespace를 확인
SQL> show parameter undo tablespace
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
undo_management string
AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
- 새로 undo
tablespace를 하나 만들자/
SQL> create undo tablespace undotbs datafile
'C:\app\ooo\oradata\onj\UNDOTBS.DBF' size 1000m
테이블스페이스가 생성되었습니다.
경
과: 00:00:19.33
-- undo에 할당 되어 있는 rollback segment를 확인해보자, 금방 만든 undo
tablespace와 기존의 것을 확인할 수 있다. 방금 만든 undo tablespace는 offline으로 됨을 확인하자.
SQL> select segment_name, tablespace_name, status from dba_rollback_segs
order by 2;
SEGMENT_NAME TABLESPACE_NAME
STATUS
------------------------------ ------------------------------
----------------
SYSTEM SYSTEM
ONLINE
_SYSSMU17_222748869$ UNDOTBS OFFLINE
_SYSSMU18_3634303265$ UNDOTBS OFFLINE
_SYSSMU19_3181162363$ UNDOTBS OFFLINE
_SYSSMU20_247679012$ UNDOTBS OFFLINE
_SYSSMU21_3214651666$ UNDOTBS OFFLINE
_SYSSMU15_2917605919$ UNDOTBS OFFLINE
_SYSSMU23_2401741525$ UNDOTBS OFFLINE
_SYSSMU24_887281181$ UNDOTBS OFFLINE
_SYSSMU25_3640496044$ UNDOTBS OFFLINE
_SYSSMU26_1561920818$ UNDOTBS OFFLINE
_SYSSMU16_4192955508$ UNDOTBS OFFLINE
_SYSSMU22_3865558656$ UNDOTBS OFFLINE
_SYSSMU1_3086899707$ UNDOTBS1 ONLINE
_SYSSMU2_1531987058$ UNDOTBS1 ONLINE
_SYSSMU3_478608968$ UNDOTBS1 ONLINE
_SYSSMU4_1451910634$ UNDOTBS1 ONLINE
_SYSSMU5_2520346804$ UNDOTBS1 ONLINE
_SYSSMU6_1439239625$ UNDOTBS1 ONLINE
_SYSSMU7_1101470402$ UNDOTBS1 ONLINE
_SYSSMU8_1682283174$ UNDOTBS1 ONLINE
_SYSSMU9_3186340089$ UNDOTBS1 ONLINE
_SYSSMU10_378818850$ UNDOTBS1 ONLINE
_SYSSMU11_3378023884$ UNDOTBS1 ONLINE
_SYSSMU12_2631554543$ UNDOTBS1 ONLINE
_SYSSMU13_3875143044$ UNDOTBS1 OFFLINE
_SYSSMU14_1585475619$ UNDOTBS1 OFFLINE
27 개의 행이 선택되었습니다.
경 과: 00:00:00.09
-- 현재 DB의 undo
tablespace를 방금 만든것으로 변경하자.
SQL> alter system set undo_tablespace=
undotbs;
시스템이 변경되었습니다.
경 과: 00:00:00.10
SQL> select
segment_name, tablespace_name, status from dba_rollback_segs order by 2;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------
----------------
SYSTEM SYSTEM
ONLINE
_SYSSMU17_222748869$ UNDOTBS ONLINE
_SYSSMU18_3634303265$ UNDOTBS ONLINE
_SYSSMU19_3181162363$ UNDOTBS ONLINE
_SYSSMU20_247679012$ UNDOTBS ONLINE
_SYSSMU21_3214651666$ UNDOTBS ONLINE
_SYSSMU15_2917605919$ UNDOTBS ONLINE
_SYSSMU23_2401741525$ UNDOTBS ONLINE
_SYSSMU24_887281181$ UNDOTBS ONLINE
_SYSSMU25_3640496044$ UNDOTBS ONLINE
_SYSSMU26_1561920818$ UNDOTBS ONLINE
_SYSSMU16_4192955508$ UNDOTBS ONLINE
_SYSSMU22_3865558656$ UNDOTBS ONLINE
_SYSSMU1_3086899707$ UNDOTBS1 OFFLINE
_SYSSMU2_1531987058$ UNDOTBS1 OFFLINE
_SYSSMU3_478608968$ UNDOTBS1 OFFLINE
_SYSSMU4_1451910634$ UNDOTBS1 OFFLINE
_SYSSMU5_2520346804$ UNDOTBS1 OFFLINE
_SYSSMU6_1439239625$ UNDOTBS1 OFFLINE
_SYSSMU7_1101470402$ UNDOTBS1 OFFLINE
_SYSSMU8_1682283174$ UNDOTBS1 OFFLINE
_SYSSMU9_3186340089$ UNDOTBS1 OFFLINE
_SYSSMU10_378818850$ UNDOTBS1 OFFLINE
_SYSSMU11_3378023884$ UNDOTBS1 OFFLINE
_SYSSMU12_2631554543$ UNDOTBS1 OFFLINE
_SYSSMU13_3875143044$ UNDOTBS1 OFFLINE
_SYSSMU14_1585475619$ UNDOTBS1 OFFLINE
27 개의 행이 선택되었습니다.
경 과: 00:00:00.03
-- 기존 undo
tablespace를 삭제
SQL> drop tablespace undotbs1;
-- 물리적으로 오라클
데이터파일이 있는 폴더에서(저의 경우 "C:\app\ooo\oradata\onj\") 아래 undotbs01.dbf를 지워주면 끝
| 
댓글 없음:
댓글 쓰기