[오라클]테이블스페이스 생성 및 관리

테이블스페이스(TABLESPACE)

-------------------------
 - 오라클 서버가 데이터를 저장하는 논리적인 구조
 - 테이블스페이스는 1개 또는 여러 개의 데이터파일을 가진 논리적인 데이터 저장 구조

 - LMT(Locally Managed Tablespace)

 - DMT(Dictionary Managed Tablespace)

 - Next Extent크기는 64KB, 1MB, 8MB, 64MB 순으로 사용됨
 - AUTOALLOCATE의 경우

   처음 테이블의 EXTENT는 64KB부터 시작, 1MB가 될 때까지 64KB단위로 증가,

   1MB가 된 이후부터는 1MB단위로 증가, 이후부터는 64MB가 되었을 때 8MB,
   1GB가 되었을 때 64MB단위로 증가

--------------------------------------------

테이블스페이스 생성

--------------------------------------

 

1. 데이터파일 raw device인 경우(9i~)

    SQL> CREATE TABLESPACE       테이블스페이스명
         datafile  '/dev/vg_data01/rlvol_test' [size  99M]
         EXTENT MANAGEMENT LOCAL     -- Default is LOCAL

                                     -- (LOCAL/DICTIONARY Managed Tablespace)
         [AUTOALLOCATE] or [UNIFORM SIZE 1M]  -- Auto Extent Size or Uniform Extent Size

                                              -- (initial이 지정되었을 경우 initial Size)
         SEGMENT SPACE MANAGEMENT AUTO        -- freelist 및 pctused 등을 자동으로 관리

         ;                                    -- (AUTO/MANUAL)

 

2. 데이터파일 file system인 경우
    SQL> CREATE TABLESPACE 테이블스페이스명
         datafile   '/data01/데이터파일명'  size  100M
         AUTOEXTEND ON  NEXT 10M

         MAXSIZE 4000M MINIMUM EXTENT 1M -- datafile에 대한 option 임
         EXTENT MANAGEMENT LOCAL         -- Default is LOCAL (LOCAL or DICTIONARY)
         [AUTOALLOCATE] or [UNIFORM SIZE 1M] -- Auto Extent Size or Uniform Extent Size

                                             -- (initial이 지정되었을 경우 initial Size)
         SEGMENT SPACE MANAGEMENT AUTO       -- freelist 및 pctused 등을 자동으로 관리

                                             -- (AUTO/MANUAL)

 

--------------------------------------------

테이블스페이스 관리

--------------------------------------

 

1. COALESCE

    SQL> alter tablespace 테이블스페이스명 coalesce ;

         - ORACLE 7, 8, 8i, 9i DICTIONARY Management Tablespace일 경우 사용

           (Locally Managed Tablespace는 coalesce가 필요 없음)
         - For each datafile in the tablespace,
           this clause coalesces all contiguous free extents

           into larger contiguous extents. 

 

2. RENAME

    SQL> ALTER TABLESPACE 구테이블스페이스명 RENAME TO 신규테이블스페이스명;

         - 9i이하에서는 지원하지 않음, 10g부터 지원
         - 사용자의 DEFAULT TABLESPACE 함께 변경됨
           SQL> SELECT * FROM DBA_USERS;
           SQL> ALTER USER 사용자명 DEFAULT TABLESPACE 테이블스페이스명;

 

3. Read/Write

    SQL> alter tablespace 테이블스페이스명 read only ;

         - read only 테이블스페이스는 온라인(HOT) 백업에서 제외됨

   SQL> alter tablespace 테이블스페이스명 read write ;

 

4. Online/Offline

   SQL> alter tablespace 테이블스페이스명 online;

   SQL> alter tablespace 테이블스페이스명 offline;

 

5. Drop

   SQL> drop tablespace 테이블스페이스명;

   SQL> drop tablespace 테이블스페이스명 including contents cascade;

   SQL> drop tablespace 테이블스페이스명 including contents cascade constraints;
   SQL> DROP TABLESPACE TBS1 INCLUDING CONTENTS AND DATAFILES;

        - (9i~) 데이타파일도 함께 삭제

 

6. LMT to DMT/DMT to LMT

   - 8.1.5는 LMT에서 DMT로 변환만이 가능
   - 8.1.6 이상부터 LMT와 DMT 상호변환 가능
   - 테이블스페이스의 Segment Management가 Auto로 되어 있거나 Compress되어 있으면
     테이블스페이스 마이그레이션이 되지 않음
   SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('테이블스페이스명');

   SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('테이블스페이스명');

 

7. 온라인(HOT) 백업

   SQL> ALTER TABLESPACE 테이블스페이스명 BEGIN BACKUP;
        테이블스페이스에 속하는 데이터파일들 cp 명령어 또는 dd 명령어로 복사
   SQL> ALTER TABLESPACE 테이블스페이스명 END   BACKUP;

 

8. 데이터파일이 손상되었을 때 데이터파일 offline drop 후 복구
   SQL> alter database datafile '/data01/데이터파일명' offline drop;

   - 해당 테이블스페이스 온라인 백업 불가

 

9. 데이터파일에 사용하는 세그먼트가 없을 경우 해당 데이터파일 DROP 가능
   SQL> alter tablespace drop datafile '/data01/데이터파일명';

 

10. 사용량/사용율 조회

    SQL> SELECT A.TABLESPACE_NAME,
                SUM(A.BYTES)/1024                                       AS "TOTAL(KB)",
                SUM(A.BYTES)/1024 - NVL(SUM(SZ_KB),0)                   AS "USED(KB)",
                NVL(SUM(SZ_KB),0)                                       AS "FREE(KB)",
                NVL(( 1. - SUM(SZ_KB)/(SUM(A.BYTES)/1024.) ) * 100,100) AS "USED RATIO(%)"
           FROM DBA_DATA_FILES A,
                ( SELECT FILE_ID  ,
                         SUM(BYTES)/1024. AS SZ_KB
                    FROM DBA_FREE_SPACE
                   GROUP BY FILE_ID
                ) B
          WHERE A.FILE_ID = B.FILE_ID(+)
          GROUP BY A.TABLESPACE_NAME;