close
프로필 배경
프로필 로고

Information Security Consultant Develop

DB · 2025. 11. 18. fullscreen 넓게보기

Oracle - 데이터 파일(Data File), 테이블스페이스(Tablespace)

1. 테이블스페이스(Tablespace) 및 데이터 파일(Data File)

 

데이터베이스, 테이블스페이스 및 데이터 파일은 밀접하게 연관되어 있으나 중요한 차이점들이 존재한다. 데이터베이스는 논리적 저장 공간 단위인 하나 이상의 테이블스페이스로 구성되며, 이 테이블스페이스는 다시 운영체제의 물리적 파일인 하나 이상의 데이터 파일로 구성되어 데이터베이스의 모든 데이터를 최종적으로 저장하는 계층적 구조를 가진다. 

 

테이블스페이스 - 테이블스페이스를 만들 때 데이터 파일이 생성되고 테이블스페이스를 삭제하면 데이터 파일도 삭제
-  테이블스페이스는 데이터베이스의 논리적 저장단위이다.
- 사용자가 생성하는 테이블, 인덱스 등 모든 객체는 반드시 어떤 테이블스페이스에 저장된다.
- 테이블스페이스 1개는 여러 개의 데이터 파일로 구성될 수 있다. 
- 사용자는 테이블스페이스를 사용 
데이터 파일 - 데이터 파일은 실젤 운영체제 파일 시스템에 존재하는 물리적 파일이다.
- 테이블스페이스와 연결되어 있으며, 데이터는 최종적으로 이 파일에 저장한다.
- 테이블스페이스의 저장 공간을 확장하고 싶다면 데이터파일을 추가하거나 크기를 늘린다.
- 1개 데이터 파일은 1개의 테이블스페이스만 연결된다.

 

2. 테이블스페이스 유형

유형 정의 및 특징
SYSTEM 테이블스페이스 - 모든 데이터베이스에서 사용되는 기본 임시 테이블스페이스이다.
- 데이터베이스 운영에 필수적이며 만들거나 삭제 불가
- 데이터 딕셔너리 및 핵심 메타데이터를 저장한다.
- 데이터베이스 생성 시 만들어진다.
- open단계에서 데이터 딕셔너리를 볼 수 있다.
SYSAUX 테이블스페이스 - SYSTEM 테이블스페이스의 부하를 줄이기 위해 도입된 보조 테이블스페이스다.
- 관리용 스페이스
-  SYSTEM과 함께 존재해야 한다.
UNDO 테이블스페이스 - 트랜잭션을 되돌리거나 읽기 일관성을 제공하기 위한 Undo 데이터를 저장하는 공간
- 저장 내용은 트랜잭션 롤백, 읽기 일관성을 위한 정보
- 복구와 트랜잭션 관리에 핵심적인 역할을 한다.
- 1개만 만들어 사용하는게 일반적이다.
TEMPORARY 테이블스페이스 - 정렬, 조인 등 작업 중 임시 데이터를 저장하는 테이블스페이스이다.
- 영구적으로 데이터 저장은 안한다.
- 세션 종료 시 데이터가 사라진다.
- 보통 1개 만들어서 여러 사용자에게 공유해서 사용한다.
PERMANENT 테이블스페이스 - 일반 사용자 객체를 저장하기 위해 생성되는 테이블스페이스이다.
- 실제 업무 데이터가 저장된다.
- 관리자가 직접 생성 가능하다.
- 여러 개 생성가능하다.

 

 

3. 데이터파일 블록(block) 단위 저장

 

데이터파일은 블록 단위로 구성되어 있으며, extent(biock을 붙여서 줌) 단위로 세그먼트에 할당한다. 이에 세그먼트는 행을 저장한다. 세그먼트는 블록을 점유해서 사용하는 객체를 말하고, extent는 연속된 블록들의 집합이다. extent의 첫번 째는 세그먼트의 헤더 블록이다. 블록을 가득채어서 쓰지않는 이유는 가득채워서 쓰면 행이 여러블럭에 저장되는 현상이 나타날 수 있어 H/M(경계선 느낌)가 뒤로 밀리는 현상이 발생한다. 이는 업데이트 명령어를 쓰면 이러한 상황이 일어나기때문에 블럭의 빈 공간을 만들어 놓는다. 블록은 bitmap을 만들어서 관리한다. 

 

 

4. Tablespace와 Data file 상태 조회

=======================================================================

SQL> SELECT tablespace_name, status, contents,    ▶ 테이블스페이스의 상태를 조회한다.

2 extent_management, segment_space_management

3 FROM dba_tablespaces

4 ORDER BY 1;

 

TABLESPACE_NAME  STATUS  CONTENTS  EXTENT_MAN   SEGMEN

---------------- -------- --------- ---------- -----

SYSAUX  ONLINE  PERMANENT  LOCAL  AUTO

SYSTEM  ONLINE  PERMANENT  LOCAL  MANUAL

TEMP  ONLINE  TEMPORARY  LOCAL  MANUAL

UNDOTBS1  ONLINE  UNDO  LOCAL  MANUAL

USERS  ONLINE  PERMANENT  LOCAL  AUTO

 

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files  ▶ 데이터파일의 상태를 조회한다.

2 ORDER BY 1;

 

TABLESPACE_NAME BYTES FILE_NAME

---------------- ---------- ---------------------------------------

SYSAUX 482344960 /app/ora19c/oradata/DB19/sysaux01.dbf

SYSTEM 838860800 /app/ora19c/oradata/DB19/system01.dbf

UNDOTBS1 68157440 /app/ora19c/oradata/DB19/undotbs01.dbf

USERS 5242880 /app/ora19c/oradata/DB19/users01.dbf

 

SQL> SELECT tablespace_name, bytes, file_name FROM dba_temp_files;. ▶ temp 파일의 상태를 조회한다.

 

TABLESPACE_NAME BYTES FILE_NAME

--------------- ---------- --------------------------------------------

TEMP 33554432 /app/ora19c/oradata/DB19/temp01.dbf

 

SQL> SELECT ts#, file#, name FROM v$datafile;

 

TS# FILE# NAME

---------- ---------- ---------------------------------------

0 1 /app/ora19c/oradata/DB19/system01.dbf

1 3 /app/ora19c/oradata/DB19/sysaux01.dbf

2 4 /app/ora19c/oradata/DB19/undotbs01.dbf

4 7 /app/ora19c/oradata/DB19/users01.dbf

 

SQL> SELECT ts#, name FROM v$tablespace;

TS#      NAME

---------- ---------------------------------------

1           SYSAUX

0           SYSTEM

2           UNDOTBS1

4           USERS

3            TEMP

 

 

SQL> SELECT t.name tablespace_name, d.bytes, d.name file_name ▶ 검색 환경이 MOUNT 이상에서 모두 조회가 가능

2 FROM v$tablespace t, v$datafile d 

3 WHERE t.ts#=d.ts#

4 ORDER BY 1;

 

TABLESPACE_NAME BYTES FILE_NAME

---------------- ---------- ---------------------------------------

SYSAUX 482344960 /app/ora19c/oradata/DB19/sysaux01.dbf

SYSTEM 838860800 /app/ora19c/oradata/DB19/system01.dbf

UNDOTBS1 68157440 /app/ora19c/oradata/DB19/undotbs01.dbf

USERS 5242880 /app/ora19c/oradata/DB19/users01.dbf

=======================================================================

 

 

5. 테이블스페이스 생성 

=======================================================================

SQL> CREATE TABLESPACE insa  ▶ 사용중인 버전이 10g, 12c인 경우 테이블스페이스 생성 명령

2 DATAFILE '/app/ora19c/oradata/DB19/insa01.dbf' SIZE 1M; 

tablespace created.

 

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files;

▶ 생성된 테이블스페이스의 설정과 데이터 파일을 확인

 

TABLESPACE_NAME BYTES FILE_NAME

---------------- ---------- ---------------------------------------

USERS 5242880 /app/ora19c/oradata/DB19/users01.dbf

UNDOTBS1 68157440 /app/ora19c/oradata/DB19/undotbs01.dbf

SYSTEM 838860800 /app/ora19c/oradata/DB19/system01.dbf

SYSAUX 482344960 /app/ora19c/oradata/DB19/sysaux01.dbf

INSA 1048576 /app/ora19c/oradata/DB19/insa01.dbf

 

SQL> SELECT tablespace_name, status, contents,

2 extent_management, segment_space_management

3 FROM dba_tablespaces

4 ORDER BY 1;

 

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN

---------------- --------- --------- ---------- ----- 

INSA ONLINE PERMANENT LOCAL AUTO

SYSAUX ONLINE PERMANENT LOCAL AUTO

........

=======================================================================

 

6. 테이블스페이스 확장 

==========================================================================

SQL> ALTER DATABASE DATAFILE ▶ 원래 크기가 1M인 data file의 크기를 2M로 늘린다.

2 '/app/ora19c/oradata/DB19/insa01.dbf' RESIZE 2M;

Database altered.

 

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files; ▶ 데이터파일 조회

 

SQL> ALTER TABLESPACE insa ▶  tablespace에 2M 크기의 data file을 추가 한다.

2 ADD DATAFILE '/app/ora19c/oradata/DB19/insa02.dbf' SIZE 2M;

tablespace altered.

 

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files; ▶ 데이터파일 조회

 

 

 

SQL> CREATE TABLESPACE usr  ▶ 각각 설정이 서로 다른 세 개의 data file을 추가 한다. 크기가 자동으로

2 DATAFILE                                                     증가하는 autoextend 설정은 data file 별로 따로 설정한다.

3 '/app/ora19c/oradata/DB19/usr01.dbf' SIZE 2M AUTOEXTEND ON,

4 '/app/ora19c/oradata/DB19/usr02.dbf' SIZE 2M AUTOEXTEND ON NEXT 5M,

5 '/app/ora19c/oradata/DB19/usr03.dbf' SIZE 2M AUTOEXTEND ON NEXT 5M MAXSIZE 20M;
tablespace created.

 

SQL> ALTER TABLESPACE insa ▶ data file의 자동 증가는 data file 각각의 설정이므로 기존 tablespace에 자유롭게 추가할 수 있다.

2 ADD DATAFILE '/app/ora19c/oradata/DB19/insa03.dbf' SIZE 2M

3 AUTOEXTEND ON NEXT 5M MAXSIZE 20M;

===========================================================================

 

7. 테이블스페이스 삭제 

=======================================================================

 

SQL> DROP TABLESPACE users; ▶ 10g 버전에 추가된 defaule permanent tablespace로 지정된 tablespace는 삭제되지                                                                않는다.

 *

ERROR at line 1:

ORA-12919: Can not drop the default permanent tablespace

 

SQL> SELECT * FROM database_properties WHERE property_name LIKE '%DEFAULT_PERMAN%';

 

PROPERTY_NAME  PROPERTY_V  DESCRIPTION

------------------------------ ---------- ---------------------------------------

DEFAULT_PERMANENT_TABLESPACE  USERS  Name of default permanent tablespace

 

SQL> DROP TABLESPACE insa; ▶ 비어있는 tablespace만 삭제가 가능하다.

tablespace dropped.

 

SQL> !ls /app/ora19c/oradata/DB19/  ▶ tablespace를 삭제해도 data file이 OS상에서 지워지지 않는다.

insa01.dbf insa03.dbf system01.dbf undotbs01.dbf usr01.dbf usr03.dbf insa02.dbf sysaux01.dbf temp01.dbf users01.dbf usr02.dbf

 

SQL> !rm /app/ora19c/oradata/DB19/insa01.dbf 

SQL> !rm /app/ora19c/oradata/DB19/insa02.dbf  ▶ data file은 OS명령을 통해 직접 지워준다.

SQL> !rm /app/ora19c/oradata/DB19/insa03.dbf

 

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files;

 

 

TABLESPACE_NAME  BYTES  FILE_NAME

---------------- ---------- ---------------------------------------

SYSAUX 482344960 /app/ora19c/oradata/DB19/sysaux01.dbf

SYSTEM 838860800 /app/ora19c/oradata/DB19/system01.dbf 

..............

7 rows selected.

 

SQL> DROP TABLESPACE usr;

Tablespace dropped.

 

SQL> !rm /app/ora19c/oradata/DB19/usr*

 

SQL> !ls /app/ora19c/oradata/DB19/

sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf

=======================================================================

 

8. Tablespace READ ONLY

 

사용 명령어 ▶ SQL> ALTER TABLESPACE [ READ ONLY / READ WRITE ]

- 지정한 tablespace를 읽기 전용(읽기 쓰기) 으로 변경한다. 

- READ ONlY 일때 트랜잭션 명령어는 안된다. 

- 조회(SELECT) 만 가능하다.

- READ ONLY를 사용했을 때 DROP은 가능하다 이유는 파일을 고치지는 못하지만 없는 파일이라고 보여주는 것이 가능해서 그렇게 보이는 것이며, DROP TABLE은 실제 데이터를 수정하는 게 아니라, 데이터 사전(딕셔너리)을 수정하는 작업이다. 

 

=======================================================================

 

SQL> CREATE USER st

2 IDENTIFIED BY st

3 DEFAULT TABLESPACE users

4 QUOTA UNLIMITED ON users;

 

SQL> GRANT connect, resource TO st;

 

SQL> SELECT username, account_status, default_tablespace 

2 FROM dba_users

3 WHERE username = 'ST';

 

USERNAME  ACCOUNT_STATUS      DEFAULT_TA

----------       --------------------                   ----------                   ◀ 만일 account_status가 EXPIRED & LOCKED 라면 계정의                                                                                                        lock을 풀어줘야 한다.

ST                 OPEN                                USERS

 

SQL> CREATE TABLE st.test (no NUMBER);

Table created.

 

SQL> SELECT owner, table_name, tablespace_name FROM dba_tables

2 WHERE owner = 'ST' AND table_name = 'TEST';

 

OWNER TABLE_NAME TABLESPACE_NAME

---------------- ---------- ---------------

ST               TEST     USERS

 

SQL> INSERT INTO st.test VALUES (10);

1 row created.

 

SQL> COMMIT;

commit complete.

 

SQL> SELECT * FROM st.test;

NO

---------

10

 

SQL> ALTER TABLESPACE users READ ONLY;  ◀ Users tablespace를 읽기 전용으로 변경 

 Tablespace altered.

 

SQL> SELECT tablespace_name, status, contents,

2 extent_management, segment_space_management

3 FROM dba_tablespaces

4 ORDER BY 1;

 

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN

---------------- --------- --------- ---------- -----

SYSAUX ONLINE PERMANENT LOCAL AUTO

SYSTEM ONLINE PERMANENT LOCAL MANUAL

TEMP ONLINE TEMPORARY LOCAL MANUAL

UNDOTBS1 ONLINE UNDO LOCAL MANUAL

USERS READ ONLY PERMANENT LOCAL AUTO

 

SQL> CONN st/st

Connected.

 

SQL> INSERT INTO test VALUES (20);        ◀  읽기 전용으로 변경했을 때 테이블스페이스의 테이블에 대한 작업 불가능

. INSERT INTO test VALUES (20)

*

ERROR at line 1:

ORA-00372: file 7 cannot be modified at this time

ORA-01110: data file 7: '/app/ora19c/oradata/disk3/users01.dbf'

 

SQL> DELETE FROM test;

DELETE FROM st.test

*

ERROR at line 1:

ORA-00372: file 7 cannot be modified at this time

ORA-01110: data file 4: '/app/ora19c/oradata/disk3/users01.dbf'

 

SQL> SELECT * FROM test;

 

NO

---------

10

 

SQL> DROP TABLE test;  ◀ 읽기 전용일 때 테이블스페이스의 테이블은 삭제 가능 

Table dropped.

 

SQL> SELECT * FROM tab WHERE tname = 'TEST';

no rows selected

 

SQL> CONN / AS sysdba

Connected.

 

SQL> ALTER TABLESPACE users READ WRITE;  ◀  테이블스페이스를 정상으로 되돌리는 작업 

tablespace altered.

 

SQL> SELECT tablespace_name, status, contents,

2 extent_management, segment_space_management

3 FROM dba_tablespaces

4 ORDER BY 1;

 

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN SEGMEN

---------------- --------- --------- ---------- -----

SYSAUX ONLINE PERMANENT LOCAL AUTO

SYSTEM ONLINE PERMANENT LOCAL MANUAL

TEMP ONLINE TEMPORARY LOCAL MANUAL

UNDOTBS1 ONLINE UNDO LOCAL MANUAL

USERS ONLINE PERMANENT LOCAL AUTO

=======================================================================

 

 

 

9. OPEN 상태에서 tablespace 이동 

 

=======================================================================

SQL> ALTER TABLESPACE users OFFLINE; ◀ 이동 하고자 하는 tablespace를 먼저 OFFLINE 시킨다. (step1)

tablespace altered.

 

 

SQL> SELECT tablespace_name, status, contents,

2 extent_management, segment_space_management   ◀  tablespace의 OFFLINE을 확인한다. (step2)

3 FROM dba_tablespaces

4 ORDER BY 1;

 

 

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files; ◀ OFFLINE 된 tablespace는 data file의 크기가 표시되지 않는다.

 

SQL> SELECT t.name tablespace_name, d.bytes, d.name file_name

2 FROM v$tablespace t, v$datafile d               ◀ OFFLINE 된 tablespace는 data file의 크기가 0으로 표시된다.

3 WHERE t.ts#=d.ts#

4 ORDER BY 1;

 

 

SQL> !ls /app/ora19c/oradata/DB19/

system01.dbf undotbs01.dbf sysaux01.dbf temp01.dbf users01.dbf

 

SQL> !mv /app/ora19c/oradata/DB19/users01.dbf /app/ora19c/oradata/disk3/  ◀   data file을 이동한다. (step3)

 

SQL> !ls /app/ora19c/oradata/disk3/

users01.dbf

 

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files;

 

TABLESPACE_NAME BYTES    FILE_NAME

----------------                   ---------- ---------------------------------------

SYSAUX                  482344960  /app/ora19c/oradata/DB19/sysaux01.dbf

SYSTEM                  838860800 /app/ora19c/oradata/DB19/system01.dbf

UNDOTBS1             68157440  /app/ora19c/oradata/DB19/undotbs01.dbf

USERS                                      /app/ora19c/oradata/DB19/users01.dbf    ◀  데이터 파일이 이동 되었으나 디렉터리 정보는 수정 되지 않았다. 

 

SQL> ALTER TABLESPACE users RENAME DATAFILE ◀ data file의 등록 정보를 수정 한다. (step4)

2 '/app/ora19c/oradata/DB19/users01.dbf'

3 TO '/app/ora19c/oradata/disk3/users01.dbf';

Tablespace altered.

 

SQL> SELECT tablespace_name, bytes, file_name FROM dba_data_files;

 

TABLESPACE_NAME BYTES FILE_NAME

---------------- ---------- ---------------------------------------

SYSAUX 482344960 /app/ora19c/oradata/DB19/sysaux01.dbf

SYSTEM 838860800 /app/ora19c/oradata/DB19/system01.dbf

UNDOTBS1 68157440 /app/ora19c/oradata/DB19/undotbs01.dbf

USERS /app/ora19c/oradata/disk3/users01.dbf  ◀ 데이터 파일 경로 수정된거 확인 

 

 

SQL> ALTER TABLESPACE users ONLINE; ◀ 이동된 tablespace를 ONLINE 한다. (step5)

Tablespace altered.

 

SQL> ALTER TABLESPACE system OFFLINE; ◀ System이나 undo용 tablespace는 OFFLINE되지 않는다.

ALTER TABLESPACE system OFFLINE ◀ 오류 뜸 

=======================================================================

 

10. Close 상태에서 tablespace 이동 

 

step 1. DB SHUTDOWN

step 2. DATA FILE 이동

step 3. DB MOUNT

step 4. ALTER DATABASE 명령으로 DATA FILE 등록

step 5. DB OPEN

=======================================================================

 

SQL> SHUTDOWN IMMEDIATE ◀ DB를 SHUTDOWN 한다.(step1)

Database closed.

Database dismounted.

Oracle instance shutdown.

 

SQL> !mv /app/ora19c/oradata/DB19/sysaux01.dbf /app/ora19c/oradata/disk3/

SQL> !mv /app/ora19c/oradata/DB19/system01.dbf /app/ora19c/oradata/disk3/  ◀ DATA FILE을 이동한다. (step 2)

SQL> !mv /app/ora19c/oradata/DB19/undotbs01.dbf /app/ora19c/oradata/disk3/

SQL> !mv /app/ora19c/oradata/DB19/temp01.dbf /app/ora19c/oradata/disk3/

 

SQL> STARTUP MOUNT  ◀ DB를 MOUNT 한다. (step3)

ORACLE instance started.

 

SQL> ALTER DATABASE RENAME FILE                 

2 '/app/ora19c/oradata/DB19/sysaux01.dbf'                ◀ 데이터파일은 이동했는데 아직 오라클은 파일이 이전 경로에 위치해서 3 TO '/app/ora19c/oradata/disk3/sysaux01.dbf';              경로를 바꿔준다.

Database altered. 

 

SQL> ALTER DATABASE RENAME FILE

2 '/app/ora19c/oradata/DB19/system01.dbf'

3 TO '/app/ora19c/oradata/disk3/system01.dbf';

Database altered.

 

SQL> ALTER DATABASE RENAME FILE

2 '/app/ora19c/oradata/DB19/undotbs01.dbf'

3 TO '/app/ora19c/oradata/disk3/undotbs01.dbf';

Database altered.

 

SQL> ALTER DATABASE RENAME FILE

2 '/app/ora19c/oradata/DB19/temp01.dbf'

3 TO '/app/ora19c/oradata/disk3/temp01.dbf';

Database altered.

 

 

SQL> SELECT t.name tablespace_name, d.bytes,d.name file_name ◀ temporary tablespace의 파일은 data file이 아니라

2 FROM v$tablespace t, v$tempfile d                                                       temp file로 9i 버전부터 별도로 관리 된다.

3 WHERE t.ts#=d.ts#

4 ORDER BY 1;

 

SQL> SELECT name FROM v$controlfile; 

NAME

---------------------------------------                  ◀ 데이터 파일이외에 컨트롤 파일이나 리두 로그 파일이 어디에 위치하는지 확인하고

/app/ora19c/oradata/disk4/control.ctl            불필요한 DB19 디렉터리를 삭제한다. 

/app/ora19c/oradata/disk5/control.ctl

 

 

SQL> SELECT member FROM v$logfile;

MEMBER

-------------------------------------

/app/ora19c/oradata/disk4/redo01.log

/app/ora19c/oradata/disk5/redo01.log

/app/ora19c/oradata/disk4/redo02.log

/app/ora19c/oradata/disk5/redo02.log

/app/ora19c/oradata/disk4/redo03.log

/app/ora19c/oradata/disk5/redo03.log

 

SQL> !rm -rf /app/ora19c/oradata/DB19

 

SQL> !ls /app/ora19c/oradata/

disk1 disk2 disk3 disk4 disk5

 

SQL> ALTER DATABASE OPEN; ◀  DB를 OPEN 한다. (step5)

Database altered.

=======================================================================

 

'DB' 카테고리의 다른 글

Oracle - 권한 관리 및 롤(ROLE)  (0) 2025.12.08
Oracle - TEMPORARY Tablespace  (1) 2025.11.18
Oracle - Control file 관리 , Redo log file 관리  (0) 2025.11.14
Oracle-파라미터 파일  (0) 2025.11.14
Oracle 구조 구성 요소  (1) 2025.11.14
DB 관련 글
더 보기

티스토리툴바