이제 나의 인생에는 Ctrl+Z 란 없다.

Tablespaces and Data Files
:tablespace는 논리적 저장구조, Data file은 물리적 저장구조

    - Tablespace
       1. 한 시점에서 하나의 DB만을 가진다.
       2. 하나 이상의 datafile을 가진다. (분산처리)
       3. T/S내에도 논리적으로 분할된 저장구조를 가진다. (segment를 의미)
    - Data files
       : schema object 데이터를 저장

Type of Tablespaces

    - System T/s(data dictionary가 저장되는 공간)
       1. database 생성시 같이 생성 (필수 T/S)
       2. data dictionary 저장
       3. system undo segment 저장
  
    - Non-system tablespaces (일반 T/S)
       1. segment 단위로 분할됨
       2. 관리의 편의상 만듬
       3. user에게 할당된 공간을 제어

Creating Tablespaces

    - syntax
      Create Tablespace [생성할 T/S이름]
      Datafile '/oracle/oradata/[datafile 이름].dbf' size [integer] [단위(M,K)]
               '/oracle/oradata/[datafile 이름02].dbf' size ....
      [Default Storage(                  (extend 할당 설정, dictionary management방식)
                       initial(초기값) integer [M/K]
                       next (다음 증가값) integer [M/K]
                       Maxextents (최대값) integer [M/K]
                       pctincrease (증가율) integer) ]
      [online/offline] (T/S 온라인 오프라인 설정)
      [logging/nologging] (undo data 생성, undo data 생성하지않음)
      [permanent/temporary] (저장대상의 영구적 저장/임시적 저장)
      [extent management
                         Dictionary | Local(원하는 만큼 할당)
                         Autoallocate | uniform size integer [M/K](일정한 size 할당)]
      [segment space management (manual/auto)]

Space Management in Tablespaces

    - Locally managed(자체적 할당/해제)
        1. Free extents를 자체적으로 관리
        2. Free extents는 Bitmap을 이용해 관리
    - Dictionary managed(중앙 통제적 관리)
        1. Free extents의 모든 관리는 중앙(system)에서 관리
            -따라서 경합 문제가 발생    
     * Bitmap : 연속된 공간에 Free block을 할당/해제

Locally managed T/S
: system T/S가 local 관리방식이면 그 외 모든 T/S도 Local방식만 허용

    - 경합을 줄임으로서 효율성이 장점
    - 할당/해제에 관한 undo data를 만들지 않음
    - Free extents의 병합작업이 따로 필요치 않음(Bitmap을 이용하므로)
    - systax
       Create tablespace userdata
       datafile '파일경로/파일이름.dbf' size 50m
       extent management LOCAL UNIFORM SIZE 128K;

Dictionary-Managed Tablespace
 :Dictionary-Managed Tablespace에 있는 세그먼트는 사용자 정의된 저장 영역을 가질수있지만
  지역적으로 관리되는 Tablespace보다 더 유연성이 있지만 효율성은 떨어짐.

    - Dictionary-Managed Tablespace
      1. 확장 영역이 데이터 딕셔너리에서 관리
      2. 테이블스페이스에 저장된 각 세그먼트에서 서로 다른 저장 영역 절이 있을수 있음.
      3. 병합 필요
 예시)
 CREATE TABLESPACE userdata
 DATAFILE '/u01/oradata/userdata01.dbf'
 SIZE 500M EXTENT MANAGEMENT DICTIONARY
 DEFAULT STORAGE
 (initial 1M NEXT 1M PCTINCREASE 0);

Undo Tablespace(실행 취소 테이블스페이스)
 :실행 취소 테이블스페이스는 자동 실행 취소 관리와 함께 사용.

      1. 실행 취소 세그먼트 저장.
      2. 다른 객체를 포함할 수 없음.
      3. 확장 영역이 지역적(only)으로 관리.
      4. DATAFILE 절 및 EXTENT MANAGEMENT 절만 사용할 수 있음.
 예시)
 CREATE UNDO TABLESPACE undo1
 DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M;

Temporary Tablespaces
 : 정렬 세그먼트에만 사용하는 임시 테이블스페이스를 지정하여 정렬 작업용 공간을 효과적
   으로 관리할 수 있습니다.
     
      1. 정렬 작업에 사용.
      2. 영구 객체를 포함할수 없음.
      3. 지역적으로 관리되는 확장 영역 권장.
 예시)
 CREATE TEMPORARY TABLESPACE temp
 TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M
 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
      4. 지역적으로 관리되는 임시 테이블스페이스에는 임시 데이터 파일(임시 파일)이 있으며
  이 파일은 다음을 제외하고 일반 데이터 파일과 비슷.
 - 항상 NOLOGGING 모드로 설정된다.
 - 읽기 전용으로 설정할 수 없다.
 - 임시 파일 이름을 바꿀 수 없다.
 - ALTER DATABASE명령으로 임시 파일을 생성할 수 없다.
 - 읽기 전용 데이터베이스에 필요.
 - 매체 복구는 임시 파일을 복구하지 않음.
      정렬 성능을 최적화하려면 UNIFORM SIZE를 SORT_AREA_SIZE매개변수의 배구가 되도록 설정.

Default Temporary Tablespace
 
    1. 모든 데이터베이스에서 사용되는 기본 임시 테이블 스페이스를 지정.
    2. 임시 데이터를 저장할 경우 SYSTEM 테이블스페이스 사용을 제거.
    3. CREATE DATABASE , ALTER DATABASE를 사용하여 생성.
 - 기본 임시 테이블스페이스 없이 데이터베이스를 생성할 경우 SYSTEM 테이블스페이스는
   TEMPORARY TABLESPACE 절 없이 생성된 임의의 사용자에 할당됩니다. 또한 SYSTEM
    테이블스페이스가 기본 임시 테이블스페이스라는 경고가 alert_sid.log에 기록됩니다.
   데이터베이스 생성 시 기본 임시 테이블스페이스를 생성하면 SYSTEM 테이블스페이스는
   임시 공간에 사용될 수 없습니다.데이터베이스 생성 후 임시 테이블스페이스를 생성하고
   데이터베이스를 변경하여 기본 임시 테이블스페이스를 설정할 수 있습니다.

Creating a Default Temporary Tablespace

 CREATE DATABASE DBA01
 LOGFILE
 GROUP 1 ('/$HOME/ORADATA/u01/redo01.log') SIZE 100M,
 GROUP 2 ('/$HOME/ORADATA/u02/redo02.log') SIZE 100M,
 MAXLOGFILES 5
 MAXLOGMEMBERS 5
 MAXLOGHISTORY 1
 MAXDATAFILES 100
 MAXINSTANCES 1
 DATAFILE '/$HOME/ORADATA/u01/system01.dbf' SIZE 325M
 UNDO TABLESPACE undotbs
 DATAFILE '/$HOME/ORADATA/u02/undotbs01.dbf' SIZE 200
 DEFAULT TEMPORARY TABLESPACE temp
 TEMPFILE '/$HOME/ORADATA/u03/temp01.dbf' SIZE 4M
 CHARACTER SET US7ASCII

    1. 데이터베이스를 생성 후 다음 작업이 진행.
 ALTER DATABASE
 DEFAULT TEMPORARY TABLESPACE default_temp2;
    2. 데이터베이스의 기본 임시 테이블스페이스를 찾으려면
       DATABASE_PROPERTIES를 질의.
 - SELECT * FROM DATABASE_PROPERTIES;

기본 임시 테이블스페이스의 제한사항

    1. 새 기본값을 사용할 수 있을 때까지 삭제불가.
    2. 오프라인으로 설정할 수 없음.
    3. 영구 테이블스페이스로 변경 불가.
 - 예외| 변경방법
   1) temp2 임시 테이블스페이스를 생성.
   2) temp2를 지정
   3) temp 임시 테이블스페이스를 삭제.

Read-Only Tablespaces

    -읽기 전용모드로 설정
    Alter Tablespace userdata READ ONLY;

    checkpoint 발생
    데이터를 읽을 수만 있음
    tablespace drop 가능

Taking a Tablespace Offline

    -data access 사용할수 없음

    -Offline으로 설정할수 없는 tablespace
    1) system tablespace
    2) active undo segment가 있는 tablespace
    3) default temporary tablespace

    -Offline으로 설정
    Alter TABLESPACE userdata OFFLINE;

    -Online으로 설정
    Alter TABLESPACE userdata ONLINE;
   
   

Changing Storage Settings

    -ALTER TABLESPACE 명령을 사용하여 설정을 변경
    ALTER TABLESPACE userdata MINIMUM EXTENT 2M;
    ALTER TABLESPACE userdata
    DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);

    -locally managed tablespace는 변경할수 없음.

Resizing a Tablespace

    -data file의 크기변경

    1)AUTOEXTEND : 자동변경
    CREATE TABLESPACE userdata02
    DATAFILE '/u01/oradata/userdata02.dbf' SIZE 5M
    AUTOEXTEND ON NEXT 2M MAXSIZE 200M

    2)ALTER TABLESPACE : 수동변경
    ALTER DATABASE
    DATAFILE '/u01/oradata/userdata02.dbf' RESIZE 5M;

    -ALTER TABLESPACE를 사용한 data file추가  

데이터 파일의 자동 확장 활성화

    - 새 데이터 파일에 AUTOEXTEND 지정
 AUTOEXTEND 절은 데이터 파일의 자동 확장을 활성화하거나 비활성화합니다. 파일은 정
 해진 최대 크기까지 지정된 증분 단위로 증가됩니다.
 AUTOEXTEND 절을 사용하면 다음 이점이 있습니다.
  ? 테이블스페이스에 공간이 부족할 때 즉시 중재의 필요성이 줄어듭니다.
  ? 확장 영역 할당 오류로 인해 응용 프로그램이 중지되는 것을 방지합니다.
 데이터 파일이 생성될 때 다음 SQL 명령으로 데이터 파일의 자동 확장을 활성화할
 수 있습니다.
  ? CREATE DATABASE
  ? CREATE TABLESPACE ... DATAFILE
  ? ALTER TABLESPACE ... ADD DATAFILE
 ex)CREATE TABLESPACE user_data
    DATAFILE
     '/u01/oradata/userdata01.dbf' SIZE 200M
      AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
      ->AUTOEXTEND ON -> DBA_TABLESPACE * NEXT_EXTENT -> TABLESPACE 최대용량까지
               자동으로 늘어남.
 DBA_DATA_FILES 뷰를 질의 하여 AUTOEXTEND가 활성화 되었는지 확인합니다.

    - 새 데이터 파일에 AUTOEXTEND 지정
 ALTER DATABASE 명령을 사용하여 데이터 파일을 수정하고 자동 확장을 활성화합
 니다.
 ALTER DATABASE DATAFILE filespec [autoextend_clause]
 autoextend_clause:== [ AUTOEXTEND { OFF|ON[NEXT integer[K|M]]
   [MAXSIZE UNLIMITED | integer[K|M]] } ]
 설명:
 AUTOEXTEND OFF: 데이터 파일의 자동 확장을 비활성화합니다.
 AUTOEXTEND ON: 데이터 파일의 자동 확장을 활성화합니다.
 NEXT: 추가 확장 영역이 필요할 때 데이터 파일에 할당할 디스크 공간을 지정합니다.
 MAXSIZE: 데이터 파일에 할당할 수 있는 최대 디스크 공간을 지정합니다.
 UNLIMITED: 데이터 파일에 무제한의 디스크 공간 할당을 설정합니다.
    - 기존 데이터 파일에 AUTOEXTEND 지정
 SQL 명령인 ALTER DATABASE를 사용하여 기존 데이터 파일에 자동 파일 확장을 활성화
 하거나 비활성화합니다.
 ALTER DATABASE [database]
  DATAFILE 'filename'[, 'filename']... autoextend_clause
 AUTOEXTEND가 활성화되었는지 또는 비활성화되었는지 확인
 DBA_DATA_FILES 뷰를 질의하여 AUTOEXTEND가 활성화되었는지 여부를 확인하고
 AUTOEXTENSIBLE 열을 검사합니다.
 SQL> select tablespace_name, file_name, autoextensible
  2 from dba_data_files;
 TABLESPACE_NAME FILE_NAME AUTOEXTENSIBLE
 --------------- ------------------------ --------------
 SYSTEM   /home/dba01/ORADATA/u01/system01.dbf  YES
 DATA01   /home/dba01/ORADATA/u04/data01.dbf    NO
 USERS   /home/dba01/ORADATA/u03/users01.dbf   NO
 INDX   /home/dba01/ORADATA/u06/indx01.dbf    NO
 SAMPLE   /home/dba01/ORADATA/u02/sample01.dbf  YES
 DATA02   /home/dba01/ORADATA/u03/data02.dbf    NO
 INDEX01  /home/dba01/ORADATA/u06/index01.dbf   YES
 UNDO2   /home/dba01/ORADATA/u01/UNDO2.dbf     NO
 8 rows selected.

    - Oracle Enterprise Manager를 사용하여 자동 크기 조정 활성화
 OEM Console에서 다음 작업을 수행합니다.
 1. Databases > Storage > Datafiles로 이동합니다.
 2. 데이터 파일을 선택합니다.
 3. Storage 탭에서 Automatically extend datafile when full 확인란을 선
    택합니다.
 4. Increment 및 Maximum Size에 값을 설정합니다.
 5. Apply를 누릅니다.

수동으로 데이터 파일 크기 조정
    - 데이터 파일을 추가하여 데이터베이스에 공간을 추가하지 않고도 DBA는 데이터 파일 크
      기를 변경할 수 있습니다. ALTER DATABASE 명령을 사용하여 데이터 파일 크기를 수동으
      로 늘리거나 줄입니다.
    - 데이터 파일의 수동 크기 조정은 사용되지 않은 데이터베이스의 공간을 확보합니다.   
      ALTER DATABASE [database]
 DATAFILE ‘filename’[, ‘filename’]...
 RESIZE integer[K|M]
      설명:
      Integer: 결과 데이터 파일의 절대 크기로 바이트 단위입니다.
      특정 크기 이상으로 저장된 데이터베이스 객체가 있는 경우 데이터 파일 크기는 데이터 파
      일에 있는 마지막 객체의 마지막 블록까지만 감소됩니다.
      ex)
  ALTER DATABASE
  DATAFILE '/u03/oradata/userdata02.dbf'
  RESIZE 200M;

테이블스페이스에 데이터 파일 추가

    - ALTER TABLESPACE ADD DATAFILE 명령으로 테이블스페이스에 데이터 파일을 추가
      하여 테이블스페이스에 할당된 디스크 공간의 전체 용량을 늘릴 수 있습니다.
      ADD DATAFILE 절은 데이터 파일을 추가하는데 사용됩니다.
      ALTER TABLESPACE tablespace
  ADD DATAFILE
   filespec [autoextend_clause]
      ex)
 ALTER TABLESPACE user_data
 ADD DATAFILE '/u01/oradata/userdata03.dbf'
 SIZE 200M;
    - Oracle Enterprise Manager를 사용하여 데이터 파일 추가
 OEM Console에서 다음 작업을 수행합니다.
 1. Databases > Storage > Tablespaces로 이동합니다.
 2. 테이블스페이스를 선택합니다.
 3. Add Datafile을 선택합니다.
 4. General 탭에서 파일 정보를 입력합니다.
 5. Create를 누릅니다.

********************************************************** 
 *system    *non-system
 1. shutdown   1. offline
 2. cp(물리)   2. cp(물리)
 3. startup mount  3. rename(논리)
 4. rename(논리)   4. online
 5. open    
 6. old file 삭제  5. old file 삭제
**********************************************************
Methods for Moving Data Files(데이터 파일 이동 방식)

    - alter tablespace
     : 테이블 스페이스는 오프라인이어야 함.
     : data file 이 존재해야 함.

    ALTER TABLESPACE userdata RENAME
    DATAFILE '/oracle/oradata/DB02/userdata01.dbf'
    TO '/oracle/backup/userdata01.dbf';
 
Methods for Moving Data Files(데이터 파일 이동 방식)
    - alter database
     : 데이터베이스는 마운트 여야 함.
     : data file 이 존재

 ALTER DATABASE RENAME
 FILE '/oracle/oradata/DB02/system01.dbf'
 to   '/oracle/backup/system01.dbf';

Dropping Tablespace

    - 삭제할 수 없는 경우
     : system tablespace 인 경우
     : 테이블 스페이스에 활성 세그먼트가 있는 경우
    - INCLUDING CONTENTS : segment 삭제 , 논리적 object 같이 삭제
    - INCLUDING CONTENTS AND DATAFILES : 데이터 파일 삭제
    - CASCADE CONSTRAINTS : 참조 무결성 제약 조건을 삭제 , 관계 끊기

 DROP TABLESPACE userdata
 INCLUDING CONTENTS AND DATAFILES CASCADE COSTRAINTS;

Managing Tablespaces Using OMF
(OMP를 사용하여 테이블스페이스 관리)
   
    - DB_CREATE_FILE_DEST 매개변수 정의
     : 초기화 매개변수 파일
     : alter system 동적 설정
 alter system set
 db_create_file_dest = '/oracle/oradata/DB02/';
    - 테이블 스페이스 생성시
     : 데이퍼 파일이 자동 생성 되어 DB_CREATE_FILE_DESTfp 저장
     : 기본 100MB
     : AUTOEXTEND - unlimited로 설정됨

Managing Tablespaces Using OMF
(OMF를 사용하여 테이블스페이스 관리)

    - OMF 테이블스페이스 생성
 create tablespace text_data datafile size 20m;
    - 기존 테이블스페이스에 OMF 데이터 파일 추가
 alter tablespace text_data add datafile;
    - 기본 파일 위치 동적 변경 
 alter system set
 db_create_file_dest = '/oracle/oradata/DB02';
    - 테이블스페이스 삭제는 OS 파일 삭제를 포함

정보 조회
    - 테이블스페이스
     : DBA_TABLESPACES
     : V$TABLESPACE
    - 데이터 파일 정보
     : DBA_DATA_FILES
     : V$DATAFILE
    - 임시 파일 정보
     : DBA_TEMP_FILES
     : V$TEMPFILE

2013년 01월 11일 study && 실습

-- 테이블스페이스 정보 조회
desc dba_data_files;
desc dba_tablespaces;
select tablespace_name, bytes/1024/1024, file_name from dba_data_files;
select tablespace_name, status, extent_management, segment_space_management from dba_tablespaces;

-- 테이블스페이스 생성
create tablespace info_data
datafile 'C:\APP\ARTMIN\ORADATA\KJCARD\info_data01.DBF' size 200m
default storage(
  initial 80k
  next 80k
  minextents 1
  maxextents 121
  pctincrease 80
)online;

-- 테이블스페이스 online/offline
-- alter tablespace 테이블스페이스 명 [online]/offline;
alter tablespace info_data offline;
alter tablespace info_data online;
select tablespace_name, status, extent_management, segment_space_management from dba_tablespaces;

-- 테이블스페이스의 공간추가
alter tablespace info_data
add datafile alter tablespace info_data
add datafile 'C:\APP\ARTMIN\ORADATA\KJCARD\info_data02.DBF' size 100m;
select tablespace_name, bytes/1024/1024, file_name from dba_data_files;

-- 테이블스페이스 크기 변경
alter database datafile 'C:\APP\ARTMIN\ORADATA\KJCARD\info_data02.DBF' resize 200m;
select tablespace_name, bytes/1024/1024, file_name from dba_data_files;

-- 테이블스페이스 변경하기(확인불가)
alter tablespace tax2110
default storage(
  initial 1024k
  next 2048k
  minextents 1
  maxextents 5
)online;
pctincrease 기본이 50%이다

-- 테이블스페이스 자동확장 추가(Automatic Extension)
alter tablespace info_data
add datafile 'C:\APP\ARTMIN\ORADATA\KJCARD\INFO_DATA03.DBF' size 50m
autoextend on next 10m
maxsize 100m;
→ maxsize를 지정할때 datafile보다 같거나, 크야된다.
select tablespace_name, bytes/1024/1024 mb, file_name from dba_data_files;

-- 기본 테이블스페이스에 자동확장 변경하기
alter database datafile 'C:\APP\ARTMIN\ORADATA\KJCARD\INFO_DATA03.DBF'
autoextend on next 10m
maxsize 100m;
select tablespace_name, bytes/1024/1024 mb, file_name from dba_data_files;

-- 테이블스페이스 삭제
drop tablespace 테이블스페이스명
including contents    --> 테이블스페이스의 모든 세그먼트를 삭제( 데이터가 있는 테이블스페이스는 삭제할수 없다)
cascade constraints;  --> 삭제된 테이블스페이스 내의 테이블의 기본키와 유일키를 참조하는 다른 테이블스페이스의 테이블로부터 참조무결성 제약 조건을 삭제합니다.
$ rm testdb.dbf       --> OS에서 물리적 datafile 삭제.

-- 테이블스페이스의 물리적파일까지 삭제
drop tablespace info_data including contents and datafiles;
select tablespace_name, bytes/1024/1024 mb, file_name from dba_data_files;
select tablespace_name, status, extent_management, segment_space_management from dba_tablespaces;

-- 테이블스페이스 온/오프라인
alter tablespace testdb_lmt_auto offline;
select tablespace_name, status, extent_management, segment_space_management from dba_tablespaces;
alter tablespace testdb_lmt_auto online;

tbs_study_1.sql

테이블스페이스 문법
참조: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7003.htm

    - 문법      
      Create Tablespace [생성할 T/S이름]
      Datafile '/oracle/oradata/[datafile 이름].dbf' size [integer] [단위(M,K)]
               '/oracle/oradata/[datafile 이름02].dbf' size ....
      [Default Storage(                  (extend 할당 설정, dictionary management방식)
                       initial(초기값) integer [M/K]
                       next (다음 증가값) integer [M/K]
                       Maxextents (최대값) integer [M/K]
                       pctincrease (증가율) integer) ]
      [online/offline] (T/S 온라인 오프라인 설정)
      [logging/nologging] (undo data 생성, undo data 생성하지않음)
      [permanent/temporary] (저장대상의 영구적 저장/임시적 저장)
      [extent management
                         Dictionary | Local(원하는 만큼 할당)
                         Autoallocate | uniform size integer [M/K](일정한 size 할당)]
      [segment space management (manual/auto)]

create tablespace
오라클 데이터베이스내에서 생성되고 처리될 테이블들의 레코드들이 실제로 존재할 영역을 디스크 상에 물리적으로 생성시키는 명령어이다.

datafile
데이터베이스내에서 사용되는 레코드들이 실제로 디스크상에 파일로 존재하게 되는데, 이때의 파일의 위치와 이름을 지정하는 곳이다.

data_file
데이터베이스내에서 사용되는 레코드들이 실제로 디스크상에 파일로 존재하게 되는데, 이때의 파일의 위치와 이름을 지정하는 곳이다.

data_full_file_name
레코들이 실제로 존재할 디렉토리(절대패스사용)와 이름이다.

size
테이블스페이스내의 레코드들을 저장할 디스크상의 파일의 최대 크기를 지정해 줄 수 있다.

datafilesize_min
테이블생성시 사용할 수 있는 공간의 크기로, 예를 들어 10m로 지정되면 생성된 임의의 테이블에 입력되는 데이터들을 10m의 영역에 저장

next
처음에 저장될 데이터의 영역인 initial 만큼을 다 쓰고 더 이상의 공간이 없을 때, 사용할 수 있는 영역을 할당 시켜 준다.

datafilesize_max
추가로 테이블에 데이터가 입력될 때, 사용할 수 있는 영역의 크기이다.
예를 들어 5m를 할당, 임의의 테이블이 사용한 영역이 10m(위의 initial 영역의 크기이다.)를 넘을 경우, 추가로 5m만큼의 영역을 더 사용할 수 있게 된다. 따라서 총 사용공간은 15m가된다.

minextents mininum
next 영역으로 할당할 수 있는 최소의 개수를 지정해 줄 수 있다.

maxextents maxnum
next 영역으로 할당할 수 있는 최대의 개수를 지정해 줄 수 있다.

picincrease num
next를 지정하여 추가로 사용할 영역을 확장하고자 할 때, 늘어날 영역의 크기를 '%'로 나타낸 값이다. pct는 '%'를 의미한다. 예를 들어 picincrease 5라고 지정해 두면, next로 추가로 작업할 영역을 늘여 줄때, 처음에는 next를 설정된 영역만을 확장시켜 주나, 두 번째는부터는 next영역의 크기에서 5%만큼 더 크게 확장시켜 주게 되는 것이다.

online/offline
테이블스페이스 생성시 online이나 offline 중 택일하여 쓸 수 있으며, 생략하면 online을 의미한다. 테이블스페이스 생성을 하더라도 offline 상태라면, 상용자들은 해당 테이블스페이스를 사용하지 못한다.

 LMT(Locally Managed Tablespace) 사용하기

--  참조
작성일: 2002-11-11
작성자: 강명규(kang@dbakorea.pe.kr)
OS: Linux 2.4.19
Oralce: Oracle EE 8.1.7
출처: <http://blog.daum.net/_blog/hdn/ArticleContentsView.do?blogid=0PsWC&articleno=13&looping=0&longOpen=>
작성된 게시글은 다른 블로그의 내용을 참조하여 재 작성된 내용입니다.

-- 본문
미리 말하자면, 당신의 오라클서버가 이 기능을 지원한다면 무조건 이 기능을 사용하라.

오라클은 tablespace, extent, data block 이라는 관점에서 공간할당(Space Allocation)을 관리한다.
data blcok < extent < tablespace 논리적 구조이다.
data block은 보통 몇 개의 OS의 블록으로 구성되는 DB의 기본 I/O단위가 된다.
extent는 몇 개의 오라클 data block으로 구성된다.
보통 테이블등의 오브젝트는 이 extent단위로 할당을 받아 segment를 이루게 되며,
tablespace는 이 모든 것들을 포함한느 컨테이너로서 역할을 수행한다.

-- Dictionary managed tablespaces
테이블스페이스의 공간할당에 대한 관리는 테이블스페이스 생성지 별다른 지정을 하지 않는다면,
다른 오브젝트와 동일하게 Data Dictionary가 한다.
오라클 8.0.x이전 버전의 경우, 이것만이 가용한 방식이다.

-- Locally managed Tablespaces (LMT)
오라클 8.0.x이후 '테이블스페이스에 의한 extent관리'라는 공간할당관리옵션을 사용할 수 있다.
테이블스페이스에 대한 extent allocation 관리를 해당 테이블스페이스 자신이 스스로 할 수 있게 되었다.
즉, 성능병목현상을 유발할 수 있는 Data Dictionary에 의한 관리로부터 벗어날 수 있다는 것이다.
이러한 테이블스페이스는 자신이 소유한 각 데이터파일에 대한 블럭의 사용유무를 추적하기 위해 '비트맵'을 유지한다.
비트맵내의 각 비트(bit)는 블럭 혹은 블럭들의 그룹에 해당한다

-- LMT의 장점
1. temporary tablespace에 적합하게 사용될 수 있다.
2. 주기적인 tablespace의 coalesce가 불필요하다.
    (tablespace의 coalesce를 모른다면 자신이 DBA인가 의심하십시오)
3. 모든 extent는 균등한 크기를 갖게 하며, 테이블스페이스레벨에서 이를 강제하여
    사용자의 잘못된 extent크기지정의 위험을 피할 수 있다.

-- 주의점
1. temporary tablespace에는 UNIFORM extent allocation만 가능
2. NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE 저장옵션은 유효하지 않다.

-- EXTENT할당 방식
오라클이 새로운 extent를 할당할 수 있는 자유공간을 찾는 방식은 다음과 같다.
해당 테이블스페이스가 소유한 데이터파일들중 한 놈을 선택, 그 데이터파일의 '비트맵'을 검색하여
요구되는 개수의 인접 자유블럭(adjacent free block)을 가진 놈을 찾는다.
만일, 데이터파일이 충분한 인접자유공간을 가지고 있지 못한다면, 또다른 놈을 검색할 것이다.
extent가 할당해제될때, 오라클은 해당 데이터파일의 비트맵을 수정한다.

EXTENT할당(Extent Allocation)방식에는 2가지가 있다.

Automatic Extent Allocation
사용자는 초기 extent크기만을 결정하고, 이후의 추가적인 extent는 오라클이 결정한다.
(이때, extent크기는 64KB, 1MB, 8MB, 64MB 순으로 사용될 것이다. )

    SQL)
    create tablespace testdb_lmt_auto
    datafile 'C:\APP\ARTMIN\ORADATA\KJCARD\testdb_lmt_auto01.DBF' size 10m
    extent management local autoallocate;

Uniform Extent Allocation
생성될 extent크기를 사용자가 정할 수 있다.(default: 1MB)
이후, 생성되는 모든 extent는 초기에 정한 이 extent의 크기를 가지게 된다.
따라서, 이 테이블스페이스내에서 생성되는 테이블, 인덱스등은 모두 같은(균등한) extent크기를 가지게 될 것이다.
당연히 tablespace의 fragmetation은 발생하지 않으므로, coalesce과정이 필요없다.

    SQL>
    create tablespace testdb_lmt_uni
    datafile 'C:\APP\ARTMIN\ORADATA\KJCARD\testdb_lmt_uni01.DBF' size 10m
    extent management local uniform size 1m;

자, 그럼 dba_tablespaces에 질의하여 tablespace의 정보를 확인해 보자.
당연히, testdb_lmt_uni, testdb_lmt_auto는 extent_management가 local로 되어 있다.
allocation_type은 uniform의 경우 UNIFORM으로, AUTOALLOCATE의 경우 SYSTEM으로 되어 있음을 알수 있다.
특의하게 testdb_lmt_auto의 경우 NEXT_EXTENT가 정해져 있지 않음을 유의하자.
    
    SQL>
    select tablespace_name, initial_extent, next_extent, min_extents, max_extents,
    pct_increase, extent_management, allocation_type
    from dba_tablespaces;

AUTOALLOCATE의 경우, 처음 테이블의 크기(처음 EXTENT의 크기)는 64KB부터 시작한다.
테이블의 크기가 1MB가 될때까지 EXTENT는 64KB단위로 증가하고, 1MB가 된 이후부터는
EXTENT의 크기가 1MB단위로 증가하게 된다. 이후부터는 약간 다른데 64MB가 되었을때 8MB,
1GB가 되었을때 64MB단위로 EXTENT가 증가하게 된다. 따라서 위의 질의에서 NEXT_EXTENT가
명확히 보이지 않는 것이다.

lmt의 경우에서, extent의 크기는 테이블스페이스 레벨에서만 관리되어 진다.
즉, LMT내의 테이블등의 오브젝트는 모두 테이블스페이스의 저장옵션을 따라 정해진다.

-- 참조: tablespace 생성시 extent_management옵션을 dictionary로....
--  temporary tablespace를 locally managed로 변환하기

    -- 기존 temporary tablespace
    SQL>
    drop tablespace temp;    
    
    -- temporary tablespace 생성
    SQL>
    create temporary tablespace temp
    tempfile '/u01/app/oracle/oradata/db/ts_temp_lmt_uni01.dbf' size 10m
    extent management local uniform size 2m;

    SQL> 
    select tablespace_name, initial_extent, next_extent, min_extents, max_extents,
    pct_increase, extent_management, allocation_type
    from dba_tablespaces;

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE EXTENT_MAN ALLOCATIO
------------------------------ -------------- ----------- ----------- ----------- ------------ ---------- ---------
SYSTEM                                  65536       65536           1  2147483645           50 DICTIONARY USER
RBS                                         524288      524288           8        4096           50 DICTIONARY USER
TOOLS                                     32768       32768           1        4096            0 DICTIONARY USER
TEMP                                      2097152     2097152           1                        0 LOCAL      UNIFORM
USERS                                    131072      131072           1        4096            0 DICTIONARY USER
INDX                                       131072      131072           1        4096            0 DICTIONARY USER
TS_DBAKOREA_LMT_UNI          1048576     1048576           1  2147483645            0 LOCAL      UNIFORM
TS_TEST                                 20480       20480           1         249           50 DICTIONARY USER
TS_CORRUPT_TEST                 20480       20480           1         249           50 DICTIONARY USER
TS_DBAKOREA_LMT_AUTO                    65536                       1  2147483645              LOCAL      SYSTEM

10 rows selected.

이렇게 생성된 temporary tablespace는 v$datafile이 아닌 v$tempfile에서만 보여진다.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/db/system01.dbf
/u01/app/oracle/oradata/db/rbs01.dbf
/u01/app/oracle/oradata/db/tools01.dbf
/u01/app/oracle/oradata/db/users01.dbf
/u01/app/oracle/oradata/db/indx01.dbf
/u01/app/oracle/oradata/db/ts_corrupt_test01.dbf
/u01/app/oracle/oradata/db/ts_test01.dbf
/u01/app/oracle/oradata/db/ts_dbakorea_lmt_uni01.dbf
/u01/app/oracle/oradata/db/ts_dbakorea_lmt_auto01.dbf

9 rows selected.

SQL> select name from v$tempfile;

NAME
----------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/db/ts_temp_lmt_uni01.dbf

SQL>

더 많은 내용을 원한다면 dba_free_space, dba_extents뷰도 확인해보면 좋을 것이다.

이제 dictionary managed tablespace를 locally managed tablespace로 변경해 보겠다.
상호간의 변경은 8.1.6이상에서만 가능하므로 주의하기 바란다. 현재 필자는 8.1.7에서 테스트했다.
8.1.5는 locally managed tablespace -> dictionary managed tablespace 로의 변환만이 가능하다고 한다.

locally managed tablespace -> dictionary managed tablespace : DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL
dictionary managed tablespace -> locally managed tablespace : DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL

temporary와 system tablespace는 locally managed 에서 dictionary-managed로 변환이 불가능하다.
SYSTEM의 경우, 새로 DATABASE를 생성할때 할 수 밖에.. 다른 방법이 있으려나?
오라클사는 locally managed tablespace를 사용하기를 권장하고 있므로 지금부터 바꿔보는 것은 어떨까?

SQL> select tablespace_name, extent_management, allocation_type
2  from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         DICTIONARY USER
RBS                            DICTIONARY USER
TOOLS                          DICTIONARY USER
TEMP                           LOCAL      UNIFORM
USERS                          DICTIONARY USER
INDX                           DICTIONARY USER
TS_DBAKOREA_LMT_UNI            LOCAL      UNIFORM
TS_TEST                        DICTIONARY USER
TS_CORRUPT_TEST                DICTIONARY USER
TS_DBAKOREA_LMT_AUTO           LOCAL      SYSTEM

10 rows selected.

SQL> exec dbms_space_admin.tablespace_migrate_to_local('TOOLS');

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.tablespace_migrate_to_local('RBS');

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.tablespace_migrate_to_local('USERS');

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.tablespace_migrate_to_local('INDX');

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.tablespace_migrate_to_local('TS_TEST');

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.tablespace_migrate_to_local('TS_CORRUPT_TEST');

PL/SQL procedure successfully completed.

SQL> select tablespace_name, extent_management, allocation_type
2  from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM                         DICTIONARY USER
RBS                            LOCAL      USER
TOOLS                          LOCAL      USER
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      USER
INDX                           LOCAL      USER
TS_DBAKOREA_LMT_UNI            LOCAL      UNIFORM
TS_TEST                        LOCAL      USER
TS_CORRUPT_TEST                LOCAL      USER
TS_DBAKOREA_LMT_AUTO           LOCAL      SYSTEM

10 rows selected.

SQL> select tablespace_name, initial_extent, next_extent, min_extents, max_extents,
2  pct_increase, extent_management, allocation_type
3  from dba_tablespaces;

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE EXTENT_MAN ALLOCATIO
------------------------------ -------------- ----------- ----------- ----------- ------------ ---------- ---------
SYSTEM                                  65536       65536           1  2147483645           50 DICTIONARY USER
RBS                                    524288      524288           8        4096           50 LOCAL      USER
TOOLS                                   32768       32768           1        4096            0 LOCAL      USER
TEMP                                  2097152     2097152           1                        0 LOCAL      UNIFORM
USERS                                  131072      131072           1        4096            0 LOCAL      USER
INDX                                   131072      131072           1        4096            0 LOCAL      USER
TS_DBAKOREA_LMT_UNI                   1048576     1048576           1  2147483645            0 LOCAL      UNIFORM
TS_TEST                                 20480       20480           1         249           50 LOCAL      USER
TS_CORRUPT_TEST                         20480       20480           1         249           50 LOCAL      USER
TS_DBAKOREA_LMT_AUTO                    65536                       1  2147483645              LOCAL      SYSTEM

10 rows selected.

SQL>  

Tbs_LMT_use.sql

-- 테이블스페이스 자동 여부 확인

Ref → http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_3135.htm
col file_name format a60
select file_name, autoextensible from dba_data_files;

-- 테이블스페이스 자동 설정

Ref → http://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles003.htm
alter database datafile 데이터파일경로 autoextend on next 100m;
alter database datafile 데이터파일경로 autoextend on maxsize 10000m;

테이블스페이스 조회

-- 설정
SQL> COL TABLESPACE_NAME FORMAT  A10

-- 테이블스페이스 용량 조회
SELECT A.TABLESPACE_NAME,
       ROUND(A.BYTES_ALLOC / 1024 / 1024, 2) CURRENT_SIZE,
       ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024, 2) FREE_SIZE,
       ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024, 2) USED_SIZE,
       ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) FREE_RATE,
       100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100,2) USED_RATE,
       ROUND(MAXBYTES/1048576,2) MAX_SIZE
FROM   ( SELECT F.TABLESPACE_NAME,
                SUM(F.BYTES) BYTES_ALLOC,
                SUM(DECODE(F.AUTOEXTENSIBLE, 'YES',F.MAXBYTES,'NO', F.BYTES)) MAXBYTES
         FROM DBA_DATA_FILES F
         GROUP BY TABLESPACE_NAME) A,
       ( SELECT F.TABLESPACE_NAME,
                SUM(F.BYTES)  BYTES_FREE
         FROM DBA_FREE_SPACE F
         GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
UNION
SELECT TABLESPACE_NAME,
       ROUND(SUM(BYTES_USED + BYTES_FREE) / 1048576, 2),
       ROUND(SUM(BYTES_FREE) / 1048576,2),
       ROUND(SUM(BYTES_USED) / 1048576,2),
       ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) FREE_RATE,
       100 - ROUND((SUM(BYTES_FREE) / SUM(BYTES_USED + BYTES_FREE)) * 100,2) USED_RATE,
       ROUND(MAX(BYTES_USED + BYTES_FREE) / 1048576, 2)
FROM   SYS.V_$TEMP_SPACE_HEADER
group by tablespace_name
ORDER BY 1;

-- 테이블스페이스 정보 조회 (DBA_TABLESPACES)
Ref → http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5054.htm
SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM DBA_TABLESPACES;

-- 테이블스페이스 파일 정보 조회 (DBA_DATA_FILES)
Ref → http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_3135.htm
SELECT FILE_NAME, BYTES, STATUS FROM DBA_DATA_FILES;

-- 테이블스페이스 잔여 공간 정보 조회 (DBA_FREE_SPACE)
Ref → http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_3179.htm
SELECT TABLESPACE_NAME, BYTES, BLOCKS FROM DBA_FREE_SPACE;

테이블스페이스(TABLESPACE)란

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

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

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

시스템 테이블 스페이스

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

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

  유저데이타가 포함될 수 있지만 관리 효율성 면에서 포함 시키면 안됩니다.

비 시스템 테이블 스페이스

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

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

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

출처: <http://www.oracleclub.com/lecture/1094>

by Artmin

테이블스페이스는 모든 데이터를 저장하고 있는 데이터 파일로 구성되어 있는 논리적인 데이터 저장구조 이다.

DML문을 수행해 insert된 data를 비롯, 유저 스키마 정보 등 흔히 우리가 data라 부르는 모든 것들이 저장되는 영역이다.

테이블스페이스는 용도에 따라 시스템 테이블스페이스와 비시스템 테이블스페이스로 나눌 수 있으며,

시스템 테이블스페이스는 시스템 운영에 필요한 데이터 딕셔너리 데이터(메타데이터)가 저장된다.

10g 이후 시스템 테이블스페이스에 SYSAUX 테이블스페이스가 새롭게 추가 되었고,

이 SYSAUX 테이블스페이스는 10g 이전의 SYSTEM과 여러 테이블스페이스에서 담당하던 역할을 분담하여 SYSTEM 테이블스페이스의

과부하를 감소하여 관리할 수 있게 되었다.

즉, 시스템 테이블스페이스라고 하면 SYSTEM, SYSAUX 2가지를 생각하면 될 것이다.

비시스템 tablespace에는 TEMP(임시) tablespace, UNDO tablespace, 일반 tablespace가 있다.

TEMP tablespace는 order by절 등 정렬 작업을 요구하는 SQL문이 수행 될 때 PGA에 할당된 공간이 부족하면, TEMP tablespace에서 swap형식으로 내렸다 올렸다 하며 정렬한다. 이와 같은 정렬 작업 때문에 index 생성시에도 TEMP tablespace가 사용된다.

정렬 작업에 TEMP tablespace가 사용되게 되면 메모리에서 해결하지 못하고 디스크까지 사용 한다는 의미이기 때문에 속도(성능)이 상당히 느려진다.

UNDO tablespace는 rollback과 commit를 담당하는 undo segment를 저장(순차순환적으로 저장된다. Over Write)하는 공간이다.

일반 tablespace는 우리가 평상시에 사용하는 data들이 저장되는 공간이다.

(필자의 경우는 default tablespace는 USERS로 되어 있어, table 생성할 때 tablespace의 이름을 명시하지 않는다면, 생성되는 table은 

default tablespace(USERS tablespace)에 저장된다.)

default tablespace는 변경이 가능하며, table생성시 옵션으로 tablespace 지정하여 해당하는 tablespace에 table을 생성할 수 있다.

extent allocation 관리 방법에 따라서는 DMT(Dictionary Management Tablespace), LMT(Locally Management Tablespace)로 나눌 수 있으며, extent 정보를 Dictionary 테이블에서 관리하는 DMT방식은 거의 쓰이지 않는다(8.0.x 버전 이하는 DMT방식으로 하였다.)

대부분이 datafile헤더 부분의 bitmap 정보(LMT방식의 특징으로 테이블스페이스 스스로 extent allocation 관리를 위해 '비트맵' 유지)를 활용,  extent allocation을 테이블스페이스 자신 스스로 해결하는 LMT방식을 오라클에서 사용을 권고한다. 이 LMT방식을 사용 해야만 자동 세그먼트 공간 관리 방식인 ASSM(Automatic Segment Space Management)를 이용 할 수 있다.

segment space managed 방식에 따른 구분은 앞에서도 언급한 ASSM 방식과 PCTUSED, FREELIST option을 이용해 관리하는 수동 세그먼트 공간 관리 방식이 있다.

수동 세그먼트 공간 관리 방식 역시 관리하기 어려우므로 자주 사용되지 않는다.