[Oracle] 상황별 테이블스페이스 명령어

기존 TBS 확인

SELECT *
FROM DBA_DATA_FILES ;

 

• UNDO  TBS

TBS 확인

 SELECT TABLESPACE_NAME, FILE_NAME, BYTES 
 FROM DBA_DATA_FILES 
 WHERE TABLESPACE_NAME = '[UNDO_TBS_NAME]';

RESIZE

  ALTER DATABASE DATAFILE '[/UNDO_FILE_PATH/UNDO_FILE_NAME]' RESIZE [SIZE]M ;

DATAFILE 추가

ALTER TABLESPACE [UNDO_TBS_NAME] ADD DATAFILE '[/UNDO_FILE_PATH/UNDO_FILE_NAME]' SIZE [SIZE]M ;
 
-- 기존의 UNDOTBS1에 DATAFILE 추가
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/ORADATA/TESTDB/undotbs02.dbf' SIZE 10000M ;

---

• TEMP TBS

TBS 확인

-- TEMPFILE 확인
SELECT *
FROM DBA_TEMP_FILES ;
 
-- 현재 사용중인 TEMP TBS 확인
SELECT * 
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

RESIZE

ALTER DATABASE TEMPFILE '[/TEMPFILE_PATH/TEMPFILE_NAME]' RESIZE [SIZE]M ;
 
--
ALTER DATABASE TEMPFILE '/ORADATA/TESTDB/temp01.dbf' RESIZE 8G;

TEMPFILE 추가

ALTER TABLESPACE [TEMP_TBS_NAME] ADD TEMPFILE '[/TEMPFILE_PATH/TEMPFILE_NAME]' SIZE [SIZE]M ;
 
--
ALTER TABLESPACE TEMP ADD TEMPFILE '/ORADATA/TESTDB/temp03.dbf' SIZE 8G;

 

---

• 일반 TBS

TBS 확인

SELECT *
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '[TBS_NAME]'
ORDER BY FILE_NAME ;

DATAFILE 추가

ALTER TABLESPACE [TBS_NAME] ADD DATAFILE '[/DATAFILE_PATH/DATAFILE_NAME]' SIZE []M ;
 
-- 1. NON-AUTOEXTEND DATAFILE이 있는 상태에서 AUTOEXTEND DATAFILE 추가
 ALTER TABLESPACE USERS ADD DATAFILE '/ORADATA/TESTDB/users03.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 1000M;
 
-- 2. AUTOEXTEND DATAFILE이 있는 상태에서 NON-AUTOEXTEND DATAFILE 추가
 ALTER TABLESPACE USERS ADD DATAFILE '/ORADATA/TESTDB/users04.dbf' SIZE 1000M;

TBS 삭제

DROP TABLESPACE [TBS_NAME] INCLUDING CONTENTS AND DATAFILES ;
 
-- 파일까지 삭제
DROP TABLESPACE TEST INCLUDING CONTENTS AND DATAFILES;

 

---

• 한 개의 DATAFILE의 MAX 용량

Block Size | Maximum Datafile Size

8k                | 4194303 * 8k = 32 GB

 

Database Reference