"ORACLE 테이블스페이스"의 두 판 사이의 차이

DB CAFE

2024년 4월 22일 (월) 23:59 기준 최신판

추천메뉴 바로가기

DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
튜닝 { 오라클 튜닝 목록 }
모델링 { 데이터 모델링 가이드 }

1 테이블 스페이스 (TABLESPACE)[편집]

1.1 TABLESPACE 목록 조회[편집]

SELECT A.TABLESPACE_NAME
     , FILE_NAME
     , BYTES / 1024 / 1024
     , MAXBYTES / 1024 / 1024
     , AUTOEXTENSIBLE
     , B.BLOCK_SIZE
     , INCREMENT_BY * B.BLOCK_SIZE / 1024 / 1024
  FROM DBA_DATA_FILES A
     , DBA_TABLESPACES B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY B.BLOCK_SIZE, A.TABLESPACE_NAME, A.FILE_ID;

1.2 TABLESPACE 종류[편집]

테이블스페이스 사이즈 축소 작업 순서

1) SYSTEM TABLESPACE

- 데이터 딕셔너리 정보들이 저장되어 있음, 이 TABLESPACE 가 손상될 경우 Oracle 서버가 시작이 안됨

- SYS계정 소유이지만 조회만 가능

- 딕셔너리 주요 정보

데이터베이스의 논리적인 구조와 물리적인 구조 정보들
객체의 정의와 공간 사용 정보들
제약조건에 관련된 정보들
사용자에 관련된 정보들
Role, Privilege 등에 관련된 정보들
감사 및 보안등에 관련된 정보들

2) SYSAUX TABLESPACE

: 10g ~ , Oracle 서버의 성능 튜닝을 위한 데이터 저장

3) 데이터 TABLESPACE

: 가장 일반적으로 많이 사용되는 TABLESPACE 로 DBA가 필요에 의해 만드는 TABLESPACE .

DBA에 의해 생성,삭제.

1.3 현재 유저의 DEFAULT TABLESPACE 확인[편집]

SELECT * FROM USER_USERS ;-- DEFAUT TABLESPACE로 설정된 부분을 확인

1.3.1 유저의 DEFAULT TABLESPACE 변경[편집]

ALTER USER [유저명] DEFAULT TABLESPACE [테이블 스페이스명]

1.4 TABLESPACE 생성 구문[편집]

CREATE [BIGFILE/SMALLFILE] TABLESPACE TABLESPACE명
DATAFILE '파일경로1' SIZE integer [M/K], '파일경로2' SIZE integer [M/K]
[ MINIMUM EXTENT integer [M/K]]
[ BLOCKSIZE integer [K]] 
[ DEFAULT STORAGE (
  INITIAL integer [M/K]
  NEXT integer [M/K]
  MAXEXTENTS integer
  MINEXTENTS integer
  PCTINCREASE integer)]
[ ONLINE | OFFLINE ]
[ PERMANENT | TEMPORARY ]
[ EXTENT MANAGEMENT [ DICTIONARY | LOCAL
[ AUTOALLOCATE | UNIFORM [ SIZE integer [M/K]]]]]
[ SEGMENT SPACE MANAGEMENT [ MANUAL | AUTO]]
;

-- AUTOEXTEND ON NEXT 10M ==> (옵션)데이타 파일 용량초과시 자동증가설정
-- MAXSIZE 100M ==> (옵션)데이타파일 최대크기지정
-- EXTENT MANAGEMENT LOCAL ==> (옵션)
-- UNIFORM SIZE 1M ==> (옵션)
-- DEFAULT STORAGE(
:   INITIAL 80K  ==> 테이블 스페이스의 맨 첫번째 EXTENTS의 크기
:   NEXT 80K  ==> 다음 EXTENTS의 크기
:   MINNEXTENTS 1  ==> 생성할 EXTENTS의 최소값
:   MAXNEXTENTS 121  ==> 생성할 EXTENTS의 최대값
:   PCTINCREASE 80  ==> EXTENTS의 증가율,(DEFAULT값은 50%)
:   )

1.4.1 BIGFILE 테이블스페이스[편집]

8K 블록 - bigfile 테이블 스페이스에는 최대 32TB
32K 블록 - bigfile 파일 테이블 스페이스에는 최대 128TB

1.4.1.1 BIGFILE 테이블스페이스 장점[편집]

1.4.1.2 자동 수행 뷰 목록[편집]

장점

CREATE DATABASE 및 CREATE CONTROLFILE 문의 DB_FILES 초기화 매개 변수와 MAXDATAFILES 매개 변수를 조정하여 데이터 파일 정보에 필요한 SGA 공간의 양과 제어 파일의 크기를 줄일 수 있다.
Bigfile 테이블 스페이스는 데이터 파일 투명성을 제공하여 데이터베이스 관리를 단순화합니다.
ALTER TABLESPACE 문의 SQL 구문을 사용하면 기본 개별 데이터 파일이 아닌 테이블 스페이스에서 작업을 수행 할 수 있다.
빅파일 테이블 스페이스는 자동 세그먼트 공간 관리가있는 로컬 관리 테이블 스페이스에 대해서만 가능함. (언두,템프,시스템 테이블스페이스는 지원 안함)

1.4.1.3 BIGFILE 테이블스페이스 단점[편집]

BIGFILE 테이블스페이스 단점

병렬 쿼리 실행 및 RMAN 백업 병렬화에 부정적인 영향을 미치므로 스트라이핑을 지원하지 않는 시스템에서 빅 파일 테이블 스페이스를 생성하지 마십시오.
큰 파일 크기를 지원하지 않는 OS 플랫폼에서 bigfile 테이블 스페이스를 사용하는 것은 권장되지 않으며 테이블 스페이스 용량을 제한 할 수 있습니다.
Bigfile 테이블 스페이스는 ASM (Automatic Storage Management) 또는 스트라이핑 또는 RAID를 지원하는 기타 논리 볼륨 관리자 및 동적으로 확장 가능한 논리 볼륨과 함께 사용하기위한 것입니다.

1.5 UNDO TABLESPACE 생성[편집]

CREATE UNDO TABLESPACE [테이블 스페이스명] ==> 이부분만 다름.
              DATAFILE '\경로\TEST_UNDO.DBF'
                  SIZE 10M
            AUTOEXTEND ON NEXT 10M
               MAXSIZE 100M
-- UNDO_MANAGEMENT와 UNDO_TABLESPACE, UNDO_RETENTION PARAMETER를 제공
-- 지역적으로 관리되는 익스텐트만 사용가능
-- 시스템에 의해 관리되는 익스텐트 할당만 사용가능하다.
--        (UNIFORMSIZE를 할당 할 수 없고 AUTOALLOCATE만 가능)

1.6 TABLESPACE 변경[편집]

1.6.1 TABLESPACE 읽기/쓰기 모드 변경[편집]

읽기 전용 모드

alter tablespace <TABLESPACE명> read only;

읽기 쓰기 모드

alter tablespace <TABLESPACE명> read ,write;

1.6.2 OPEN 상태에서 DATAFILE 이동[편집]

alter tablespace TABLESPACE명 offline;

1.6.2.1 offline 된 T/S에 대해 복사/이동[편집]

cp /data1/xxx.dbf /data2/xxx.dbf
or 
mv /data1/xxx.dbf /data2/xxx.dbf

1.6.2.2 OFFLINE 상태에서 DATAFILE 이동[편집]

alter tablespace TABLESPACE명 rename datafile '파일경로1/xxx.dbf' to '파일경로2/xxx.dbf';
alter tablespace TABLESPACE명 online;

1.6.3 MOUNT 상태에서 DATAFILE 이동[편집]

startup mount;

1.6.3.1 해당 T/S에 대해 복사/이동 후[편집]

alter database rename file '파일경로' to '파일경로';
alter database open;

모든 데이타 파일은 mount상태에서 복사/이동 가능
system 파일은 mount상태에서만 복사/이동 가능

1.7 테이블의 TABLESPACE MOVE[편집]

ALTER TABLE [테이블명] 
       MOVE TABLESPACE [테이블 스페이스명]

-테이블스페이스 이동시 인덱스 리빌드 필요

ALTER INDEX [인덱스명] REBUILD ;

인덱스 리빌드 동시 수행 시

ALTER TABLE [테이블명] 
       MOVE TABLESPACE [테이블 스페이스명]
       UPDATE INDEXES;

1.7.1 파티션닝 테이블 TABLESPACE MOVE[편집]

ALTER TABLE [테이블명] 
       MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명]

파티셔닝 테이블 인덱스 REBUILD

ALTER INDEX [인덱스명] 
       REBUILD PARTITION [파티션명]

1.8 운영중인 테이블 TABLESPACE ONLINE MOVE[편집]

ALTER TABLE [테이블명] 
       MOVE TABLESPACE [테이블 스페이스명]
     ONLINE -- online 옵션
;

인덱스 리빌드 동시 수행 시

ALTER TABLE [테이블명] 
       MOVE TABLESPACE [테이블 스페이스명]
       UPDATE INDEXES
     ONLINE -- online 옵션
;

1.8.1 운영중인 파티션닝 테이블 TABLESPACE ONLINE MOVE[편집]

ALTER TABLE [테이블명] 
       MOVE PARTITION [파티션명] TABLESPACE [테이블 스페이스명]
     ONLINE

온라인 Move 작업시 장단점

Move 작업 중 다른 세션의 DML 작업이 가능함
파티션의 로컬인덱스는 USABLE 상태로 바로 사용가능함(unusable 되지 않음)
도메인 인덱스가 포함된 테이블은 사용불가
온라인 Move 작업시 parallel DML , Direct Path Insert 가 안됨(즉,성능은 좋지않다)

1.9 TABLESPACE 사이즈 변경(RESIZE)[편집]

ALTER TABLESPACE ts_txxxx 
          RESIZE 500G;

1.10 TABLESPACE 이름 변경[편집]

ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]

1.11 인덱스의 TABLESPACE 변경[편집]

ALTER INDEX 인덱스명 REBUILD TABLESPACE [테이블 스페이스명]
[ PARALLEL parallel_num ]
[ LOGGING or NOLOGGING ]
;

1.12 인덱스의 TABLESPACE 변경 스크립트[편집]

SELECT 'ALTER INDEX '||index_name||' REBUILD TABLESPACE TS_OO_'||substr(a.index_name,4,2)||'_I;' 
  FROM  user_indexes a
 WHERE a.index_name NOT IN (SELECT index_name from user_part_indexes)
   AND substr(a.index_name,4,2) IN (SELECT SUBJECT_CD FROM TB_DBA_SUBJECT_CD);

1.13 인덱스/테이블 TABLESPACE 변경 스크립트[편집]

SELECT DECODE (segment_type, 'TABLE', segment_name, table_name)  order_col1
       , DECODE (segment_type, 'TABLE', 1, 2)   order_col2
       , 'alter '
         || segment_type
         || ' '
         || segment_name
         || DECODE (segment_type, 'TABLE', ' MOVE ', ' REBUILD ')
         || CHR (10)
         || ' TS_오너_01 '
         || CHR (10)
         || ' storage ( initial '
         || initial_extent
         || ' next '
         || next_extent
         || CHR (10)
         || ' minextents '
         || min_extents
         || ' maxextents '
         || max_extents
         || CHR (10)
         || ' pctincrease '
         || pct_increase
         || ' freelists '
         || freelists
         || ');'
    FROM user_segments, (SELECT table_name, index_name FROM user_indexes)
   WHERE segment_type IN ('TABLE', 'INDEX') AND segment_name = index_name(+)
ORDER BY 1, 2;

1.14 TABLESPACE 자동증가/최대 사이즈 변경[편집]

ALTER TABLESPACE ts_txxxx
      AUTOEXTEND ON 
            NEXT 100M  
         MAXSIZE 500G;-- 최대사이즈 , UNLIMITED => 무제한 증가

최대 TABLESPACE 사이즈 변경

ALTER TABLESPACE [테이블 스페이스명] 
             ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M
      AUTOEXTEND ON NEXT 100M 
         MAXSIZE 100G;

  ==> 10M씩 자동증가

1.15 데이터파일 사이즈 변경[편집]

ALTER DATABASE DATAFILE 'C:\경로\TEST1.DBF' 
        RESIZE 10M;

1.16 데이터파일 추가[편집]

ALTER TABLESPACE [테이블 스페이스명] 
             ADD DATAFILE 'C:\경로\TEST2.DBF' SIZE 10M;

1.17 데이터/템프파일 삭제[편집]

ALTER TABLESPACE [테이블 스페이스명] 
             DROP DATAFILE 'data파일';
-- 템프TS    DROP TEMPFILE 'data파일';

1.18 데이터 파일 삭제 후 OS 디스크 사이즈가 줄지 않을경우[편집]

1) 테이블스페이스 사이즈를 줄인 (shrink) 후 데이터파일 삭제

     2) DB 리부팅 하면 공간 확보 됨(DBWR 이 데이터파일을 사용중이기 때문에 공간이 줄지 않음)

1.19 TABLESPACE 삭제[편집]

1.19.1 TABLESPACE에 포함된 모든 세그먼트 삭제[편집]

DROP TABLESPACE [테이블 스페이스명] 
      INCLUDING CONTENTS;-- TABLESPACE의 모든 세그먼트를 삭제한다.
                         -- 단, 데이타가 있는 TABLESPACE는 삭제할수 없다.

1.19.2 TABLESPACE에 포함된 테이블의 참조/제약 조건 삭제[편집]

DROP TABLESPACE [테이블 스페이스명] 
        CASCADE CONSTRAINTS; -- 삭제된 TABLESPACE 내의 테이블의 기본키와 유일키를 참조하는
                             -- 다른 TABLESPACE의 테이블로부터 참조무결성 제약 조건을 삭제한다.

1.19.3 TABLESPACE의 데이터파일 삭제[편집]

DROP TABLESPACE [테이블 스페이스명] 
      INCLUDING CONTENTS AND DATAFILES; -- 물리적파일까지 삭제한다.

1.20 TABLESPACE 수정이나 삭제시 ONLINE/OFFLINE 설정[편집]

ALTER TABLESPACE [테이블 스페이스명] ONLINE
ALTER TABLESPACE [테이블 스페이스명] OFFLINE

1.21 템프 TABLESPACE[편집]

1.21.1 TEMPORARY TABLESPACE 정보[편집]

SELECT d.TABLESPACE_NAME
       , d.FILE_NAME
       , d.BYTES / 1024 / 1024                             SIZE_MB
       , d.AUTOEXTENSIBLE
       , d.MAXBYTES / 1024 / 1024                          MAXSIZE_MB
       , d.INCREMENT_BY * (v.BLOCK_SIZE / 1024) / 1024     INCREMENT_BY_MB
    FROM dba_temp_files d, v$tempfile v
   WHERE d.FILE_ID = v.FILE#
ORDER BY d.TABLESPACE_NAME, d.FILE_NAME

1.21.1.1 템프테이블 사이즈 조정[편집]

ALTER DATABASE TEMPFILE 'D:\APP\ORADATA\ORCL\TEMP01.DBF'
RESIZE 304M;

1.21.2 TEMPORARY TABLESPACE 생성[편집]

. 신규생성만 가능.

: 사용중인 TEMP TABLESPACE 는 삭제가 안되므로 신규로 템프 TABLESPACE를 생성

CREATE TEMPORARY TABLESPACE [테이블 스페이스명]
        TEMPFILE '\경로\TEST_TEMP.DBF'
            SIZE 10M
      AUTOEXTEND ON 
            NEXT 100M 
         MAXSIZE 300G
          EXTENT MANAGEMENT LOCAL
         UNIFORM SIZE 512K -- LOCALY MANAGED TABLESPACE NUIFORM SIZE만 생성가능하다.
                           -- (주의)AUTOALLOCATE, EXTENT MANAGEMENT DICIONARY OPTION을 사용하면 ORA-25319 ERROR 발생한다.
                           --  RENAME 이 불가능하다.

1.21.3 TEMPORARY TABLESPACE를 DEFAULT TABLESPACE로 변경[편집]

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE [테이블 스페이스명];

1.21.4 TEMPORARY TABLESPACE 사이즈 증가[편집]

ALTER TABLESPACE TEMP  
      AUTOEXTEND ON 
            NEXT 100M  
         MAXSIZE 500G;

1.21.5 TEMPORARY TABLESPACE 사이즈 추가[편집]

ALTER TABLESPACE TEMP 
             ADD TEMPFILE '+DATA' SIZE 10G 
      AUTOEXTEND ON 
            NEXT 100M 
         MAXSIZE 32767M;

1.21.6 TEMPORARY TABLESPACE 삭제[편집]

DROP TABLESPACE TEMP2;

1.21.7 TEMPORARY TABLESPACE DATA FILE 삭제[편집]

ALTER TABLESPACE TEMP DROP TEMPFILE '+DATA/temp.368.1013282149';

- +DATA/temp.368.1013282149 은 ASM 사용시

1.21.8 TEMPORARY TABLESPACE 사용율 조회 쿼리[편집]

/* TEMP 사용 쿼리 확인 */

select a.username, a.sid, a.serial#, b.blocks, a.program, a.module, a.action, a.machine, a.status, a.event, d.sql_Text ,
      b.blocks*8192/1024/1024 mb
 from v$session a,
      v$sort_usage b,
      v$process c,
      v$sqlarea d
where  a.saddr = b.session_addr
and a.paddr = c.addr
and a.sql_hash_value= d.hash_value
and b.tablespace like 'TEMP%'
--and a.username ='MIG_ADM'
ORDER BY A.MACHINE, SQL_TEXT

1.21.9 템프테이블 TABLESPACE sort 사용 현황[편집]

SELECT 
   A.tablespace_name tablespace, 
   D.mb_total,
   SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
   D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM 
   v$sort_segment A,
(
SELECT 
   B.name, 
   C.block_size, 
   SUM (C.bytes) / 1024 / 1024 mb_total
FROM 
   v$tablespace B, 
   v$tempfile C
WHERE 
   B.ts#= C.ts#
GROUP BY 
   B.name, 
   C.block_size
) D
WHERE 
   A.tablespace_name = D.name
GROUP by 
   A.tablespace_name, 
   D.mb_total

2 TABLESPACE 오브젝트별 정보[편집]

2.1 TABLESPACE/파일 확인(딕셔너리)[편집]

SELECT * FROM DBA_DATA_FILES ; 
SELECT * FROM DBA_TABLESPACES ;
SELECT * FROM DBA_SEGMENTS;

2.2 TABLESPACE별 정보[편집]

SELECT A.TABLESPACE_NAME AS "TABLESPACE"
     , A.INITIAL_EXTENT / 1024 AS "INIT(K)"
     , A.NEXT_EXTENT / 1024 AS "NEXT(K)"
     , A.MIN_EXTENTS AS "MIN"
     , A.MAX_EXTENTS AS "MAX"
     , A.PCT_INCREASE AS "PCT_INC(%)"
     , B.FILE_NAME AS "FILE_NAME"
     , B.BLOCKS * C.VALUE / 1024 / 1024 AS "SIZE(M)"
     , B.STATUS AS "STATUS"
  FROM DBA_TABLESPACES A
     ,  DBA_DATA_FILES B
     ,  V$PARAMETER C
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME 
   AND C.NAME = 'db_block_size' 
 ORDER BY 1, 2;

2.3 TABLESPACE별 파일 목록[편집]

SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS "TABLESPACE"
     , SUBSTRB(FILE_NAME, 1, 50) AS "파일명"
     , TO_CHAR(BLOCKS, '999,999,990') AS "블럭수"
     , TO_CHAR(BYTES, '99,999,999') AS "크기"
  FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME, FILE_NAME;

2.4 TABLESPACE 사이즈 정보[편집]

2.4.1 TABLESPACE별 전체 오브젝트(테이블/인덱스/LOB) 사이즈[편집]

-- OBJECT별 테이블 사이즈 (UNDO,TEMP T/S 제외)

-- SELECT TABLESPACE_NAME
--      , SUM(SIZE_MB)
--   FROM (     
SELECT  A.TABLESPACE_NAME
      , A.SEGMENT_NAME
      , A.SEGMENT_TYPE      
      , ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB"      
  FROM DBA_SEGMENTS A
     , DBA_TABLES B
 WHERE A.SEGMENT_NAME = B.TABLE_NAME
   AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')
--     AND A.OWNER = '유저아이디'
 GROUP BY A.TABLESPACE_NAME
        , A.SEGMENT_NAME
        , A.SEGMENT_TYPE
UNION ALL

-- INDEX SIZE
SELECT  A.TABLESPACE_NAME
      , A.SEGMENT_NAME
      , A.SEGMENT_TYPE
      , ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB"
  FROM DBA_SEGMENTS A
     , DBA_INDEXES B
 WHERE A.SEGMENT_NAME = B.INDEX_NAME   
   AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')                    
--     AND A.OWNER = '유저아이디'
 GROUP BY A.TABLESPACE_NAME
        , A.SEGMENT_NAME
        , A.SEGMENT_TYPE
-- ORDER BY 2 DESC;
UNION ALL 
-- LOB  
SELECT  A.TABLESPACE_NAME
      , A.SEGMENT_NAME
      , A.SEGMENT_TYPE
      , ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB"
   FROM DBA_SEGMENTS A
      , DBA_LOBS B
  WHERE A.segment_name = B.segment_name  
    AND A.SEGMENT_TYPE LIKE 'LOB%'      
 GROUP BY A.TABLESPACE_NAME
        , A.SEGMENT_NAME
        , A.SEGMENT_TYPE        

 ORDER BY SEGMENT_TYPE,TABLESPACE_NAME DESC   
-- )
--  GROUP BY TABLESPACE_NAME 
;

2.4.2 TABLESPACE별 사이즈[편집]

SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
        Substr(df.file_name,1,80) "File Name",
        Round(df.bytes/1024/1024,0) "Size (M)",
        decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
        decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
        decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM    DBA_DATA_FILES DF,
       (SELECT file_id,
               sum(bytes) used_bytes
        FROM dba_extents
        GROUP by file_id) E,
       (SELECT sum(bytes) free_bytes,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE    e.file_id (+) = df.file_id
AND      df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name

2.4.3 테이블 TABLESPACE 사이즈[편집]

-- 테이블 사이즈

SELECT A.SEGMENT_NAME
      , A.SEGMENT_TYPE      
      , ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB"      
  FROM DBA_SEGMENTS A
     , DBA_TABLES B
 WHERE A.SEGMENT_NAME = B.TABLE_NAME
   AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')
--     AND A.OWNER = '유저아이디'
 GROUP BY A.SEGMENT_NAME
        , A.SEGMENT_TYPE

2.4.4 인덱스 TABLESPACE 사이즈[편집]

-- INDEX SIZE

SELECT A.SEGMENT_NAME
      , A.SEGMENT_TYPE
      , ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB"
  FROM DBA_SEGMENTS A
     , DBA_INDEXES B
 WHERE A.SEGMENT_NAME = B.INDEX_NAME   
   AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')                    
--     AND A.OWNER = '유저아이디'
 GROUP BY A.SEGMENT_NAME
        , A.SEGMENT_TYPE
 ORDER BY 2 DESC;

2.4.5 LOB TABLESPACE 사이즈[편집]

-- LOB 사이즈

SELECT TABLE_NAME
      , sum(bytes)
   FROM (SELECT B.table_name AS table_name
              , A.bytes
           FROM DBA_SEGMENTS A
              , DBA_LOBS B
          WHERE A.segment_name = B.segment_name                          
          )
group by table_name;

2.4.6 데이터파일 별 테이블스페이스 사이즈 조회[편집]

SELECT    A.TABLESPACE_NAME "테이블스페이스명",
          A.FILE_NAME "파일경로",
           (A.BYTES - B.FREE)    "사용공간",
            B.FREE                 "여유 공간",
            A.BYTES                "총크기",
            TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"
      FROM
       (
         SELECT FILE_ID,
                TABLESPACE_NAME,
                FILE_NAME,
                SUBSTR(FILE_NAME,1,200) FILE_NM,
                SUM(BYTES) BYTES
           FROM DBA_DATA_FILES
         GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)
       ) A,
       (
         SELECT TABLESPACE_NAME,
                FILE_ID,
                SUM(NVL(BYTES,0)) FREE
           FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME,FILE_ID
       ) B
      WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
         AND A.FILE_ID = B.FILE_ID;

2.4.7 TABLESPACE별 사용하는 파일의 크기 합[편집]

SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS TABLESPACE
     , TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES
     , TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS
  FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME 
UNION ALL 
SELECT '총계', TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES, TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS 
  FROM DBA_DATA_FILES;

2.4.8 TABLESPACE별 디스크 사용량[편집]

SELECT A.TABLESPACE_NAME AS "TABLESPACE"
     , A.INIT AS "INIT(K)"
     , A.NEXT AS "NEXT(K)"
     , A.MIN AS "MIN"
     , A.MAX AS "MAX"
     , A.PCT_INC AS "PCT_INC(%)"
     , TO_CHAR(B.TOTAL, '999,999,999,990') AS "총량(바이트)"
     , TO_CHAR(C.FREE, '999,999,999,990') AS "남은량(바이트)"
     , TO_CHAR(B.BLOCKS, '9,999,990') AS "총블럭"
     , TO_CHAR(D.BLOCKS, '9,999,990') AS "사용블럭"
     , TO_CHAR(100 * NVL(D.BLOCKS, 0) / B.BLOCKS, '999.99') AS "사용율%"
  FROM (SELECT TABLESPACE_NAME
                , INITIAL_EXTENT / 1024 AS INIT
                , NEXT_EXTENT / 1024 AS NEXT
                , MIN_EXTENTS AS MIN
                , MAX_EXTENTS AS MAX
                , PCT_INCREASE AS PCT_INC
           FROM      DBA_TABLESPACES) A
     , (SELECT    TABLESPACE_NAME, SUM(BYTES) AS TOTAL, SUM(BLOCKS) AS BLOCKS
           FROM      DBA_DATA_FILES
           GROUP BY  TABLESPACE_NAME) B
     , (SELECT    TABLESPACE_NAME, SUM(BYTES) AS FREE
           FROM      DBA_FREE_SPACE
           GROUP BY  TABLESPACE_NAME) C
     , (SELECT    TABLESPACE_NAME, SUM(BLOCKS) AS BLOCKS
           FROM      DBA_EXTENTS
           GROUP BY  TABLESPACE_NAME) D
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) 
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+) 
   AND A.TABLESPACE_NAME = D.TABLESPACE_NAME(+) 
 ORDER BY A.TABLESPACE_NAME;

2.4.9 공간의 90% 이상을 사용하고 있는 TABLESPACE[편집]

SELECT X.TABLESPACE_NAME
     , TOTAL_SIZE / 1024 / 1024 TOTAL_SIZE
     , USED_SIZE / 1024 / 1024 USED_SIZE
     , (ROUND(USED_SIZE / TOTAL_SIZE, 2)) * 100 USED_RATIO
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_SIZE
           FROM      DBA_DATA_FILES
           GROUP BY  TABLESPACE_NAME) X
     , (SELECT    TABLESPACE_NAME, SUM(BYTES) USED_SIZE
           FROM      DBA_EXTENTS
           GROUP BY  TABLESPACE_NAME) Y
 WHERE X.TABLESPACE_NAME = Y.TABLESPACE_NAME(+) AND Y.USED_SIZE > .9 * X.TOTAL_SIZE;

2.5 TABLESPACE에 포함된 테이블 명 보기[편집]

SELECT TABLESPACE_NAME
     , TABLE_NAME 
  FROM DBA_TABLES 
 WHERE TABLESPACE_NAME = UPPER('&TABLESPACE명') 
 ORDER BY TABLESPACE_NAME
     , TABLE_NAME;

2.6 오브젝트별 TABLESPACE 및 데이터파일[편집]

SELECT DISTINCT E.SEGMENT_NAME, E.TABLESPACE_NAME, F.FILE_NAME
  FROM DBA_EXTENTS E
     , DBA_DATA_FILES F
 WHERE E.FILE_ID = F.FILE_ID 
   AND E.SEGMENT_TYPE = 'TABLE' 
   AND E.TABLESPACE_NAME NOT IN ('SYSTEM', 'TOOLS');

2.7 TABLESPACE별 Table, Index 개수[편집]

SELECT OWNER
     , TABLESPACE_NAME
     , SUM(DECODE(SEGMENT_TYPE, 'TABLE', 1, 0))          TAB
     , SUM(DECODE(SEGMENT_TYPE, 'INDEX', 1, 0))          IDX
     , SUM(DECODE(SEGMENT_TYPE, 'LOBINDEX', 1, 0))       LOB_IDX
     , SUM(DECODE(SEGMENT_TYPE, 'LOBSEGMENT', 1, 0))     LOB_SEG
  FROM DBA_SEGMENTS 
 WHERE SEGMENT_TYPE IN ('TABLE', 'INDEX', 'LOBINDEX','LOBSEGMENT')
 GROUP BY OWNER, TABLESPACE_NAME;

2.8 파일위치별 TABLESPACE 아는 방법[편집]

SELECT SUBSTRB(A.FILE_NAME, 1, 40) AS FILE_NAME
     , A.FILE_ID
     , B.FREE_BYTES / 1024 AS FREE_BYTES
     , B.MAX_BYTES / 1024 AS MAX_BYTES
  FROM DBA_DATA_FILES A
     , (SELECT FILE_ID, SUM(BYTES) AS FREE_BYTES, MAX(BYTES) AS MAX_BYTES
          FROM DBA_FREE_SPACE
         GROUP BY  FILE_ID) B
 WHERE A.FILE_ID = B.FILE_ID 
   AND A.TABLESPACE_NAME = UPPER('&TABLESPACE명') 
 ORDER BY A.FILE_NAME;

2.9 현재 Extension 횟수가 MaxExtents의 80% 이상인 경우[편집]

SELECT TABLESPACE_NAME
     , OWNER
     , SEGMENT_NAME
     , SEGMENT_TYPE
     , EXTENTS
     , MAX_EXTENTS
  FROM SYS.DBA_SEGMENTS S 
 WHERE EXTENTS / MAX_EXTENTS > .8 AND MAX_EXTENTS > 0 
 ORDER BY TABLESPACE_NAME, OWNER, SEGMENT_NAME;

2.10 테이블의 익스텐트 정보 조회[편집]

오라클에서 스토리지 구조는 아래와 같다.

테이블 스페이스 -> 세그먼트 -> 익스텐트 -> 블록 -> OS 범위 -> 데이터 파일 -> 운영체제 블록

   세그먼트의 이름,
   해당 세그먼트의 최대 익스텐트 개수,
   익스텐트 아이디
   해당 세그먼트의 최대 익스텐트 개수 - 최대 익스텐트 아이디

딕셔너리 관리 TABLESPACE로 생성한 것으로 조회를 한다.

SELECT B.SEGMENT_NAME
     , B.MAX_EXTENTS
     , MAX(C.EXTENT_ID) AS EXTENT_ID
     , B.MAX_EXTENTS - MAX(C.EXTENT_ID) AS DIFF
  FROM USER_TABLESPACES A
     , USER_SEGMENTS B
     , USER_EXTENTS C
 WHERE A.EXTENT_MANAGEMENT = 'DICTIONARY' 
   AND B.TABLESPACE_NAME = A.TABLESPACE_NAME 
   AND C.SEGMENT_NAME = B.SEGMENT_NAME 
 GROUP BY B.SEGMENT_NAME, B.MAX_EXTENTS 
HAVING B.MAX_EXTENTS - MAX(C.EXTENT_ID) <= 50 
 ORDER BY B.MAX_EXTENTS - MAX(C.EXTENT_ID);

3 UNDO TABLESPACE[편집]

UNDO Segment를 저장하고 있는 TABLESPACE , 관리자가 생성/관리 가능
Instance당 여러 개가 동시에 존재할 수 있지만 사용은 한번에 1개만

자동 관리 기법(AUM, Automatic Undo Management)

- Undo Data : 사용자가 DML을 수행할 경우 발생하는 원본데이터, Oracle Server Process가 직접 관리

         ex) 홍길동→일지매로 업데이트 시 홍길동이 Undo Data

- Undo Segment : Undo Data만을 저장하는 Segment

3.1 현재 UNDO 상태 확인[편집]

SELECT A.TABLESPACE_NAME,A.STATUS, B.TOTAL_MB, A.USE_MB,
       ROUND(RATIO_TO_REPORT(A.USE_MB) OVER(PARTITION BY A.TABLESPACE_NAME) * 100)||'%' AS PCT
  FROM (SELECT TABLESPACE_NAME, STATUS,
               ROUND(SUM(BYTES/1024/1024)) USE_MB
          FROM DBA_UNDO_EXTENTS
         GROUP BY TABLESPACE_NAME,STATUS
       )A,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024) TOTAL_MB
          FROM DBA_DATA_FILES
         WHERE TABLESPACE_NAME LIKE 'UNDO%'
         GROUP BY TABLESPACE_NAME
       )B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
 ORDER BY 1,2

3.2 UNDO 테이블 스페이스 삭제[편집]

DROP TABLESPACE UNDO_T1 INCLUDING CONTENTS AND DATAFILES;

3.3 ROLLBACK SEGMENT의 사용상황 보기[편집]

--: EXTENTS = 현재 할당된 EXTENT의 수 --: EXTENDS = 마지막 트랜잭션에 의해 할당된 EXTENT의 수

SELECT SUBSTRB(A.SEGMENT_NAME, 1, 10) AS SEGMENT_NAME
     , SUBSTRB(A.TABLESPACE_NAME, 1, 10) AS TABLESPACE_NAME
     , TO_CHAR(A.SEGMENT_ID, '99,999') AS SEG_ID
     , TO_CHAR(A.MAX_EXTENTS, '999,999') AS MAX_EXT
     , TO_CHAR(B.EXTENTS, '999,999') AS EXTENTS
     , TO_CHAR(B.EXTENDS, '999,999') AS EXTENDS
     , TO_CHAR((A.INITIAL_EXTENT + (B.EXTENTS - 1) * A.NEXT_EXTENT) / 1000000, '9,999.999') AS "ALLOC(MB)"
     , TO_CHAR(XACTS, '9,999') AS XACTS
  FROM DBA_ROLLBACK_SEGS A
     , V$ROLLSTAT B
 WHERE A.SEGMENT_ID = B.USN(+) ORDER BY 1;

4 TABLESPACE 장애 처리[편집]

4.1 TABLESPACE 용량 부족으로 에러 발생시[편집]

방법 1) Data file을 크게 늘려줌(자동 증가/수동증가)
방법 2) Data file을 하나 더 추가

4.1.1 Tablespace Offline[편집]

사용자가 더 이상 해당 Tablespace에 접근하지 못한다는 의미
데이터파일의 위치를 이동하거나 특정 Tablespace가 장애가 나서 복구해야 할 때 사용

4.1.2 TABLESPACE 를 Offline하는 방법 3가지[편집]

1) Normal Mode

SQL> alter TABLESPACE TS_TEST offline ;

2) Temporary Mode

Normal이 수행되지 못할 때(Tablespace의 Data file 이상) 사용하는 방법

3) Immediate Mode

- 반드시 Archive Log Mode일 경우에만 사용. - Data file에 장애가 나서 데이터를 내려쓰지 못하는 상황에서 TABLESPACE 를 offline해야 할 경우 사용

4.1.3 TABLESPACE 이동하기[편집]

1) Offline되는 Tablespace의 Data file 이동하기

해당 Tablespace Offline 하기
- ```
ALTER TABLESPACE TS_TEST OFFLINE;
```
Data file을 대상 위치로 복사
Control file 내의 해당 Data file 위치 변경
해당 Tablespace Online
- ```
ALTER TABLESPACE TS_TEST ONLINE;
```

2) Offline 안 되는 TABLESPACE 의 Data file 이동하기

DB 종료
Mount 상태로 시작
Data file 복사
Control file의 내용 변경
DB Open

5 LOB포함된 TABLESPACE 용량 축소/REORG[편집]