[Oracle] undo tablespace 크기 변경 / 생성

HYEOB KIM·2023년 6월 14일
1

Oracle

목록 보기
46/58

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                                YES

2) 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                                YES

2) 임시의 새로운 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     YES

4) 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                       OFFLINE

5) 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      UNDOTBS2

7) 기존 undo tablespace 삭제

SQL> drop tablespace undotbs1 including contents and datafiles;

테이블스페이스가 삭제되었습니다.

참고

profile
Devops Engineer

0개의 댓글