[ORACE] TABLESPACE

출처 및 참조 사이트 :
1. http://www.zetswing.com/bbs/board.php?bo_table=ORACLE_TIP&wr_id=21
2. http://blog.naver.com/jyhwan?Redirect=Log&logNo=150135281011

 

-- 개념 --
* * * * TABLESPACE 이해 - 요약 * * * *
 . 테이블 스페이스는 논리적인 storage 개념이다.
 . 테이블 스페이스는 물리적으로 하나 이상의 데이터 파일로 구성된다.
 . 테이블 스페이스는 크게 SYSTEM 테이블 스페이스와 NON-SYSTEM(USER) 테이블 스페이스로 구분된다.
 . segment data , index, rollback, temporary segment등으로 구분된다.
 . segment는 하나 이상의 extent들로 구성된다.
 . extent 란 연속적으로 할당된 free block 의 모음이다.
 . extent 는 기본 storage 할당 단위이다.
 . DB Block(Oracle Block) 은 기본 IO단위이다.
 . Local Management Tablespace(지역관리), Dictionary Management Tablespace(딕셔너리 관리)
* * * * * * * * * * * * * * * * * * * *

* * * * * * * * * * * * * * * * * * * *
1. BLOCK : 오라클에서 말하는 데이터 입출력의 최소 단위.
2. EXTENT : SEGMENT의 할당 단위 ( 1 EXTENT는 8BLOCK)
3. SEGMENT : 테이블, 인덱스를 합쳐서 SEGMENT 라고 부름.
4. TABLESPACE : SEGMENT를 저장해 놓은 공간
5. DATABASE : TABLESPACE를 모아놓은 것
6. QOUTA : 쿼터, 특정 TABLESPACE에 데이터를 얼마나 저장할 수 있는지에 대한 정의
* * * * * * * * * * * * * * * * * * * *

* * * * 임시 테이블 스페이스 만드는 이유 * * * *
오라클에서 명령을 실행하면 명열을 해당 영역에 바로 저장하지 않고
임시 공간에 저장했다가 이후에 해당 영역으로 가져가 저장합니다.
이러한 이유로 임시 테이블 스페이스를 만듭니다. 또한 정렬시에도 사용.
* * * * * * * * * * * * * * * * * * * * * * * * *

* * * * Storage Parameter * * * * * * *
Storage Parameter는 데이터가 저장되는
객체인 Table과 Index의 최적화를 위해 사용되어진다.
DMT 방식에서 사용 되는 옵션이고, 현재는 Oracle9i 이후로 사용하지 않는 옵션이다. (9i 이후로는 LMT 사용)
Storage 생성 옵션
INITIAL : 테이블이나 인덱스가 작성될 때 최초로 할당되는 크기이다. 다르게 말하면
    Segment 생성에 처음 할당되는 extent 크기로 디폴트는 5개의 데이터 블록이다.
NEXT : 데이터량이 늘어나서 세그먼트에 공간이 더 필요하게 될 때 확장되는 크기를 말한다.
PCTINCREASE : NEXT만큼 확장할 때 NEXT 값에 추가로 확장될 비율을 뜻한다.
     정확하게는 NEXT * (1 - PCTINCREASE/100) 크기만큼 씩 확장된다.
     롤백 세그먼트는 0으로 고정되어 있으며 기본 값은 50이지만 0으로 세팅하기를 권장한다.
MINEXTENTS : 객체가 작성될 때 초기에 확장되는 개수를 세팅함
MAXEXTENTS : Segment 에 할당될 수 있는 전체 Extent 의 수. 디폴트 99개.
OPTIMAL : 롤백세그먼트에만 적용. 디폴트는 NULL.
FREELISTS : 테이블에 데이터가 입력될 때 메모리 상에서 빈 블록을 필요로 한다.
   이 속성은 메모리 상에 빈 블록을 미리 확보해 놓는 수이다.
* * * * * * * * * * * * * * * * * * * *

-- Tip. Hot  --

1. 오라클 서로 다른 계정의 테이블 복사하기. (SCOTT 계정의 emp 테이블을 scott2 계정으로 복사.)
 - create table scott2.emp table space mytablespace as select * from scott.emp;
 - 방법2 : http://www.zetswing.com/bbs/board.php?bo_table=ORACLE_TIP&wr_id=31
 
2. 현재 oracle db의 만들어진TABLESPACE 보기
 - system 으로 로그인 해서 select * from dba_tablespaces; 쿼리 실행
 - 해당 계정으로 로그인해서 select distinct tablespace_name from user_tables; 쿼리 실행

 - select * from user_tablespaces;

3. TABLESPACE 에 포함된 테이블 알아보기
 - SELECT TABLESPACE_NAME, TABLE_NAME FROM USER_TABLES WHERE TABLESPACE_NAME = '테이블스페이스명';

4. 현재 ORACLE서버에 만들어져있는 TABLESPACE보기(SYSTEM계정에서 실행)
 - SELECT file_name, tablespace_name, bytes, status FROM DBA_DATA_FILES;
  * FILE_NAME : datafile의 물리적인 위치와 파일명을 알 수 있습니다.
  * TABLESPACE_NAME : 테이블 스페이스의 이름을 알 수 있습니다.
  * BYTES : 테이블 스페이스의 크기를 알수 있습니다.
  * STATUS : 테이블 스페이스의 이용 가능 여부를 알 수 있습니다.

5. 현재 계정에서 사용할수 있는 테이블의 용량 조회
 - SELECT * FROM USER_SEGMENTS;

6. TABLESPACE별 사용 가능한 공간의 확인
 - SELECT tablespace_name, SUM(bytes), MAX(bytes) FROM DBA_FREE_SPACE GROUP BY tablespace_name;
 - 용량 확인
 SELECT A.TABLESPACE_NAME
           , ROUND(A.BYTES,2) TOT_SIZE
           , ROUND((A.BYTES-B.BYTES),2) USE_SIZE
           , ROUND(B.BYTES,2) FREE_SIZE
    FROM (SELECT B.TABLESPACE_NAME
    , SUM(B.BYTES)/1024/1024/1024 BYTES
   FROM DBA_DATA_FILES B
   GROUP BY B.TABLESPACE_NAME
   ) A
    , (SELECT A.TABLESPACE_NAME
       , SUM(A.BYTES)/1024/1024/1024 BYTES
   FROM DBA_FREE_SPACE A
   GROUP BY A.TABLESPACE_NAME
   ) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
 ORDER BY 1
 
7. TABLESPACE 생성
CREATE TABLESPACE 스페이스명    
DATAFILE '저장위치'    
SIZE 테이블 스페이스 크기    
AUTOEXTEND ON -- 자동 확장할건지 여부    
NEXT 증가사이즈    
MAXSIZE 최대크기;  

8. TABLESPACE 변경
ALTER TABLESPACE 스페이스명     -- 대상 TableSpace에서 명
ADD DATAFILE filespec      -- 처음 TableSpace에서 생성한 물리적 파일과 다른 이름
RENAME DATAFILE 'filename' to 'filename'   -- DATA 파일 이름 변경
DEFAULT STORAGE storage_clause ;   -- ?
 
9. TABLESPACE 제거
DROP TABLESPACE 스페이스명
INCLUDING CONTENTS         -- TableSpace의 모든 세그먼트 삭제, 즉 포함된 object, segment를 지움
CASCADE CONSTRAINTS        -- 삭제된 TableSpace내의 테이블의 기본키와 유일키를 참조하는 다른
              TableSpace의 테이블로부터 참조 무결성 제약조건을 삭제, (참조 무결성 깨지더라도 다지우겠다.)
AND DATAFILES ;          -- 실 파일 삭제
 
10. TABLESPACE + STORAGE 생성          --**oracle 9i로 이후로는 LMT 사용으로 사용하지 않는다. 추세 DMT
CREATE TABLESPACE 스페이스명    
DATAFILE 'E:\app\kipf_iam.dbf'     -- 저장위치
SIZE 50M         -- 테이블 스페이스 크기    
AUTOEXTEND ON         -- 자동 확장할건지 여부    
NEXT 10M         -- 증가사이즈    
MAXSIZE 100M         -- 최대크기
DEFAULT STORAGE (       ** 스토리지 설정
 INITIAL 10K        -- 첫번째 Extents 크기
 NEXT 10K        -- 다음 Extents 크기
 MINEXTENTS 2       -- 생성할 Extents의 최소값
 MAXEXTENTS 50       -- 생성할 Extents의 최대값
 PCTINCREASE 50       -- Extents의 증가율(default값 50)
) ONLINE            -- 활성시키기  ONLINE(활성) :: OFFLINE(비활성)
 
 
11. TABLESPACE + SEGMENT 생성     -- 미확실        
CREATE TABLESPACE 스페이스명    
DATAFILE 'E:\app\kipf_iam.dbf'     -- 저장위치
SIZE 50M         -- 테이블 스페이스 크기    
AUTOEXTEND ON         -- 자동 확장할건지 여부    
NEXT 10M         -- 증가사이즈    
MAXSIZE 100M         -- 최대크기
LOGGING          -- LOGGING(활성) :: NOLOGGING(비활성)
EXTENT MANAGEMENT LOCAL      -- 8i 부터 추가 됐으며 9i부터 디폴트
SEGMENT SPACE MANAGMENT AUTO;     -- 9i 부터 추가 됐으며 10g 에 디폴트

12. TEMPORARY TABLESPACE 생성      --** TEMPORARY TABLESPACE 생성
CREATE TEMPORARY TABLESPACE 스페이스명    
TEMPFILE 'E:\app\kipf_iam.dbf'     -- 저장위치
SIZE 50M         -- 테이블 스페이스 크기    
EXTENT MANAGEMENT LOCAL      -- Default 값임. SEGEMENT SPACE MANAGEMENT를 AUTO로 지정할 수 없다.
AUTOALLOCATE 65536K       -- SORT_AREA_SIZE의 배수로 설정 크기[K:M]

13. 세그먼트 (테이블, 인덱스) 재구성
SQL> ALTER TABLE USER01.TAB01 MOVE TABLESPACE TS_DATA;            -- 테이블스페이스를 지정하지 않으면 현재 저장된 테이블스페이스에 재구성한다.
SQL> ALTER INDEX USER01.IX_TAB01_01 REBUILD TABLESPACE TS_INDX;   -- 테이블스페이스를 지정하지 않으면 현재 저장된 테이블스페이스에 재구성한다.

 

 

14. 현재 oracle 서버의 모든 계정 확인
 - select * from all_users;

 
15. 사용자 생성 및 TABLESPACE , 인덱스TABLESPACE 지정
CREATE USER [유저명]
IDENTIFIED BY [비밀번호]
DEFAULT TABLESPACE [테이블스페이스이름]
TEMPORARY TABLESPACE [TEMP테이블스페이스이름]
QUOTA UNLIMITED ON [INDEX테이블스페이스이름]

16. 권한 생성(뷰 포함)
GRANT CONNECT, RESOURCE TO [유저명];
GRANT CREATE VIEW TO [유저명] ;

17. 사용자 삭제
DROP USER [유저명] CASCADE;

(ex)
CREATE TABLESPACE kipf_iam
DATAFILE 'E:\app\kipf_iam.dbf'
SIZE 500M
AUTOEXTEND ON
NEXT 100M
MAXSIZE 1G