Wednesday, April 27, 2011

ORA-14404 / 14405 and Deferred Segment Creation

SQL>> select count(*) from dba_segments where tablespace_name in ('TS_TO_DROP', 'TS_TO_NOT_DROP');

COUNT(*)---------- 0

SQL>> drop tablespace ts_to_drop including contents and datafiles cascade constraints;drop tablespace ts_to_drop including contents and datafiles cascade constraints*ERROR at line 1:ORA-14404: partitioned table contains partitions in a different tablespace

How is it possible to get a message about an object that is supposed to have partitions in the tablespace to be dropped and in some other tablespaces if there is no segment contained in the tablespace to be dropped?

May be a dictionary corruption, or a problem with the recyclebin?

Possibly yes, but in this case the answer is simple and can be found in the title: It is a feature, not a bug, because you are running (at least) 11.2.0.2 Enterprise Edition and make use of deferred segment creation, which is enabled by default.

The 11.2.0.2 patch set extended the deferred segment creation to partitioned objects, which means that it adds just another twist to the potential "problems" caused by deferred segment creation.

In this case, although no segment actually exists in the tablespace, a partition is supposed to be created in the tablespace to be dropped once you add data to it or materialize the segment explicitly. Furthermore the object has other partitions that are assigned to different tablespaces (and again potentially have not been materialized yet).

Obviously Oracle treats this the same as if the segment(s) existed and therefore throws this error message although the tablespace is effectively empty.

The same applies to partitioned indexes, by the way, the only difference is the error message raised (ORA-14405).

SQL>SQL> drop tablespace ts_to_drop including contents and datafiles;drop tablespace ts_to_drop including contents and datafiles*ERROR at line 1:ORA-00959: tablespace 'TS_TO_DROP' does not exist

SQL>SQL> drop tablespace ts_to_not_drop including contents and datafiles;drop tablespace ts_to_not_drop including contents and datafiles*ERROR at line 1:ORA-00959: tablespace 'TS_TO_NOT_DROP' does not exist

The script also shows that the default tablespace that can be assigned to (sub-) partitions is not relevant in this case. It also shows that you can now move and rebuild segments without materializing them - I think this is also a feature that has been added in 11.2.0.2.