Admin 실습 02: 테이블스페이스

실습 스크립트 전체 보기: https://github.com/nsylove97/Seongryeol-OracleDB-Portfolio

실습 환경

OS	Oracle Linux 7.9 (VMware Virtual Machine)
DB	Oracle Database 19c
접속 툴	SQL*Plus, MobaXterm(SSH)

 

1. 딕셔너리 뷰 조회 실습 

 

-- DB에 존재하는 모든 데이터파일 이름(경로) 조회
SELECT NAME FROM V$DATAFILE;

데이터 파일들의 경로

-- 테이블스페이스 목록 조회
SELECT TABLESPACE_NAME, STATUS, CONTENTS
FROM DBA_TABLESPACES;

-- 테이블스페이스별 데이터파일 조회
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB
FROM DBA_DATA_FILES;

테이블스페이스 목록

테이블스페이스별 데이터파일 목록

2. Permanent Tablespace 생성

 

테이블스페이스는 반드시 1개 이상의 데이터파일이 필요함.

 

기본 생성

-- Locally Managed Tablespace (LMT) 방식으로 생성 (default)
CREATE TABLESPACE inventory
DATAFILE '/u01/app/oracle/oradata/ORCL/inventory01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL          -- LMT 방식 (기본값)
AUTOALLOCATE;                    -- Oracle이 자동으로 extent 크기 결정

inventory 테이블스페이스 생성

LMT 방식으로 생성된 것 확인 가능

 

익스텐트 할당 방식 비교해서 생성해보기

-- UNIFORM: 익스텐트를 항상 동일한 크기로 할당
CREATE TABLESPACE sample_uniform
DATAFILE '/u01/app/oracle/oradata/ORCL/sample_uni01.dbf' SIZE 100M
UNIFORM SIZE 1M;                 -- 1MB씩 동일하게 할당

-- AUTOALLOCATE: 오라클이 자동으로 적절한 크기 결정 (default)
CREATE TABLESPACE sample_auto
DATAFILE '/u01/app/oracle/oradata/ORCL/sample_auto01.dbf' SIZE 100M
AUTOALLOCATE;

 

uniform으로 sample_uniform 테이블스페이스 생성

autoallocate로 sample_auto 테이블스페이스 생성

sample_unifom은 next_extent가 1mb, sample_auto는 공란(자동 할당)

 

AUTOEXTEND 옵션 — 데이터파일 자동 크기 확장

-- 데이터파일이 꽉 차면 자동으로 늘어나도록 설정
CREATE TABLESPACE inventory2
DATAFILE '/u01/app/oracle/oradata/ORCL/inventory02.dbf' SIZE 100M
AUTOEXTEND ON                    -- 자동 확장 켜기
NEXT 50M                         -- 한 번에 50MB씩 늘어남
MAXSIZE 500M;                    -- 최대 500MB까지만

inventory2 테이블스페이스 생성

dba_data_files 뷰로 확인

3. 용량 부족 상황 재현 & 데이터파일 추가 실습

 

용량 부족 상황 만들기

-- HR의 employees 테이블을 inventory 테이블스페이스에 복사
-- (반복 INSERT로 용량 부족 상황 재현)
CREATE TABLE emp3
  TABLESPACE inventory
  AS SELECT * FROM hr.employees;

-- 용량이 찰 때까지 반복 INSERT
INSERT INTO emp3 SELECT * FROM emp3;
INSERT INTO emp3 SELECT * FROM emp3;
INSERT INTO emp3 SELECT * FROM emp3;
-- ORA-01653: unable to extend table 에러 발생 확인

emp3 테이블 생성

용량이 찰 때까지 insert 반복하면 ORA-01653 오류 발생

 

데이터파일 추가로 용량 확장

-- 기존 테이블스페이스에 새 데이터파일 추가
ALTER TABLESPACE inventory
ADD DATAFILE '/oradata/orcl/inventory01_1.dbf' SIZE 100M;

-- 추가 확인
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'INVENTORY';

추가된 데이터 파일 확인

 

4. Default Tablespace 생성 & 유저 배정 실습

-- 1. 새 테이블스페이스 생성
CREATE TABLESPACE userdata
DATAFILE '/u01/app/oracle/oradata/ORCL/userdata01.dbf' SIZE 200M;

-- 2. DB 전체의 기본 테이블스페이스로 지정
ALTER DATABASE DEFAULT TABLESPACE userdata;

-- 3. 해당 테이블스페이스에 새 유저 생성
CREATE USER uduser
IDENTIFIED BY uduser
DEFAULT TABLESPACE userdata        -- 기본 저장 공간
TEMPORARY TABLESPACE temp;         -- 임시 작업 공간

-- 4. 유저와 테이블스페이스 배정 확인
SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
FROM DBA_USERS
WHERE USERNAME = 'UDUSER';

uduser와 테이블스페이스가 userdata로 된 것 확인

 

5. Tablespace OFFLINE / READ ONLY 전환 실습

 

OFFLINE 실습

-- 테이블스페이스 OFFLINE (백업/유지보수 시 사용)
ALTER TABLESPACE inventory OFFLINE;

-- OFFLINE 상태 확인
SELECT TABLESPACE_NAME, STATUS
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = 'INVENTORY';

-- OFFLINE 상태에서 접근 시도 → 에러 확인
SELECT * FROM emp3;
-- ORA-00376: file ... cannot be read at this time 에러 발생

-- 다시 ONLINE으로 복구
ALTER TABLESPACE inventory ONLINE;

-- ONLINE 확인
SELECT TABLESPACE_NAME, STATUS
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = 'INVENTORY';

inventory 테이블스페이스를 offline으로 바꾸면 테이블 emp3 조회 불가

online으로 다시 바꾸면 조회 가능

READ ONLY 전환 실습

-- READ ONLY 전환 (SELECT만 가능, DML/DDL 불가)
ALTER TABLESPACE inventory READ ONLY;

-- READ ONLY 상태 확인
SELECT TABLESPACE_NAME, STATUS
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = 'INVENTORY';

-- SELECT는 가능한지 확인
SELECT COUNT(*) FROM emp3;  -- 가능

-- DML 시도 → 에러 확인
INSERT INTO emp3 SELECT * FROM hr.employees;
-- ORA-00372: file ... cannot be modified at this time

-- READ WRITE로 복구
ALTER TABLESPACE inventory READ WRITE;

inventory 테이블스페이스를 read only로 바꾸면 select는 가능, insert는 불가능

 

SYSTEM 테이블스페이스는 OFFLINE, DROP, READ ONLY 전환 불가

-- 에러 확인
ALTER TABLESPACE system OFFLINE;
-- ORA-01544: cannot offline system tablespace

ALTER TABLESPACE system READ ONLY;
-- ORA-01643: system tablespace can not be made read only

system 테이블스페이스는 offline, read only로 전환 불가

 

6. 테이블스페이스 DROP 실습

-- 방법 1: 테이블스페이스만 삭제 (OS상의 데이터파일은 남음)
DROP TABLESPACE sample_uniform;

-- OS에서 데이터파일이 남아있는지 확인
!ls -l /u01/app/oracle/oradata/ORCL/sample_uni01.dbf   -- 파일은 여전히 존재

sample_uniform 테이블스페이스를 drop해도 OS상의 데이터파일은 남아있음

-- 방법 2: 테이블스페이스 + 데이터파일까지 완전 삭제
DROP TABLESPACE sample_auto
INCLUDING CONTENTS AND DATAFILES;
  -- INCLUDING CONTENTS: 안에 있는 오브젝트도 함께 삭제
  -- AND DATAFILES: OS상의 실제 파일도 함께 삭제
  
-- 삭제됐는지 확인
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = 'SAMPLE_AUTO';   -- 조회 안되면 정상

including contents and datafiles 로 sample_auto 테이블스페이스는 완전 삭제됨

-- 방법 3: 데이터파일만 따로 삭제
ALTER TABLESPACE inventory
DROP DATAFILE '/u01/app/oracle/oradata/ORCL/inventory01_1.dbf';

inventory 테이블스페이스에 추가한 데이터파일만 따로 삭제 가능

7. Temporary Tablespace 생성 실습

 

Temporary Tablespace는 ORDER BY, GROUP BY, JOIN, CREATE INDEX 등 정렬 작업이 메모리를 초과할 때 자동으로 사용됨.

-- Temporary Tablespace 생성
-- 데이터파일이 아닌 tempfile을 사용한다는 점이 다름
CREATE TEMPORARY TABLESPACE mytemp
TEMPFILE '/u01/app/oracle/oradata/ORCL/mytemp01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 50M MAXSIZE 500M
UNIFORM SIZE 1M;

-- 생성 확인
SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = 'MYTEMP';

-- 또는
SELECT NAME FROM V$TEMPFILE;

-- Temporary Tablespace에는 영구 오브젝트 생성 불가 확인
CREATE TABLE test_table (id NUMBER) TABLESPACE mytemp;
-- ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace 에러 발생

temporary tablespace 'mytemp' 생성

mytemp 생성된 것 확인

mytemp에는 영구 오브젝트 생성 불가

Temporary Tablespace Group 생성

-- 여러 Temporary Tablespace를 그룹으로 묶기
CREATE TEMPORARY TABLESPACE mytemp2
TEMPFILE '/u01/app/oracle/oradata/ORCL/mytemp02.dbf' SIZE 100M
TABLESPACE GROUP tempgroup;        -- 그룹에 추가

-- 기존 mytemp도 같은 그룹에 추가
ALTER TABLESPACE mytemp
TABLESPACE GROUP tempgroup;

-- 그룹 확인
SELECT * FROM DBA_TABLESPACE_GROUPS;

-- 그룹을 Default Temporary Tablespace로 지정
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempgroup;

-- DB 기본값 확인
SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

mytemp2를 만들어 tempgroup에 추가하고, 기존 mytemp도 tempgroup에 추가한 뒤 확인

tempgroup을 default temporary tablespace로 변경 후 확인

 

8. OMF (Oracle-Managed Files) 실습

 

OMF를 설정하면 파일 경로/이름을 직접 지정하지 않아도 Oracle이 자동으로 관리.

 

OMF 경로 설정

-- OMF 설정 확인
SHOW PARAMETER db_create_file_dest;

-- 데이터파일 자동 생성 경로 설정
ALTER SYSTEM SET db_create_file_dest = '/u01/app/oracle/oradata/ORCL' SCOPE=BOTH;

db_create_file_dest 파라미터 확인 후 OMF 경로 설정

 

OMF 적용 확인 — 파일 경로 없이 테이블스페이스 생성

-- 파일명/경로를 전혀 지정하지 않아도 Oracle이 자동으로 생성
CREATE TABLESPACE omf_test
DATAFILE SIZE 100M;              -- 경로/이름 없이 크기만 지정

-- Oracle이 자동 생성한 파일 경로 확인
SELECT TABLESPACE_NAME, FILE_NAME
FROM DBA_DATAFILES
WHERE TABLESPACE_NAME = 'OMF_TEST';
-- /u01/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_omf_test_xxxxx.dbf 형태로 자동 생성

-- OS에서 실제 파일 생성 확인
!ls -la /u01/app/oracle/oradata/orcl/ORCL/datafile/

-- 테이블스페이스 DROP 시 파일도 자동 삭제 확인
DROP TABLESPACE omf_test 
INCLUDING CONTENTS AND DATAFILES;

-- 파일이 사라졌는지 OS에서 확인
!ls -la /u01/app/oracle/oradata/orcl/ORCL/datafile/

 

omf_test 테이블스페이스 생성

OMF로 생성된 파일 확인

drop including contents and datafile로 omf_test는 완전히 사라짐

---

실습 핵심 요약

주제	핵심 명령어
테이블스페이스 생성	CREATE TABLESPACE ... DATAFILE ...
데이터파일 추가	ALTER TABLESPACE ... ADD DATAFILE ...
데이터파일 삭제	ALTER TABLESPACE ... DROP DATAFILE ...
OFFLINE/ONLINE	ALTER TABLESPACE ... OFFLINE/ONLINE
READ ONLY/WRITE	ALTER TABLESPACE ... READ ONLY/WRITE
완전 삭제	DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES
Temp Tablespace	CREATE TEMPORARY TABLESPACE ... TEMPFILE ...
Temp 확인 뷰	DBA_TEMP_FILES, V$TEMPFILE
OMF 설정	ALTER SYSTEM SET db_create_file_dest = '경로'