테이블스페이스 관리

 

[참고 - Locally managed tablespace]

1. 9i부터 Locally managed tablespace가 디폴트임

2. Tablespace의 할당된 extents와 Free extents 정보를 datafile header 부분에 Bitmap 형태(0:free extent, 1:used extent)로 저장

3. 해당 테이블스페이스 내의 모든 Extent 크기는 동일, 기본적으로 extent의 size가 system에 의해서 자동적으로 정해짐 (64K)

4. Local 상태에서는 Default Storage 옵션은 무시됨

5. 세그먼트(테이블,인덱스) 생성시 지정하는 스토리지 옵션도 무시됨

   - 예외) INITIAL은 변경 가능, 200K 지정시 64K EXTENT 4개가 할당됨 

6. extent의 size를 지정하기 위해 UNIFORM 옵션을 사용함

   - EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M;

 

 

[LOCALLY MANAGED TABLESPACES의 장점] 

1. DMT 의 경우에는 세그먼트 마다 다른 extent 사이즈를 사용하게 되므로 Extent 할당, 해제가 반복되면서 Fragmentation 의 가능성이 많지만

   LMT (특히 System-Managed) 의 경우에는 Tablespace Fragmentation의 가능성이 없어짐

2. 데이타의 꾸준한 증가로 extent 횟수가 많아지는 오브젝트들을의 extent 횟수를 모니터링하고 관리할 필요가 없어짐

3. extent 할당, 해제시에 발생하는 Dictionary Table 수정을 위한 recursive SQL이 발생하지 않으므로 Performance 향상 (내부적 latch 감소)

4. Segment마다 INITIAL, NEXT, PCTINCREASE, MAXEXTENTS 등의 Storage 파라미터를 지정하고, 관리해야 등의 Segment 관리작업이 거의 필요없게 됨

 

 

-- 일반 테이블스페이스 예제 (CREATE TABLESPACE POBY DATAFILE '/data/NHICEDI/poby.dbf' SIZE 10M 와 동일)

CREATE TABLESPACE POBY DATAFILE 

  '/data/NHICEDI/poby.dbf' SIZE 10M AUTOEXTEND OFF

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

 

-- 시스템 테이블스페이스 예제

CREATE TABLESPACE SYSTEM DATAFILE 

  '/data/NHICEDI/system01.dbf' SIZE 6864M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

FLASHBACK ON;

 

-- TEMP 테이블스페이스 예제

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 

  '/data/NHICEDI/temp01.dbf' SIZE 1024M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

TABLESPACE GROUP ''

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 

 

-- 테이블스페이스 생성1 (테이블스페이스 생성2와 동일)

CREATE TABLESPACE BABY DATAFILE 

  'C:\ORACLE\ORADATA\ORCL\BABY01.DBF' SIZE 100M [AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED];

  

-- 테이블스페이스 생성2

CREATE TABLESPACE BABY DATAFILE 

  'C:\ORACLE\ORADATA\ORCL\BABY01.DBF' SIZE 100M [AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED]

  LOGGING

  ONLINE

  PERMANENT

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE

  BLOCKSIZE 4K

  SEGMENT SPACE MANAGEMENT AUTO;

 

-- 테이블스페이스 생성3 (모든 익스텐트의 크기를 2M로 생성)

CREATE TABLESPACE BABY DATAFILE

  'C:\ORACLE\ORADATA\ORCL\BABY01.DBF' SIZE 100M [AUTOEXTEND ON NEXT 10M MAXSIZE 500M]

  EXTENT MANAGEMENT LOCAL

  UNIFORM SIZE 2M;

 

-- 테이블스페이스 생성4

CREATE TABLESPACE BABY DATAFILE 

  'C:\ORACLE\ORADATA\ORCL\BABY01.DBF' SIZE 100M [AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED]

  DEFAULT STORAGE ( 

                    INITIAL     20K

                    NEXT        20K

                    MINEXTENTS  1

                    MAXEXTENTS  249

                    PCTINCREASE 50

                  )

  EXTENT MANAGEMENT DICTIONARY;

 

-- 테이블스페이스 추가

ALTER TABLESPACE BABY ADD DATAFILE 

  'C:\ORACLE\ORADATA\ORCL\BABY02.DBF' SIZE 100M [AUTOEXTEND ON NEXT 10M MAXSIZE 500M];

  

-- AUTOEXTEND 중지

ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORCL\BABY02.DBF' AUTOEXTEND OFF;  

 

-- Default Storage 옵션절의 수정

ALTER TABLESPACE BABY MINIMUM EXTENT 2M; 

ALTER TABLESPACE BABY DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);

 

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

ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORCL\BABY02.DBF' RESIZE 300M;

 

-- 테이블스페이스 삭제

DROP TABLESPACE BABY INCLUDING CONTENTS;

 

-- USER01 유저가 소유한 테이블에 대하여 테이블스페이스 변경

SELECT 'ALTER TABLE USER01.'||TNAME||' MOVE TABLESPACE TS_DATA;' FROM TAB WHERE TABTYPE='TABLE';

ALTER TABLE USER01.TAB01 MOVE TABLESPACE TS_DATA;

 

-- USER01 유저가 소유한 인덱스에 대하여 테이블스페이스 변경

SELECT 'ALTER INDEX USER01.'||INDEX_NAME||' REBUILD TABLESPACE TS_INDX;' FROM USER_INDEXES;

ALTER INDEX USER01.IX_TAB01_01 REBUILD TABLESPACE TS_INDX;

 

-- 파티션 이동

ALTER TABLE PARTS MOVE PARTITION DEPOT2 TABLESPACE TOOLS;

-- 파티션 인덱스 리빌드

ALTER INDEX PARTS2_IDX REBUILD PARTITION DEPOT02 TABLESPACE TOOLS;

 

-- 테이블스페이스 정보 조회

SELECT TABLESPACE_NAME, INITIAL_EXTENT,NEXT_EXTENT,PCT_INCREASE,EXTENT_MANAGEMENT, ALLOCATION_TYPE 

  FROM DBA_TABLESPACES 

 WHERE TABLESPACE_NAME = 'BABY';

 

-- 데이터파일 정보 조회

select tablespace_name, file_name, bytes 

  from dba_data_files;

  

-- Tablespace 별 여유공간의 량

select tablespace_name, sum(bytes)

  from dba_free_space 

 group by tablespace_name;

  

-- tablespace_name별로 총용량, 실제사용량, 여유공간 확인

select a.tablespace_name, 

       round(총용량) 총용량, 

       round(nvl(실제사용량,0)) 실제사용량, 

       round(총용량 - nvl(실제사용량,0)) 여유공간

  from (select tablespace_name, sum(bytes) 총용량

          from dba_data_files

         group by tablespace_name) a, 

       (select tablespace_name, sum(bytes) 실제사용량

          from dba_extents 

         group by tablespace_name) b

 where a.tablespace_name = b.tablespace_name(+);

 

SELECT   A.TABLESPACE_NAME, A.BYTES BYTES_USED, B.BYTES BYTES_FREE, B.LARGEST,

         ROUND (((A.BYTES - B.BYTES) / A.BYTES) * 100, 2) PERCENT_USED

    FROM (SELECT   TABLESPACE_NAME, SUM (BYTES) BYTES

              FROM DBA_DATA_FILES

          GROUP BY TABLESPACE_NAME) A,

         (SELECT   TABLESPACE_NAME, SUM (BYTES) BYTES, MAX (BYTES) LARGEST

              FROM DBA_FREE_SPACE

          GROUP BY TABLESPACE_NAME) B

   WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME

     AND A.TABLESPACE_NAME = 'DMDTS'

ORDER BY ((A.BYTES - B.BYTES) / A.BYTES) ASC;

 

-- 데이터 파일별 사용량 확인

select b.tablespace_name, b.file_name, b.bytes "총용량",

   b.bytes - sum(nvl(a.bytes,0)) "사용량",

       sum(nvl(a.bytes,0)) "남은용량",

   sum(nvl(a.bytes,0))/nvl(b.bytes,0)*100 "여유비율"

  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;

 

-- user별로 사용하고 있는 실제 사용공간 확인(index를 포함한다)

select owner, segment_type, round(sum(bytes)) 실제사용량

  from dba_extents 

 group by owner, segment_type;

 

select owner, segment_type, round(sum(bytes)) 실제사용량

  from dba_segments

 group by owner, segment_type;

 

-- 특정 세그먼트의 정보와 사용량 확인

select segment_name, initial_extent, next_extent, max_extents, extents, bytes

  from dba_segments

 where segment_name = 'DF041DM';

 

 

출처 : http://blog.naver.com/jyhwan/150135289665