오라클] 테이블스페이스(Tablespace)와 데이터 파일(Data File)

2017.07.17 07:00


테이블스페이스와 데이터 파일



테이블스페이스(tablespace)는 매장, 데이터 파일(Data File)은 창고에 해당






테이블스페이스와 데이터 파일 조회하기



select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;

1
2
3
4
5
6
7
8
9
10
11
12
SQL> col tablespace_name for a15
SQL> col file_name for a30
SQL> select tablespace_name, bytes/1024/1024 MB, file_name 
from dba_data_files;
 
TABLESPACE_NAME     MB FILE_NAME
--------------- ---------- ------------------------------
USERS                  5 /ora_data/OELORA/users01.dbf
UNDOTBS1             90 /ora_data/OELORA/undotbs01.dbf
SYSAUX               530 /ora_data/OELORA/sysaux01.dbf
SYSTEM               680 /ora_data/OELORA/system01.dbf
EXAMPLE             100 /ora_data/OELORA/example01.dbf
 
cs






테이블스페이스 만들기


- 테이블스페이스가 사용할 데이터 파일 생성


create tablespace 테이블스페이스명 datafile '데이터 파일명' size 파일크기



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> create tablespace scott_tbs datafile '/ora_data/scott_tbs01.dbf' size 2M;
 
Tablespace created.
 
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
 
TABLESPACE_NAME     MB FILE_NAME
--------------- ---------- ------------------------------
USERS                  5 /ora_data/OELORA/users01.dbf
UNDOTBS1             90 /ora_data/OELORA/undotbs01.dbf
SYSAUX               530 /ora_data/OELORA/sysaux01.dbf
SYSTEM               680 /ora_data/OELORA/system01.dbf
EXAMPLE             100 /ora_data/OELORA/example01.dbf
SCOTT_TBS              2 /ora_data/scott_tbs01.dbf
 
6 rows selected.
cs






사용자별 디폴트 테이블스페이스 조회


select username, default_tablespace from dba_users;



1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> select username, default_tablespace from dba_users;
 
USERNAME               DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW               SYSTEM
SYS                   SYSTEM
SYSTEM                   SYSTEM
DBSNMP                   SYSAUX
SYSMAN                   SYSAUX
SCOTT                   USERS
OUTLN                   SYSTEM
FLOWS_FILES               SYSAUX
MDSYS                   SYSAUX
cs




디폴트 테이블스페이스 변경


alter user 사용자 default tablespace 테이블스페이스명;



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> alter user scott default tablespace scott_tbs;
 
User altered.
 
SQL> select username, default_tablespace from dba_users;
 
USERNAME               DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW               SYSTEM
SYS                   SYSTEM
SYSTEM                   SYSTEM
DBSNMP                   SYSAUX
SYSMAN                   SYSAUX
SCOTT                   SCOTT_TBS
 
cs






테이블스페이스에 포함된 테이블명 조회하기


SELECT TABLESPACE_NAME, TABLE_NAME FROM USER_TABLES ;



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> conn scott/tiger
Connected.
SQL> create table emp2 as select * from emp;
 
Table created.
 
SQL> SELECT TABLESPACE_NAME, TABLE_NAME FROM USER_TABLES ;
 
TABLESPACE_NAME TABLE_NAME
--------------- ------------------------------
USERS        DEPT
USERS        EMP
USERS        BONUS
USERS        SALGRADE
SCOTT_TBS    EMP2
 
cs






테이블스페이스(데이터 파일) 사용량 조회



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SQL> ! cat tbs_usage.sql
-- tbs_usage.sql
 
set line 200;
col file# for 999 ;
col ts_name for a10 ;
col total_blocks for 9999999 ;
col used_blocks for 9999999 ;
col pct_used for a10 ;
 select distinct d.file_id          file#,
    d.tablespace_name                  ts_name,
    d.bytes /1024/1024                 MB,
    d.bytes /8192                      total_blocks,
    sum(e.blocks)                      used_blocks,
    to_char(nvl(round(sum(e.blocks)/(d.bytes/8192), 4),0*100'09.99'|| '%' pct_used
    from       dba_extents e, dba_data_files d
    where      d.file_id = e.file_id(+)
    group by d.file_id , d.tablespace_name , d.bytes
    order by 1,2 ;
 
 
SQL> @tbs_usage.sql
 
FILE# TS_NAME         MB TOTAL_BLOCKS USED_BLOCKS PCT_USED
----- ---------- ---------- ------------ ----------- ----------
    1 SYSTEM        680       87040       86840  99.77%
    2 SYSAUX        530       67840       63480  93.57%
    3 UNDOTBS1         90       11520    2368  20.56%
    4 USERS          5         640     392  61.25%
    5 EXAMPLE        100       12800    9952  77.75%
    6 SCOTT_TBS       2         256       8  03.13%
 
6 rows selected.
 
cs





데이터 파일 용량 변경


ㅇ 파일 사이즈 변경(확장 및 축소 가능)


alter database datafile 'dbf 파일이름' resize 파일크기;



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> alter database datafile '/ora_data/scott_tbs01.dbf' resize 5M;
 
Database altered.
 
 
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
 
TABLESPACE_NAME     MB FILE_NAME
--------------- ---------- ------------------------------
USERS              5 /ora_data/OELORA/users01.dbf
UNDOTBS1         90 /ora_data/OELORA/undotbs01.dbf
SYSAUX             530 /ora_data/OELORA/sysaux01.dbf
SYSTEM             680 /ora_data/OELORA/system01.dbf
EXAMPLE            100 /ora_data/OELORA/example01.dbf
SCOTT_TBS          5 /ora_data/scott_tbs01.dbf
 
6 rows selected.
 
cs




ㅇ 데이터 파일 추가


alter tablespace 테이블스페이스명 add datafile '데이터파일명' size 파일크기;



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> alter tablespace scott_tbs add datafile '/ora_data/scott_tbs02.dbf' size 5M;
 
Tablespace altered.
 
 
SQL> select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
 
TABLESPACE_NAME     MB FILE_NAME
--------------- ---------- ------------------------------
USERS              5 /ora_data/OELORA/users01.dbf
UNDOTBS1         90 /ora_data/OELORA/undotbs01.dbf
SYSAUX            530 /ora_data/OELORA/sysaux01.dbf
SYSTEM            680 /ora_data/OELORA/system01.dbf
EXAMPLE           100 /ora_data/OELORA/example01.dbf
SCOTT_TBS          5 /ora_data/scott_tbs01.dbf
SCOTT_TBS          5 /ora_data/scott_tbs02.dbf
 
7 rows selected.
cs





ㅇ 데이터 파일 크기 자동 증가 옵션


alter database datafile '/ora_data/scott_tbs02.dbf' autoextend on;

- 자동 증가 기본 옵션



alter database datafile '/ora_data/scott_tbs02.dbf' autoextend on next 10M maxsize 100M; 

-- maxsize 100M 대신 UNLIMITED를 사용하면 용량 제한 없음;







데이터파일의 autoextend 여부 확인


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> set line 200
SQL> col tablespace_name for a10
SQL> select tablespace_name, bytes/1024/1024 MB, file_name, 
     autoextensible "AUTO", online_status from dba_data_files ;
 
TABLESPACE    MB FILE_NAME               AUT ONLINE_
---------- ---------- ------------------------------ --- -------
USERS          5 /ora_data/OELORA/users01.dbf   YES ONLINE
UNDOTBS1      90 /ora_data/OELORA/undotbs01.dbf YES ONLINE
SYSAUX       530 /ora_data/OELORA/sysaux01.dbf  YES ONLINE
SYSTEM       680 /ora_data/OELORA/system01.dbf  YES SYSTEM
EXAMPLE      100 /ora_data/OELORA/example01.dbf YES ONLINE
SCOTT_TBS      5 /ora_data/scott_tbs01.dbf      NO  ONLINE
SCOTT_TBS      5 /ora_data/scott_tbs02.dbf      YES ONLINE
 
7 rows selected.
 
cs








또루아빠 SQL(오라클,mysql) , , , , , , , , ,