4. 테이블스페이스

Oracle TableSpace Management.

오라클에서 OS의 파일과 가장가까운 관련이 있는 테이블스페이스는
MSSQL 의 DB 또는 MYSQL의 DB라고 봐야할지도 모르겠다.

오라클은 다른 DBMS와는 달리 보통은 1개의 머신에 1개의 instance에서 여러개의 테이블스페이스를 생성해서 관리하는
형태로 사용이 된다.


1. 오라클의 테이블 스페이스를 관리할때 사용하는 뷰들.

오라클에서 무언가가 변경이되면 딕셔너리정보에 저장이 된다.
실시간 정보는 물론 v$ gv$ x$ 등의 dynamic performance view 로 보긴하지만
테이블스페이스의 정보는 큰 변경사항이라 바로바로 dictionary 에 반영이 되나보다.

ㄱ. DBA_TABLESPACES
기본적인 테이블스페이스의 정보를 볼수있는 dictionary 정보.

select     TABLESPACE_NAME,     STATUS,     EXTENT_MANAGEMENT,     SEGMENT_SPACE_MANAGEMENT From     DBA_TABLESPACES; TABLESPACE_NAME                STATUS    EXTENT_MAN SEGMEN ------------------------------ --------- ---------- ------ SYSTEM                                 ONLINE    LOCAL      MANUAL UNDOTBS1                              ONLINE    LOCAL      MANUAL SYSAUX                                  ONLINE    LOCAL      AUTO TEMP                                     ONLINE    LOCAL      MANUAL USERS                                    ONLINE    LOCAL      AUTO EXAMPLE                                ONLINE    LOCAL      AUTO GOOGLE                                 ONLINE    LOCAL      AUTO TEMPS02                                ONLINE    LOCAL      MANUAL 8 rows selected. SQL>

ㄴ. DBA_DATA_FILES
실제 테이블스페이스의 OS파일 경로.

column TABLESPACE_NAME format a20; column FILE_NAME format a50; select     FILE_NAME,     TABLESPACE_NAME from     DBA_DATA_FILES; FILE_NAME                                                            TABLESPACE_NAME -------------------------------------------------- -------------------- /u01/app/oracle/oradata/test01/undotbs01.dbf           UNDOTBS1 /u01/app/oracle/oradata/test01/sysaux01.dbf            SYSAUX /u01/app/oracle/oradata/test01/system01.dbf            SYSTEM /u01/app/oracle/product/10.2.0/dbs/MISSING00004    USERS /u01/app/oracle/product/10.2.0/dbs/MISSING00005    EXAMPLE /u01/app/oracle/product/10.2.0/dbs/MISSING00006    GOOGLE 6 rows selected. SQL>

ㄷ. DBA_FREE_SPACE
테이블스페이스 프리한 공간을 확인하는 딕셔너리.

select     TABLESPACE_NAME,     count(*) cnt,     sum(bytes) sm,     sum(bytes) / 1024 / 1024 as MB ,     max(bytes) mx from     DBA_FREE_SPACE group by TABLESPACE_NAME; TABLESPACE_NAME          CNT          SM           MB             MX -------------------- ---------- ---------- -----  ----- ---------- TESTMAN1                           1        46071808       43.9375   46071808 UNDOTBS1                           9        831062016      792.5625  828375040 SYSAUX                              28        9895936         9.4375    4456448 SYSTEM                             34        84410368        80.5       13631488 SQL>

ㄹ. DBA_TABLESPACE_USAGE_METRICS
테이블스페이스 사용량 정보

SELECT     * from     DBA_TABLESPACE_USAGE_METRICS; TABLESPACE_NAME      USED_SPACE TABLESPACE_SIZE USED_PERCENT -------------------- ---------- --------------- ------------ SYSAUX                    30784         4194302   .733948104 SYSTEM                    67768         4194302    1.6157158 TEMPS02                       0          128000            0 TESTMAN1                  96136          128000     75.10625 SQL>

ㅁ. v$tablespace
테이블스페이스의 간단한 정보

SELECT      * from       v$tablespace;        TS# NAME                           INC BIG FLA ENC ---------- ------------------------------ --- --- --- ---          0 SYSTEM                          YES NO  YES          2 SYSAUX                           YES NO  YES          1 UNDOTBS1                       YES NO  YES          3 TEMP                              NO  NO  YES          4 USERS                             YES NO  YES          6 EXAMPLE                        YES NO  YES          7 GOOGLE                          YES NO  YES          9 TEMPS02                         NO  NO  YES         10 TESTMAN1                      YES NO  YES 9 rows selected. SQL>

ㅂ. v$datafile
테이블스페이스의 파일정보

column NAME format a50; column STATUS format a8;     select     NAME,     STATUS from     v$datafile; NAME                                                                   STATUS -------------------------------------------------- -------- /u01/app/oracle/oradata/system01.dbf                     SYSTEM /u01/app/oracle/oradata/test01/undotbs01.dbf           ONLINE /u01/app/oracle/oradata/test01/sysaux01.dbf            ONLINE /u01/app/oracle/product/10.2.0/dbs/MISSING00004    RECOVER /u01/app/oracle/product/10.2.0/dbs/MISSING00005    RECOVER /u01/app/oracle/product/10.2.0/dbs/MISSING00006    RECOVER /u01/app/oracle/oradata/test01/testman1.dbf             ONLINE /u01/app/oracle/oradata/test01/test_a_1.dbf              ONLINE /u01/app/oracle/oradata/test01/test_a_2.dbf              ONLINE /u01/app/oracle/oradata/test01/test_a_3.dbf              ONLINE /u01/app/oracle/oradata/test01/test_a_4.dbf              ONLINE ..... 19 rows selected. SQL>

2. 기본 구문

CREATE [undo,temporary,smallfile,bigfile] TABLESPACE [테이블스페이스명] datafile[tempfile] '경로및파일명' ... size [nK,nM,nG] autoextend on next [nK,nM,nG] maxsize [nK,nM,nG, unlimited] extent management local [ autoallocate , uniform size nK ] segment space management [auto, manual] [online,offline] [logging,nologging]

A. undo , temporary , smallfile, bigfile 
- undo : undo 테이블스페이스 만들때.
- temporary : temp 테이블스페이스 만들때.
- smallfile : 기본값. default 테이블스페이스.
- bigfile : 대용량 테이블스페이스

B. datafile,tempfile
- datafile : undo 테이블스페이스포함한 일반 테이블스페이스를 만들때 쓰는 경로파일 지정.
- tempfile : temp 테이블스페이스 만들때 쓰는 경로파일 지정.
- 데이터파일 추가 가능 (datafile 'exam1.dbf' size 1m , 'exam2.dbf' size 1m)

C. size
- size : 처음 만들어지는 테이블스페이스 사이즈

D. autoextend on
- 추가시 자동증가.

E. next [nK,nM,nG]
- 자동증가시 자동으로 늘어나는 용량.

F. maxsize [nK,nM,nG, unlimited]
- 최대 증가 사이즈 ( unlimited 일경우 OS또는 실제 HDD용량 최대 사용 )

G. extent management local [ autoallocate , uniform size nK ]
- tablespace 관리를 로컬(file의 헤더에서 관리)에서 관리함.
- autoallocate : Extent 64K - system 관리.(기본값)
- uniform size nK : 테이블스페이스내 extent 사이즈 사용자정의.( default 1MB )
- 수동으로 만들경우 지정을 해야함 - 수동생성기본값 dictionary, DBCA로 만들경우 : local

H. segment space management [auto, manual]
- ASSM : 세그먼트 자동증가.
- MSSM : 수동 자동증가.

I. [online,offline]
- online : 테이슬스페이스 활성화.
- offline : 테이블스페이스 비활성화.

J. [logging,nologging]
- logging : 해당 테이블스페이스관련 로그 활성화
- nologging : 해당 테이블스레이스관련 로그 비활성화

3. 테이블스페이스 관리.

A. 테이블 스페이스 생성.
 

create tablespace studytbs1  datafile '/u01/app/oracle/oradata/test01/studytb1.dbf'  size 1m; Tablespace created. SQL> select     TABLESPACE_NAME,     STATUS,     EXTENT_MANAGEMENT,     SEGMENT_SPACE_MANAGEMENT From     DBA_TABLESPACES where     TABLESPACE_NAME = 'STUDYTBS1'; TABLESPACE_NAME                STATUS    EXTENT_MAN SEGMEN ------------------------------ --------- ---------- ------ STUDYTBS1                             ONLINE      LOCAL    AUTO SQL>

B. 테이블 스페이스 늘리기 [추가, 사이즈 늘리기, 자동증가 ]

-- 추가하기 alter tablespace studytbs1 add datafile '/u01/app/oracle/oradata/test01/studytb2.dbf' size 1m; Tablespace altered. SQL> column TABLESPACE_NAME format a20; column FILE_NAME format a50; select     FILE_NAME,     TABLESPACE_NAME from     DBA_DATA_FILES where     TABLESPACE_NAME = 'STUDYTBS1'; FILE_NAME                                                           TABLESPACE_NAME -------------------------------------------------- -------------------- /u01/app/oracle/oradata/test01/studytb1.dbf             STUDYTBS1 /u01/app/oracle/oradata/test01/studytb2.dbf             STUDYTBS1 SQL> -- 사이즈를 변경하기 alter database  datafile '/u01/app/oracle/oradata/test01/studytb2.dbf' resize 2m; Database altered. SQL> select     FILE_NAME,     BYTES From     dba_data_files where     TABLESPACE_NAME = 'STUDYTBS1'; FILE_NAME                                                            BYTES -------------------------------------------------- ---------- /u01/app/oracle/oradata/test01/studytb1.dbf             1048576 /u01/app/oracle/oradata/test01/studytb2.dbf             2097152 SQL> -- 자동증가 alter database datafile '/u01/app/oracle/oradata/test01/studytb2.dbf' autoextend on; Database altered. SQL> select     FILE_NAME,     BYTES,     AUTOEXTENSIBLE From     dba_data_files where     TABLESPACE_NAME = 'STUDYTBS1'; FILE_NAME                                                           BYTES      AUT -------------------------------------------------- ---------- --- /u01/app/oracle/oradata/test01/studytb1.dbf             1048576     NO /u01/app/oracle/oradata/test01/studytb2.dbf             2097152     YES SQL>

C. 테이블스페이스 offline :

select     STATUS from     dba_tablespaces where     tablespace_name = 'STUDYTBS1'; STATUS --------- ONLINE SQL> alter tablespace studytbs1 offline; Tablespace altered. SQL> select     STATUS from     dba_tablespaces where     tablespace_name = 'STUDYTBS1'; STATUS --------- OFFLINE SQL> create table aaa   2  (clum varchar(10)) tablespace studytbs1; create table aaa * ERROR at line 1: ORA-01542: tablespace 'STUDYTBS1' is offline, cannot allocate space in it SQL>

D. 테이블스페이스 이동(일반테이블스페이스)

--현재 경로 확인 select     FILE_NAME from     DBA_DATA_FILES where     TABLESPACE_NAME = 'STUDYTBS1'; FILE_NAME -------------------------------------------------- /u01/app/oracle/oradata/test01/studytb1.dbf /u01/app/oracle/oradata/test01/studytb2.dbf SQL> -- 테이블스페이스 오프라인 alter tablespace studytbs1 offline; Tablespace altered. SQL> -- 물리적인 파일 이동 SQL>!mv /u01/app/oracle/oradata/test01/studytb1.dbf /u01/app/oracle/oradata/test01/studytb1_move.dbf -- 논리적인 파일명 변경 alter tablespace studytbs1 rename datafile '/u01/app/oracle/oradata/test01/studytb1.dbf' to '/u01/app/oracle/oradata/test01/studytb1_move.dbf'; Tablespace altered. SQL> -- 테이블스페이스 온라인 alter tablespace studytbs1 online; Tablespace altered. SQL> -- 변경후 확인 select     FILE_NAME from     DBA_DATA_FILES where     TABLESPACE_NAME = 'STUDYTBS1'; FILE_NAME -------------------------------------------------- /u01/app/oracle/oradata/test01/studytb1_move.dbf /u01/app/oracle/oradata/test01/studytb2.dbf SQL>

E. 테이블스페이스 이동 (필수테이블스페이스)

select     FILE_NAME from     DBA_DATA_FILES where     TABLESPACE_NAME = 'SYSTEM'; FILE_NAME -------------------------------------------------- /u01/app/oracle/oradata/test01/system01.dbf SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount ORACLE instance started. Total System Global Area   285212672 bytes Fixed Size                        2020192 bytes Variable Size                    96472224 bytes Database Buffers               180355072 bytes Redo Buffers                     6365184 bytes Database mounted. SQL> SQL> !mv /u01/app/oracle/oradata/test01/system01.dbf /u01/app/oracle/oradata/system01.dbf select     name from     v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/test01/system01.dbf ..... 18 rows selected SQL> alter database rename file '/u01/app/oracle/oradata/test01/system01.dbf' to '/u01/app/oracle/oradata/system01.dbf'; Database altered. SQL> select     name from     v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/system01.dbf ..... 18 rows selected SQL> SQL> alter database open; Database altered. SQL>

F. 테이블스페이스 삭제

create tablespace droptest1  datafile '/u01/app/oracle/oradata/test01/droptest1.dbf'  size 1m;   create tablespace droptest2  datafile '/u01/app/oracle/oradata/test01/droptest2.dbf'  size 1m;   create tablespace droptest3  datafile '/u01/app/oracle/oradata/test01/droptest3.dbf'  size 1m;   create table droptbltest1 (c varchar(1)) tablespace droptest1; create table droptbltest2 (c varchar(1)) tablespace droptest2; create table droptbltest3 (c varchar(1)) tablespace droptest3; select     TABLESPACE_NAME from     DBA_TABLESPACES where     TABLESPACE_NAME in ('DROPTEST1','DROPTEST2','DROPTEST3'); TABLESPACE_NAME ------------------------------ DROPTEST1 DROPTEST2 DROPTEST3 SQL> !ls droptest* droptest1.dbf  droptest2.dbf  droptest3.dbf SQL> drop tablespace droptest1; drop tablespace droptest2 including contents; drop tablespace droptest3 including contents and datafiles; -- 내용이 있을경우 drop 불가. SQL> drop tablespace droptest1 * ERROR at line 1: ORA-01549: tablespace not empty, use INCLUDING CONTENTS option -- 내용 포함 drop SQL> Tablespace dropped. SQL> -- 물리적인 파일 포함 drop Tablespace dropped. SQL> select     TABLESPACE_NAME from     DBA_TABLESPACES where     TABLESPACE_NAME in ('DROPTEST1','DROPTEST2','DROPTEST3'); TABLESPACE_NAME ------------------------------ DROPTEST1 SQL> SQL> !ls droptest* droptest1.dbf  droptest2.dbf SQL>