Monday, December 28, 2015

[Exadata] About "ORA-14404: partitioned table contains partitions in a different tablespace" error

Hi,

In this post, i will talk about ORA-14404 error i got recently when i try to drop an old tablespace. It was very annoying and  time consuming because i knew all partitions for all tables reside in same tablespace :)


The problem arised from an attempt to drop a read only tablespace as follows.

SQL> DROP TABLESPACE my_ts INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE my_ts INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

The error was interesting because i knew that all the tables reside in same tablespace. I also double-checked the tablespace from Oracle Data Dictionary and Oracle SQL Developer also :)

But it didn't help. So i tried again after taking tablespace read-write.

SQL> ALTER TABLESPACE my_ts READ WRITE;
SQL>  DROP TABLESPACE my_ts INCLUDING CONTENTS AND DATAFILES;
 DROP TABLESPACE my_ts INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

Same annoying error, i dropped manually all the tables in tablespace and tried again...

SAME error again, so let me check it on support :)

I found that note: ORA-14404 or ORA-14407 When Trying to Drop a Tablespace (Doc ID 1674989.1) 

In the note, it says if deferred segment creation is enabled, the mislocated partitions can not be seen on dba_segments view. That addresses my problem.

SQL> show parameter deferred
deferred_segment_creation            boolean  TRUE

I run the following query in the note and find out some partitions resides in different tablespaces actually.

SQL>select table_owner, table_name, partition_name,tablespace_name from dba_tab_partitions X where x.tablespace_name='MY_TS' and exists (select * from dba_tab_partitions Y
where x.table_owner=y.table_owner and x.table_name=y.table_name and y.tablespace_name<>'MY_TS'
...
t_owner    table_1     p42    another_ts

It produced me some table names , so i got surprised and dropped those tables manually.

After that, i dropped tablespace successfully.

SQL> DROP TABLESPACE my_ts  INCLUDING CONTENTS AND DATAFILES

Tablespace dropped.

Ok, that's all.
Enjoy & Share.

Source:



No comments :

Post a Comment