undo tablespace 크기 변경
- undo tablespace의 크기를 변경하려면 기존 사용 중인 undo tablespace를 파악한 후 resize 하면 됩니다.
(temporary tablespace와 다르게 기존 undo tablespace를 resize 할 수 있습니다. resize 후 재부팅할 필요가 없지만, 재부팅을 할 수 있는 환경이라면 기존 undo를 반환하기 위해 재부팅을 하면 좋습니다)
1) 현재 운영 중인 undo tablespace name 확인
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS1
SQL> select tablespace_name, bytes/1024/1024 MB, maxbytes/1024/1024 MMB, file_name, autoextensible AUTO
from dba_data_files;
TABLESPACE_NAME MB MMB FILE_NAME AUT
------------------------------ ---------- ---------- ------------------------------------------------------- ---
SYSTEM 700 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\SYSTEM01.DBF YES
SYSAUX 550 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\SYSAUX01.DBF YES
UNDOTBS1 300 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\UNDOTBS01.DBF YES
USERS 10 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\USERS01.DBF YES
TEST 10 32767.9844 D:\ORA_BACKUP\TEST01.DBF YES2) undo tablespace resize
SQL> alter database datafile 'C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\UNDOTBS01.DBF' resize 100m;
SQL> select tablespace_name, bytes/1024/1024 MB, maxbytes/1024/1024 MMB, file_name, autoextensible AUTO
from dba_data_files;
TABLESPACE_NAME MB MMB FILE_NAME AUT
------------------------------ ---------- ---------- ------------------------------------------------------- ---
SYSTEM 700 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\SYSTEM01.DBF YES
SYSAUX 550 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\SYSAUX01.DBF YES
UNDOTBS1 300 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\UNDOTBS01.DBF YES
USERS 100 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\USERS01.DBF YES
TEST 10 32767.9844 D:\ORA_BACKUP\TEST01.DBF YES
undo tablespace 변경
- 기존에 설정된 default undo tablespace에서 새로 tablespace를 생성하고 생성한 tablespace로 변경합니다.
1) 현재 운영 중인 undo tablespace name 확인
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS1
SQL> select tablespace_name, bytes/1024/1024 MB, maxbytes/1024/1024 MMB, file_name, autoextensible AUTO
from dba_data_files;
TABLESPACE_NAME MB MMB FILE_NAME AUT
------------------------------ ---------- ---------- ------------------------------------------------------- ---
SYSTEM 700 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\SYSTEM01.DBF YES
SYSAUX 550 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\SYSAUX01.DBF YES
UNDOTBS1 300 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\UNDOTBS01.DBF YES
USERS 10 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\USERS01.DBF YES
TEST 10 32767.9844 D:\ORA_BACKUP\TEST01.DBF YES2) 임시의 새로운 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;3) 생성된 undo tablespace 확인 (undotbs2)
SQL> select tablespace_name, bytes/1024/1024 MB, maxbytes/1024/1024 MMB, file_name, autoextensible AUTO
from dba_data_files
TABLESPACE_NAME MB MMB FILE_NAME AUT
------------------------------ ---------- ---------- ------------------------------------------------------- ---
SYSTEM 700 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\SYSTEM01.DBF YES
SYSAUX 550 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\SYSAUX01.DBF YES
UNDOTBS1 300 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\UNDOTBS01.DBF YES
USERS 10 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\USERS01.DBF YES
TEST 10 32767.9844 D:\ORA_BACKUP\TEST01.DBF YES
UNDOTBS2 500 32767.9844 C:\ORACLE\APP\HYEOB.KIM\ORADATA\ORACLE\UNDOTBS2.DBF YES4) undo tablespace에 설정된 rollback segment를 확인
SQL> select segment_name, tablespace_name, status
2 from dba_rollback_segs
3 order by 2;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU2_2683774700$ UNDOTBS1 ONLINE
_SYSSMU1_3816786214$ UNDOTBS1 ONLINE
_SYSSMU10_3127983438$ UNDOTBS1 ONLINE
_SYSSMU4_4276191194$ UNDOTBS1 ONLINE
_SYSSMU8_144927730$ UNDOTBS1 ONLINE
_SYSSMU7_2918432556$ UNDOTBS1 ONLINE
_SYSSMU6_2914453419$ UNDOTBS1 ONLINE
_SYSSMU5_1246563608$ UNDOTBS1 ONLINE
_SYSSMU3_2738722134$ UNDOTBS1 ONLINE
_SYSSMU9_3421301242$ UNDOTBS1 ONLINE
_SYSSMU22_1860458664$ UNDOTBS2 OFFLINE
_SYSSMU23_4105948215$ UNDOTBS2 OFFLINE
_SYSSMU24_4125868429$ UNDOTBS2 OFFLINE
_SYSSMU25_3922061799$ UNDOTBS2 OFFLINE
_SYSSMU21_1562334345$ UNDOTBS2 OFFLINE
_SYSSMU20_1705216662$ UNDOTBS2 OFFLINE
_SYSSMU19_2547820628$ UNDOTBS2 OFFLINE
_SYSSMU18_3866007816$ UNDOTBS2 OFFLINE
_SYSSMU17_3493676169$ UNDOTBS2 OFFLINE
_SYSSMU16_1330652104$ UNDOTBS2 OFFLINE
_SYSSMU15_1966862661$ UNDOTBS2 OFFLINE
_SYSSMU14_605629855$ UNDOTBS2 OFFLINE
_SYSSMU26_3409566138$ UNDOTBS2 OFFLINE
_SYSSMU12_2747588289$ UNDOTBS2 OFFLINE
_SYSSMU11_3824030738$ UNDOTBS2 OFFLINE
_SYSSMU13_3108999731$ UNDOTBS2 OFFLINE5) undo tablespace 변경
SQL> alter system set undo_tablespace=UNDOTBS2;
시스템이 변경되었습니다.
6) 새로운 segment가 online 모드로 변경되었는지 확인
SQL> select segment_name, tablespace_name, status
2 from dba_rollback_segs
3 order by 2;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU2_2683774700$ UNDOTBS1 OFFLINE
_SYSSMU1_3816786214$ UNDOTBS1 OFFLINE
_SYSSMU10_3127983438$ UNDOTBS1 OFFLINE
_SYSSMU4_4276191194$ UNDOTBS1 OFFLINE
_SYSSMU8_144927730$ UNDOTBS1 OFFLINE
_SYSSMU7_2918432556$ UNDOTBS1 OFFLINE
_SYSSMU6_2914453419$ UNDOTBS1 OFFLINE
_SYSSMU5_1246563608$ UNDOTBS1 OFFLINE
_SYSSMU3_2738722134$ UNDOTBS1 OFFLINE
_SYSSMU9_3421301242$ UNDOTBS1 OFFLINE
_SYSSMU22_1860458664$ UNDOTBS2 ONLINE
_SYSSMU23_4105948215$ UNDOTBS2 ONLINE
_SYSSMU24_4125868429$ UNDOTBS2 ONLINE
_SYSSMU25_3922061799$ UNDOTBS2 ONLINE
_SYSSMU21_1562334345$ UNDOTBS2 ONLINE
_SYSSMU20_1705216662$ UNDOTBS2 ONLINE
_SYSSMU19_2547820628$ UNDOTBS2 ONLINE
_SYSSMU18_3866007816$ UNDOTBS2 ONLINE
_SYSSMU17_3493676169$ UNDOTBS2 ONLINE
_SYSSMU16_1330652104$ UNDOTBS2 ONLINE
_SYSSMU15_1966862661$ UNDOTBS2 ONLINE
_SYSSMU14_605629855$ UNDOTBS2 ONLINE
_SYSSMU26_3409566138$ UNDOTBS2 ONLINE
_SYSSMU12_2747588289$ UNDOTBS2 ONLINE
_SYSSMU11_3824030738$ UNDOTBS2 ONLINE
_SYSSMU13_3108999731$ UNDOTBS2 ONLINE
27 행이 선택되었습니다.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 3600
undo_tablespace string UNDOTBS27) 기존 undo tablespace 삭제
SQL> drop tablespace undotbs1 including contents and datafiles;
테이블스페이스가 삭제되었습니다.참고
Devops Engineer