YesXYZ

아딸이 함께 만드는 세상

[Oracle] 테이블스페이스를 삭제하는 절차

by · 2026년 01월 13일

스키마를 삭제하더라도 OS의 디스크 여유 공간이 증가하지 않습니다. 스키마를 물이라고 생각하면 물을 담고 있는 그릇, 즉 테이블스페이스를 삭제해야 비로소 가용 디스크 용량이 증가합니다. 아래는 테이블스페이스를 안전하게 삭제하기 위한 절차입니다.

  1. 휴지통(Recycle Bin)에 들어있는 객체 확인 및 삭제
  2. 데이터가 없는 테이블스페이스 확인
  3. 테이블스페이스 및 데이터파일 상태 확인 및 변경(ONLINE -> OFFLINE)
  4. 테이블스페이스 사용여부 모니터링
  5. 테이블스페이스 삭제
  6. 미사용 데이터파일 존재 여부 확인

1. 휴지통(Recycle Bin)에 들어있는 객체 확인 및 삭제

  • DBA_RECYCLEBIN 뷰 및 DBA_SEGMENTS 뷰를 통해 휴지통 객체를 확인합니다. 
    SELECT R.OWNER,
       R.OBJECT_NAME,
       R.ORIGINAL_NAME,
       R.TYPE,
       R.TS_NAME,
       S.SEGMENT_NAME
FROM   DBA_RECYCLEBIN R,
       DBA_SEGMENTS S
WHERE  R.OWNER = S.OWNER(+)
AND    R.OBJECT_NAME = S.SEGMENT_NAME(+) ;
  • 휴지통 객체가 더 이상 필요없다고 판단되면 아래 명령어로 삭제합니다. PURGE를 실행하는 순간 영구 삭제되어 일반적인 방법으로는 복구가 불가능합니다. 
    PURGE DBA_RECYCLEBIN;

2. 테이터가 없는 테이블스페이스 확인

  • 아래 쿼리를 실행하여 데이터가 없는 테이블스페이스를 확인합니다. DBA_USERS뷰 뿐만 아니라, 모든 LOB(Large Object) 컬럼에 대한 메타데이터와 저장 정보를 담고 있는 관리자 뷰(DBA_LOBS)도 점검해야 합니다. 
    SELECT T.TABLESPACE_NAME,
       T.CONTENTS,       
       T.STATUS,
       NVL(S.SEG_COUNT, 0) AS SEGMENT_COUNT, -- 0이면 데이터가 하나도 없다는 뜻
     ( SELECT LISTAGG(U.USERNAME, ', ') WITHIN GROUP (ORDER BY U.USERNAME) 
       FROM   DBA_USERS U 
       WHERE  U.DEFAULT_TABLESPACE = T.TABLESPACE_NAME 
       OR     U.TEMPORARY_TABLESPACE = T.TABLESPACE_NAME ) AS USERS_LIST,
     ( SELECT LISTAGG(OWNER, ', ') WITHIN GROUP (ORDER BY OWNER)
       FROM  (SELECT DISTINCT TABLESPACE_NAME, OWNER FROM DBA_LOBS) L
       WHERE  L.TABLESPACE_NAME = T.TABLESPACE_NAME ) AS LOBS_LIST,
     ( SELECT LISTAGG(Q.USERNAME, ', ') WITHIN GROUP (ORDER BY Q.USERNAME)
       FROM   DBA_TS_QUOTAS Q
       WHERE  Q.TABLESPACE_NAME = T.TABLESPACE_NAME ) AS QUOTAS_LIST,
     ( SELECT LISTAGG(I.OWNER, ', ') WITHIN GROUP (ORDER BY I.OWNER)
       FROM  (SELECT DISTINCT TABLESPACE_NAME, OWNER FROM DBA_INDEXES) I
       WHERE  I.TABLESPACE_NAME = T.TABLESPACE_NAME) AS INDEX_LIST,
     ( SELECT LISTAGG(P.TABLE_OWNER, ', ') WITHIN GROUP (ORDER BY P.TABLE_OWNER)
       FROM  (SELECT DISTINCT TABLE_OWNER, TABLESPACE_NAME FROM DBA_TAB_PARTITIONS) P
       WHERE  P.TABLESPACE_NAME = T.TABLESPACE_NAME ) AS PART_LIST,
       'ALTER TABLESPACE ' || T.TABLESPACE_NAME || ' OFFLINE;' AS OFFLINE_DDL,
       'SELECT DBMS_METADATA.GET_DDL(''TABLESPACE'', ''' || T.TABLESPACE_NAME || ''') FROM DUAL;' AS GET_DDL,  
       'DROP TABLESPACE ' || T.TABLESPACE_NAME || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;' AS DROP_DDL
FROM   DBA_TABLESPACES T,
     ( SELECT TABLESPACE_NAME, COUNT(*) AS SEG_COUNT
       FROM DBA_SEGMENTS
       GROUP BY TABLESPACE_NAME ) S
WHERE T.TABLESPACE_NAME = S.TABLESPACE_NAME(+)
  AND T.CONTENTS = 'PERMANENT' -- 실제 데이터용만 필터링
  AND NVL(S.SEG_COUNT, 0) = 0  -- 데이터가 없는 것만 출력
  AND T.TABLESPACE_NAME NOT IN ('SYSTEM', 'SYSAUX')
ORDER BY T.TABLESPACE_NAME;

3. 테이블스페이스 및 데이터파일 상태 확인 및 변경(ONLINE -> OFFLINE)

  • 아래 쿼리를 실행하여 테이블스페이스 및 데이터파일의 상태를 확인합니다. 일반적으로 테이블스페이스의 상태가 변경되면, 관련 데이터파일의 상태도 동일하게 변경됩니다. 
    SELECT T.TABLESPACE_NAME, 
       T.STATUS AS TS_STATUS, 
       D.FILE_NAME, 
       D.ONLINE_STATUS AS FILE_STATUS
FROM   DBA_TABLESPACES T,
       DBA_DATA_FILES D
WHERE  T.TABLESPACE_NAME = D.TABLESPACE_NAME
AND    T.STATUS = 'OFFLINE';
  • 1단계에서 생성한 OFFLINE_DDL을 사용해서 테이블스페이스의 상태를 OFFLINE으로 변경합니다. 
    ALTER TABLESPACE 테이블스페이스명 OFFLINE ;

4. 테이블스페이스 사용여부 모니터링

  • Audit Trail을 사용해서 OFFLINE상태의 테이블스페이스 접근 여부를 모니터링합니다. 
    AUDIT SELECT ANY TABLE WHENEVER NOT SUCCESSFUL;
AUDIT EXECUTE PROCEDURE WHENEVER NOT SUCCESSFUL;
  • 감사 로그(DBA_AUDIT_TRAIL)에서 RETURNCODE가 오프라인 관련 에러 코드인 것을 찾습니다.
    ORA-01542: 테이블스페이스가 오프라인 상태임
    ORA-00376: 데이터파일이 읽기 불가능한 상태임 
    SELECT EXTENDED_TIMESTAMP, 
       DB_USER, 
       OS_USER, 
       USERHOST, 
       OBJ_NAME,      -- 접근하려 했던 테이블명
       RETURNCODE,    -- 1542 또는 376 확인
       SQL_TEXT       -- 시도했던 SQL 문 (설정에 따라 보일 수 있음)
FROM   DBA_AUDIT_TRAIL
WHERE  RETURNCODE IN (1542, 376)
ORDER BY EXTENDED_TIMESTAMP DESC;
  • Invalid Object의 접근/사용 이력을 기록하는 Trigger를 이용하셔도 됩니다.

5. 테이블스페이스 삭제

  • 테이블스페이스를 삭제하기 전에 2단계에서 생성한 DBMS_METADATA.GET_DDL()을 실행하여 테이블스페이스 생성 스크립트 백업합니다. 
    SELECT DBMS_METADATA.GET_DDL('TABLESPACE', '테이블스페이스_이름') FROM DUAL;
  • 아래 명령어로 테이블스페이스를 물리적으로 삭제합니다. 
    DROP TABLESPACE 테이블스페이스명 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
    • INCLUDING CONTENTS: 테이블스페이스 안에 테이블, 인덱스 등 데이터가 남아있어도 강제로 삭제합니다. (이 옵션이 없으면 비어있지 않은 테이블스페이스는 삭제되지 않습니다.)
    • AND DATAFILES: 서버 디스크에 저장된 실제 파일(.dbf)을 함께 삭제합니다. 이 옵션을 빼면 DB 엔진에서는 사라지지만 디스크 용량은 그대로 점유하게 됩니다.
    • CASCADE CONSTRAINTS: 삭제하려는 테이블스페이스 내의 테이블을 참조하고 있는 다른 테이블스페이스의 외래 키(FK) 관계까지 끊어주어 에러를 방지합니다.

6. 미사용 데이터파일 존재 여부 확인

AND DATAFILES 옵션을 사용하지 않거나, 비정상적으로 테이블스페이스가 삭제된 경우, 미사용 데이터파일이 존재할 수 있습니다.(Ghost File 현상)

  • 데이터파일 저장 디렉토리 확인 
    SQL> show parameter db_create_file_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /path/to
  • DBA_DATA_FILES 뷰에 등록된 데이터파일과 /path/to//datafile/ 디렉토리에 생성된 파일을 비교합니다. 1번 리스트에는 없는데 2번 리스트에는 있는 파일이 바로 “미사용 데이터파일”입니다. 
    SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;          => 1번 리스트
vs.
$ cd /path/to/<SID>/datafile/; ls -d $PWD/* -1   => 2번 리스트

Tags:

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다