본문 바로가기

Oracle/Study 세미나 자료

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>