[Oracle] 테이블스페이스 Drop 전 체크 쿼리

보관주기가 있는 테이블, 테이블스페이스의 경우.

보관주기가 도래하면 테이블스페이스 Drop을 통해 Datafile을  반납해줘야 효율적으로 스토리지를 관리할 수 있다.

 

1. 테이블스페이스 Drop 전 체크 쿼리

   TABLE, TAB_PARTITION,TAB_SUBPARTITION, INDEX, IND_PARTITION, IND_SUBPARTITION, TABLE DEFAULT TABLESPACE, INDEX DEFAUL TABLESPACE, LOB, LOB PARTITION, USER, TEMPORARY를 확인하는 쿼리

WITH T0 AS (
   SELECT '테이블스페이스이름' AS TBS_NM FROM DUAL
)
SELECT *
FROM (
  SELECT 'TABLES' AS TP, OWNER, TABLE_NAME, '', TABLESPACE_NAME 
  FROM DBA_TABLES
           ,T0
  WHERE TABLESPACE_NAME = T0.TBS_NM
  UNION ALL
  SELECT 'TAB_PARTITIONS' AS TP, TABLE_OWNER, TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME 
  FROM DBA_TAB_PARTITIONS
           ,T0
  WHERE TABLESPACE_NAME = T0.TBS_NM
  UNION ALL
  SELECT 'TAB_SUBPARTITIONS' AS TP, TABLE_OWNER, TABLE_NAME, SUBPARTITION_NAME, TABLESPACE_NAME 
  FROM DBA_TAB_SUBPARTITIONS
           ,T0
  WHERE TABLESPACE_NAME = T0.TBS_NM
  UNION ALL
  SELECT 'INDEXES' AS TP, OWNER, INDEX_NAME, '', TABLESPACE_NAME 
  FROM DBA_INDEXES
           ,T0
  WHERE TABLESPACE_NAME = T0.TBS_NM
  UNION ALL
  SELECT 'IND_PARTITIONS' AS TP, INDEX_OWNER, INDEX_NAME, PARTITION_NAME, TABLESPACE_NAME 
  FROM DBA_IND_PARTITIONS
           ,T0
  WHERE TABLESPACE_NAME = T0.TBS_NM
  UNION ALL
  SELECT 'IND_SUBPARTITIONS' AS TP, INDEX_OWNER, INDEX_NAME, SUBPARTITION_NAME, TABLESPACE_NAME 
  FROM DBA_IND_SUBPARTITIONS
           ,T0
  WHERE TABLESPACE_NAME = T0.TBS_NM
  UNION ALL
  SELECT 'TAB_DEF_TBS' AS TP, OWNER, TABLE_NAME, '', DEF_TABLESPACE_NAME 
  FROM DBA_PART_TABLES
           ,T0
  WHERE DEF_TABLESPACE_NAME = T0.TBS_NM
  UNION ALL
  SELECT 'IND_DEF_TBS' AS TP, OWNER, INDEX_NAME, '', DEF_TABLESPACE_NAME 
  FROM DBA_PART_INDEXES
           ,T0
  WHERE DEF_TABLESPACE_NAME = T0.TBS_NM
  UNION ALL
  SELECT 'LOB' AS TP, OWNER, TABLE_NAME, COLUMN_NAME||'-'||PARTITIONED ,TABLESPACE_NAME 
  FROM DBA_LOBS
           ,T0
  WHERE TABLESPACE_NAME = T0.TBS_NM
  UNION ALL
  SELECT 'LOB_PARTITIONS' AS TP, TABLE_OWNER, TABLE_NAME, LOB_NAME AS PARTITION_NAME, TABLESPACE_NAME 
  FROM DBA_LOB_PARTITIONS
           ,T0
  WHERE DEFAULT_TABLESPACE_NAME = T0.TBS_NM
  UNION ALL
  SELECT 'USERS' AS TP, USERNAME, '', '', DEFAULT_TABLESPACE_NAME 
  FROM DBA_USERS
           ,T0
  WHERE DEFAULT_TABLESPACE_NAME = T0.TBS_NM
  UNION ALL
  SELECT 'TEMPORARY' AS TP, OWNER, SEGMENT_NAME, PARTITION_NAME, TABLESPACE_NAME 
  FROM DBA_SEGMENTS
           ,T0
  WHERE SEGMENT_TYPE = 'TEMPORARY'
            AND TABLESPACE_NAME = T0.TBS_NM
)
ORDER BY TABLESPACE_NAME, TP

 

 

2. Drop Tablespace Syntax

   테이블스페이스 Drop 문법

-- 1. 기본 Drop 명령어
--    : Object가 남아있는 경우 error가 발생한다.
DROP TABLESPACE 테이블스페이스이름;

-- 2. Constraints와 안에 있는 Ojbect 모두 지우기
--    : Object가 남아있는 경우에도 Drop하지만, 반드시 어떤 Object인지 확인하는게 좋다.
DROP TABLESPACE 테이블스페이스이름 INCLUDING CONTENTS CASCADE CONSTRAINTS; 

-- 3. Object 모두 지우고 OS에 있는 관련 파일까지 삭제
--    : AND DATAFILES의 경우, 일반적인 경우 안 써도 DATAFILE이 삭제된다.
--       This clause is not needed for Oracle-managed files,
--       because they are removed from the system even if you do not specify AND DATAFILES.
DROP TABLESPACE 테이블스페이스이름 INCLUDING CONTENTS AND DATAFILES;

참고 : 

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9004.htm

DROP TABLESPACE