ORA-14404: partitioned table contains partitions in a different tablespace

OS환경 : Oracle Linux4.8 (64bit)

DB 환경 : Oracle Database 9.2.0.4

에러 : ORA-14404: partitioned table contains partitions in a different tablespace

파티션 테이블이 있는 테이블 스페이스 삭제시 발생한 메세지

해결 방법 : 해당 테이블 스페이스 안에있는 파티션 테이블들을 모두 삭제해준뒤 테이블 스페이스 삭제

테스트 시나리오

1. 테이블 스페이스 조회

select * from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME               BYTES     BLOCKS STATUS      RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------
/oradata/ORCL/system01.dbf
     1 SYSTEM               629145600      76800 AVAILABLE         1 YES 3.4360E+10     4194302     1280  629080064       76792
 
/oradata/ORCL/undotbs01.dbf
     2 UNDOTBS1              2726297600     332800 AVAILABLE         2 YES 3.4360E+10     4194302      640 2726232064      332792
 
/oradata/ORCL/users01.dbf
     6 USERS                26214400       3200 AVAILABLE         6 YES 3.4360E+10     4194302      1626148864    3192
 
/oradata/ORCL/xdb01.dbf
     7 XDB                    47185920       5760 AVAILABLE         7 YES 3.4360E+10     4194302       847120384    5752
 
/oradata/ORCL/t1.dbf
    10 T1                   314572800      38400 AVAILABLE        10 NO        0           0        0  314507264       38392
 
/oradata/ORCL/t2.dbf
    11 T2                   314572800      38400 AVAILABLE        11 NO        0           0        0  314507264       38392
 
/oradata/ORCL/t3.dbf
    12 T3                   314572800      38400 AVAILABLE        12 NO        0           0        0  314507264       38392
 
/oradata/ORCL/t4.dbf
    13 T4                   314572800      38400 AVAILABLE        13 NO        0           0        0  314507264       38392
 
/oradata/ORCL/t5.dbf
    14 T5                   314572800      38400 AVAILABLE        14 NO        0           0        0  314507264       38392

2. 테이블 스페이스 삭제(T1~T5)

drop tablespace T5 including contents and datafiles;
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

바로 삭제가 되지 않음

3. 해당 테이블스페이스 안에있는 테이블 검색

col SEGMENT_NAME for a10
col PARTITION_NAME for a12
col OWNER for a10
select SEGMENT_NAME,PARTITION_NAME,OWNER from dba_segments where TABLESPACE_NAME IN ('T1','T2','T3','T4','T5');
 
SEGMENT_NA PARTITION_NA OWNER
---------- ------------ ----------
COM_OFF    COLUMN_P1    SYS
COM_OFF    COLUMN_P2    SYS
COM_OFF    COLUMN_P3    SYS
COM_OFF    COLUMN_P4    SYS
COM_OFF    COLUMN_P5    SYS
COM_ON       COLUMN_P1    SYS
COM_ON       COLUMN_P2    SYS
COM_ON       COLUMN_P3    SYS
COM_ON       COLUMN_P4    SYS
COM_ON       COLUMN_P5    SYS
COM_OLTP   COLUMN_P1    SYS
COM_OLTP   COLUMN_P2    SYS
COM_OLTP   COLUMN_P3    SYS
COM_OLTP   COLUMN_P4    SYS
COM_OLTP   COLUMN_P5    SYS

4. sys 계정으로 접속

conn / as sysdba

5. 해당 테이블 파티션 제거 및 테이블 제거

alter table COM_OFF drop partition COLUMN_P1;
alter table COM_OFF drop partition COLUMN_P2;
alter table COM_OFF drop partition COLUMN_P3;
alter table COM_OFF drop partition COLUMN_P4;
alter table COM_OFF drop partition COLUMN_P5;
drop table COM_OFF;
 
alter table COM_ON drop partition COLUMN_P1;
alter table COM_ON drop partition COLUMN_P2;
alter table COM_ON drop partition COLUMN_P3;
alter table COM_ON drop partition COLUMN_P4;
alter table COM_ON drop partition COLUMN_P5;
drop table COM_ON;
 
alter table COM_OLTP drop partition COLUMN_P1;
alter table COM_OLTP drop partition COLUMN_P2;
alter table COM_OLTP drop partition COLUMN_P3;
alter table COM_OLTP drop partition COLUMN_P4;
alter table COM_OLTP drop partition COLUMN_P5;
drop table COM_OLTP;

6. 에러 발생했던 테이블 스페이스 삭제

drop tablespace T1 including contents and datafiles;
drop tablespace T2 including contents and datafiles;
drop tablespace T3 including contents and datafiles;
drop tablespace T4 including contents and datafiles;
drop tablespace T5 including contents and datafiles;
 
Tablespace dropped.

7. 테이블 스페이스 재조회

select * from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME               BYTES     BLOCKS STATUS      RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------
/oradata/ORCL/system01.dbf
     1 SYSTEM               629145600      76800 AVAILABLE         1 YES 3.4360E+10     4194302     1280  629080064       76792
 
/oradata/ORCL/undotbs01.dbf
     2 UNDOTBS1              2726297600     332800 AVAILABLE         2 YES 3.4360E+10     4194302      640 2726232064      332792
 
/oradata/ORCL/users01.dbf
     6 USERS                26214400       3200 AVAILABLE         6 YES 3.4360E+10     4194302      1626148864    3192
 
/oradata/ORCL/xdb01.dbf
     7 XDB                    47185920       5760 AVAILABLE         7 YES 3.4360E+10     4194302       847120384    5752

제대로 지워진것을 확인할 수 있다.

원인 : 테이블 스페이스가 비어있지 않아서 발생한 메세지

참조 : http://www.ucjmh.com/2015/01/23/ora/