Oracle TableSpace 관리 생성 및 삭제

  – 테이블스페이스는 하나 또는 여러개의 데이터 파일로 구성되어 있는 논리적인 데이터 저장 구조 입니다.

– 테이블스페이스는 크게 시스템(SYSTEM) 테이블 스페이스와 비시스템(NON-SYSTEM)  테이블 스페이스로 구분 됩니다.

– 테이블스페이스는 사용자에게 공간을 할당할 수 있으며, 테이블스페이스 안에 저장되어   있을 데이터의 가용성을 제어할 수 있습니다.

※ 참고 1

– 임시 테이블스페이스를 지정해 주지 않으면 시스템 테이블스페이스가 기본으로 지정 되지만 시 스템 테이블스페이스에 단편화가 발생할 수 있으므로 사용자를 생성할때 임시테이블스페이스를 따로 지정해 주는 것이 좋습니다.

– 또한 DEFAULT TABLESPACE도 사용자를 생성할때 지정해 주지 않으면 기본적으로 시스템 테이블스페이스가 지정이 됩니다. 하지만 사용자를 생성할때 DEFAULT TABLESPACE를 지정을 해서 사용자가 소유한 데이터와 객체들의 저장 공간을 별도로 관리를 해야 합니다.

시스템 테이블스페이스는 본래의 목적(모든 데이터 사전 정보와, 저장 프로시저, 패키지, 데이터베이스 트리거등을 저장)을 위해서만 사용되어져야 하지 일반사용자의 데이터 저장용으로 사용 되어서는 안됩니다.

 

1. 시스템 테이블 스페이스

– 오라클 데이터 베이스를 생성할 때 자동으로 생기며 오라클 데이터 베이스의 기동을 위해
꼭 필요한 테이블 스페이스 입니다.

– 모든 데이터 사전(Data Dictionary) 정보와, 저장 프로시저, 패키지, 데이터베이스
트리거등을 저장 합니다

– 유저데이타가 포함될 수 있지만 관리 효율성 면에서 포함 시키지 않습니다
2. 비 시스템 테이블 스페이스

– 롤백세그먼트, 임시세그먼트, 응용프로그렘 데이타, 그리고 응용프로그렘 인덱스를 저장 할 수 있습니다

– 공간관리를 쉽게 하기 위해서 생성 합니다.

– 유저에게 할당되는 공간 입니다.

 


▣ 테이블스페이스의 생성


   – 옵션절을 생략할 경우 밑줄친 옵션이 디폴트 값입니다.
– tablespace_name : 생성할 테이블 스페이스 명
– DATAFILE : 새로 생성하는 테이블스페이스가 사용할 데이터 파일
– filespec : 디렉토리 경로명을 포함한 파일명
– size : 새로 생성되는 데이터 파일의 크기

– ONLINE/OFFLINE
ONLINE : 새로 생성되는 테이블 스페이스를 활성화 시키며, 생성 후 바로 사용할 수 있게 함
OFFLINE : 테이블 스페이스를 비활성화 시키며, 생성 후 바로 사용할 수 없음

– PERMANT/TEMPORARY : TEMPORARY 옵션을 사용하면 생성하는 테이블스페이스는
임시 테이블스페이스가 됩니다.

– DEFAULT STORAGE

* INITIAL : 테이블 스페이스의 맨 첫번째 Extents의 크기
* NEXT : 다음 Extents의 크기
* MINEXTENTS : 생성할 Extents의 최소 값
* MAXEXTENTS : 생성할 Extents의 최대 값
* PCTINCREASE : Extents의 증가율, Default값은 50 입니다
테이블 스페이스 생성 예제

— sysdba권한으로 접속을 합니다.
SQL> conn sys/manager as sysdba

— 테이블스페이스 생성
SQL>CREATE TABLESPACE storm
DATAFILE ’C:\ORACLE\ORADATA\app_data.dbf’ SIZE 100M
DEFAULT STORAGE
(INITIAL    10K
NEXT      10K
MINEXTENTS 2
MAXEXTENTS 50
PCTINCREASE 50)

아래 그림 설명은 잘 못되었습니다.
두번째 extent는 next의 원래 크기와 동일합니다.
다음부터의 NEXT는 (1+pctincrease/100)에 next의 이전크기를 곱한 크기로 설정 됩니다.
그러므로 16K는 세번째 extent의 크기가 됩니다.

10k, 10k, 16k, 24K, 36k.. 이렇게 되겠죠..

 


▣ 비 활성화(OFFLINE) 테이블 스페이스

– 오프라인 상태 테이블스페이스의 데이타에는 접근 할 수가 없습니다.
– SYSTEM 테이블스페이스와 활성화된 롤백세그먼트를 가진 모든 테이블스페이스는
오프라인 상태가 될 수 없습니다.

– 테이블스페이스를 오프라인으로 만들기

데이타베이스가 오픈되어 있다면 데이타베이스 관리자는 SYSTEM 테이블스페이스나
활성 롤백세그먼트나  임시세그먼트를 가진 테이블스페이스를 제외한
어떤 테이블 스페이스든지 오프라인 상태로 만들 수  있습니다.

테이블스페이스가 오프라인상태가 됐을때 오라클 서버는 모든 관련된 모든 데이터
파일을 오프라인 상태로 만듭니다.


테이블스페이스는 세가지 모드로 오프라인 상태가 될 수 있습니다.
* normal : 디폴드 값, 테이블스페이스의 모든 데이터 파일에 대해 체크 포인트를 수행
합니다.
* immediate : CheckPoint를 수행하지 않습니다.
* temporary : 테이블스페이스의 ONLINE데이터 파일에 대해 체크 포인트를 수행 합니다.

예제)
SQL> ALTER TABLESPACE app_data OFFLINE;

▣ 데이터 파일의 이동(ALTER DATABASE)

1) 데이터베이스를 종료 합니다.
2) 운영체제 명령을 사용하여 데이터 파일을 이동 합니다.
3) 데이터베이스를 마운트 합니다.
4) ALTER DATABASCE RENAME FILE명령을 수행하여 이름을 변경합니다.
5) 인스턴스를 시작합니다.

1) 인스턴스의 종료

C:>SQLPLUS /nolog
SQL>conn sys/manager  as sysdba
SQL>shutdown immediate;

2) 운영체제 명령을 사용하여 데이터 파일을 이동

– 운영체제 명령을 이용해서 데이터 파일을 이동 시킵니다.
– UNIX에서는 mv명령을 이용해서 새로운 위치로 이동 시킵니다.

3) 데이터베이스 마운트

SQL>startup mount;

4) ALTER DATABASE명령의 수행

SQL>ALTER DATABASE RENAME FILE
’C:\oracle\oradata\storm.dbf’ to
’D:\oracle\oradata\oracle\storm.dbf’ ;

alter database명령을 실행하면 오라클은 파일이 존재하는가를 검사합니다.
만약 이단계에서 에러가 발생하면 파일이름이 정확한지 다시 확인 하시면 됩니다.

5) 인스턴스 시작

SQL>alter database open

▣ 읽기전용(Read Only) 테이블 스페이스

– 테이블스페이스는 온라인 상태 이어야 합니다.

– 활성화된 트랜잭션은 허용되지 않습니다.(DML 작업)

– 테이블 스페이스에 활성화된 롤백 세그먼트가 포함되어 있지 않아야 합니다.

– 테이블 스페이스는 현재 온라인 백업에 포함되어 있지 않아야 합니다.


SQL>ALTER TABLESPACE app_data  READ ONLY;

==>app_data 테이블 스페이스를 읽기 전용으로 변경 시킵니다.

▣ 테이블 스페이스의 삭제

◈ 데이타를 가지고 있는 테이블스페이스는 INCLUDING CONTENTS옵션 없이는 삭제할 수
없습니다.

◈ 컨트롤 파일 내에 있는 파일 포인터만 삭제됩니다. (데이터 파일은 존재)

◈ 데이타베이스 파일은 여전히 존재하므로 운영체제 레벨에서 명시적으로 삭제 되여야만 합니다.

◈ 데이터 딕셔너리의 내용이 변경 됩니다.

◈ TableSpace를 삭제 하기 전에 테이블스페이스를 오프라인 상태로 할 것을 권장 합니다.

◈ TableSpace가 일단 삭제되면 그 데이타는 데이타베이스에 더 이상 존재하지 않게 됩니다.


– INCLUDING CONTENTS: TableSpace에 Data가 이미들어있을 경우 들어있는 내용을
포함해서 모두 삭제 합니다.

– CASECADE CONSTRAINTS : Primary Key가 설정되어 있는 경우에 child 의 Foriegn Key를                                           삭제하고 Tablespace를 삭제 합니다.

SQL> DROP TABLESPACE app_data  INCLUDING CONTENTS ;

==>app_data테이블 스페이스를 삭제 합니다.

# 데이타 파일까지 삭제
SQL> drop tablespace 테이블스페이스명
including contents
and datafiles
cascade constraints;

DROP TABLESPACE만 하면 그 안에 만약 테이블들이 들어있다면 안지워짐.
INCLUDING CONTENTS : 안에 테이블이 있건 없건 관계없이 무조건 삭제.
AND DATAFILES : 데이터파일도 같이 삭제.
CASCADE CONSTRAINTS : 물려있던 제약조건까지 삭제됨.
DBA_TABBLESPACES 데이터 사전

SQL>conn sys/manager as sysdba
SQL>SELECT tablespace_name, initial_extent, max_extents,  min_extlen
FROM dba_tablespaces;
DBA_DATA_FILES 데이터 사전

SQL>conn sys/manager as sysdba
SQL> SELECT file_name, tablespace_name
FROM dba_data_files;
콘트롤파일의 데이타파일 정보와 테이블스페이스 정보

SQL>conn sys/manager as sysdba
SQL> SELECT status,enabled, t.name,d.name
FROM  v$datafile d, v$tablespace t
WHERE t.ts#=d.ts#;

STATUS    ENABLED       NAME            NAME
——-   ———-      ————   ————————————-
SYSTEM  READ WRITE SYSTEM       C:\ORACLE\ORADATA\ORACLE\SYSTEM01.DBF
ONLINE    READ WRITE RBS             C:\ORACLE\ORADATA\ORACLE\RBS01.DBF
ONLINE    READ WRITE USERS         C:\ORACLE\ORADATA\ORACLE\USERS01.DBF
ONLINE    READ WRITE TEMP           C:\ORACLE\ORADATA\ORACLE\TEMP01.DBF
ONLINE    READ WRITE TOOLS         C:\ORACLE\ORADATA\ORACLE\TOOLS01.DBF
ONLINE    READ WRITE INDX            C:\ORACLE\ORADATA\ORACLE\INDX01.DBF
ONLINE    READ WRITE DRSYS         C:\ORACLE\ORADATA\ORACLE\DR01.DBF
ONLINE    READ WRITE STORM        C:\ORACLE\ORADATA\ORACLE\STORM.DBF

테이블 스페이스의데이터 파일과 테이블 스페이스의 크기 확인

DBA_DATA_FILES 데이터 사전을 이용 하면 됩니다.

SQL> COL FILE_NAME FORMAT A40
SQL> COL TABLESPACE_NAME FORMAT A15

SQL>  SELECT file_name, tablespace_name, bytes, status FROM  DBA_DATA_FILES;

FILE_NAME                                                    T ABLESPACE_NAME      BYTES     STATUS
————————————-              —————    ————    ————
C:\ORACLE\ORADATA\ORACLE\SYSTEM01.DBF   SYSTEM           248250368   AVAILABLE
C:\ORACLE\ORADATA\ORACLE\RBS01.DBF          RBS                545259520    AVAILABLE
C:\ORACLE\ORADATA\ORACLE\USERS01.DBF      USERS            113246208    AVAILABLE
C:\ORACLE\ORADATA\ORACLE\TEMP01.DBF       TEMP               75497472     AVAILABLE
C:\ORACLE\ORADATA\ORACLE\TOOLS01.DBF     TOOLS             12582912     AVAILABLE
C:\ORACLE\ORADATA\ORACLE\INDX01.DBF         INDX                60817408     AVAILABLE
C:\ORACLE\ORADATA\ORACLE\DR01.DBF            DRSYS            92274688      AVAILABLE

◎ FILE_NAME : DATAFILE의 물리적인 위치와 파일명을 알 수 있습니다.
◎ TABLESPACE_NAME : 테이블 스페이스의 이름을 알 수 있습니다.
◎ BYTES : 테이블 스페이스의 크기를 알수 있습니다.
◎ STATUS : 테이블 스페이스의 이용 가능 여부를 알 수 있습니다.
테이블 스페이스별 사용 가능한 공간의 확인

DBA_FREE_SPACE 데이터 사전

SQL> SELECT tablespace_name, SUM(bytes), MAX(bytes)
FROM DBA_FREE_SPACE
GROUP BY tablespace_name

TABLESPACE_NAME SUM(BYTES) MAX(BYTES)
—————           ———-     ———-
DRSYS                     88268800           88268800
INDX                        60809216           60809216
RBS                       524279808          498589696
SYSTEM                      65536                 65536
TEMP                      75489280           74244096
TOOLS                     12574720           12574720
USERS                   113238016          113238016

◎ SUM을 사용한 이유는하나의 테이블 스페이스에 분산되어 있는 여유공간을 합한 것이며,
◎ MAX를 사용한 이유는 여유 공간중 가장 큰 공간의 SIZE를 의미 합니다.

 데이타 화일에 대한 총 크기와 남아있는 공간, 사용한 용량, 남은 %율

DBA_FREE_SPACE, DBA_DATA_FILES 데이터 사전

SQL> COL FILE_NAME FORMAT A40
SQL> COL TABLESPACE_NAME FORMAT A30
SQL> SET LINESIZE 150
SQL> SELECT  b.file_name “FILE_NAME”,                                         —  DataFile Name
b.tablespace_name “TABLESPACE_NAME”,                      — TableSpace Name
b.bytes / 1024 “TOTAL SIZE(KB)”,                                    — 총 Bytes
((b.bytes – sum(nvl(a.bytes,0)))) / 1024 “USED(KB)”,         — 사용한 용량
(sum(nvl(a.bytes,0))) / 1024 “FREE SIZE(KB)”,                  — 남은 용량
(sum(nvl(a.bytes,0)) / (b.bytes)) * 100 “FREE %”               — 남은 %
FROM   DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.file_id(+) = b.file_id
GROUP BY b.tablespace_name, b.file_name, b.bytes
ORDER BY b.tablespace_name

FILE_NAME                                                  TABLESPACE_NAME  TOTAL SIZE(KB)   USED(KB) FREE SIZE(KB)     FREE %
————————————-                —————     ————–   ————-    ————- ———-
C:\ORACLE\ORADATA\ORACLE\DR01.DBF            DRSYS                    90112            3912         86200            95.6587358
C:\ORACLE\ORADATA\ORACLE\INDX01.DBF         INDX                        59392                8         59384            99.9865302
C:\ORACLE\ORADATA\ORACLE\RBS01.DBF          RBS                       532480          20488        511992            96.1523438
C:\ORACLE\ORADATA\ORACLE\TEMP01.DBF       TEMP                       73728               8         73720             99.9891493
C:\ORACLE\ORADATA\ORACLE\TOOLS01.DBF     TOOLS                      12288               8         12280            99.9348958
C:\ORACLE\ORADATA\ORACLE\USERS01.DBF      USERS                    110592               8        110584           99.9927662
# 사용자 정보 포함 출력

SELECT FILE_NAME,
TABLESPACE_NAME||'(‘||b.USERNAME||’)’ AS TABLESPACE_NAME,
TOTAL_SIZE AS “TOTAL_SIZE(KB)”,
USED AS “USED(KB)”,
FREE_SIZE AS “FREE_SIZE(KB)”,
FREE_P AS “FREE %”,
STATUS
FROM
(
SELECT  b.file_name “FILE_NAME”,                                         —  DataFile Name
b.tablespace_name “TABLESPACE_NAME”,               — TableSpace Name
b.bytes / 1024 “TOTAL_SIZE”,                                  — 총 Bytes
((b.bytes – sum(nvl(a.bytes,0)))) / 1024 “USED”,         — 사용한 용량
(sum(nvl(a.bytes,0))) / 1024 “FREE_SIZE”,                  — 남은 용량
(sum(nvl(a.bytes,0)) / (b.bytes)) * 100 “FREE_P”,           — 남은 %
b.STATUS                                                                 — 사용 여부
FROM   DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.file_id(+) = b.file_id
GROUP BY b.tablespace_name, b.file_name, b.bytes, b.status
ORDER BY b.tablespace_name
) a, DBA_USERS b
WHERE a.TABLESPACE_NAME = b.DEFAULT_TABLESPACE(+)
;

# TEMP 테이블 스페이스 용량 확인

COL NAME FORMAT A50
COL TOTAL FORMAT A15
COL USED FORMAT A15
COL FREE FORMAT A15
COL USAGE FORMAT A15
SET LINESIZE 130

SELECT B.NAME
, B.BYTES/1024/1024 || ‘ M’ TOTAL
, A.BYTES_USED/1024/1024 || ‘ M’ USED
, (B.BYTES-A.BYTES_USED)/1024/1024 || ‘ M’ “FREE”
, ROUND(A.BYTES_USED/B.BYTES * 100) || ‘% ‘ “USAGE”
FROM (SELECT BYTES_USED, FILE_ID FROM V$TEMP_EXTENT_POOL) A
, (SELECT BYTES, FILE#, NAME FROM V$TEMPFILE) B
WHERE A.FILE_ID = B.FILE#
;

▣ 수동으로 데이터 파일 크기 변경

– ALTER DATABASE명령으로 데이터 파일의 크기를 변경 할 수 있습니다.

예제)
SQL>ALTER DATABASE DATAFILE
’C:\ORACLE\ORADATA\app_data02.dbf’ RESIZE 300M

RESIZE명령으로 데이터 파일의 크기를 300M로 했습니다.
기존 데이터 파일의 크기가 300M가 넘을 경우에는 300M로 줄어들게되고,
300M가 되지 않았을 경우에는 300M로 늘어나게 됩니다.

– 무제한으로 변경
ALTER DATABASE DATAFILE
‘/app/oracle/oradata/TEST.dbf’
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
;

— TABLESPACE 생성

CREATE TABLESPACE SCOTT_DATA
DATAFILE ‘/usr/local/ora9i/oradata/oracle/SCOTT_DATA.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M
— AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED — 크기 제한 없음
DEFAULT STORAGE
(INITIAL     4K
NEXT        128K
MINEXTENTS  1
PCTINCREASE 0);

# INDEX 테이블 스페이스의 사용은 인덱스를 사용시에 옵션으로 지정.

CREATE TABLESPACE SCOTT_INDEX
DATAFILE ‘/usr/local/ora9i/oradata/oracle/SCOTT_INDEX.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M
DEFAULT STORAGE
(INITIAL     4K
NEXT        128K
MINEXTENTS  1
PCTINCREASE 0);

CREATE TEMPORARY TABLESPACE SCOTT_TEMP
TEMPFILE ‘/usr/local/ora9i/oradata/oracle/SCOTT_TEMP.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

— 사용자 계정 생성과 동시에 테이블 스페이스 지정.
CREATE USER scott   — 사용자 아이디
IDENTIFIED BY tiger    — 사용자 패스워드
DEFAULT TABLESPACE SCOTT_DATA   — 기본 사용 테이블 스페이스
TEMPORARY TABLESPACE SCOTT_TEMP — 템프(order by 등 정렬작업) 테이블 스페이스
QUOTA UNLIMITED ON SCOTT_DATA              — 할당량 지정(현재설정 무제한)
QUOTA 0M ON SYSTEM                                — system tablespace를 사용 못하게 설정
;

— 권한 설정
— 생성한 사용자에게 자신의 schema에서 테이블등을 만들 권한과 자원을 사용할 권한을 준다.
— GRANT RESOURCE, CONNECT TO MIDAN;
— DBA 권한을 준다.
GRANT DBA TO unicorn;