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
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
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
SQL> CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE
'C:\oracle\product\10.2.0\oradata\test\undotbs2.dbf'
SIZE 500M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;
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
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
SQL> alter system set undo_tablespace=UNDOTBS2;
시스템이 변경되었습니다.
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
SQL> drop tablespace undotbs1 including contents and datafiles;
테이블스페이스가 삭제되었습니다.
참고