ORA-14404 / 14405 and Deferred Segment Creation

Have a look at the following SQL*Plus output snippet:

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).

For a complete walkthrough, see this demo script:

set echo on linesize 200

drop tablespace ts_to_drop including contents and datafiles;

drop tablespace ts_to_not_drop including contents and datafiles;

column file_path new_value file_path

select
substr(
file_name
, 1
, instr(
file_name
, case when dbms_utility.port_string like '%WIN%' then '\' else '/' end
, -1)
) as file_path
from
dba_data_files
where
tablespace_name = (select tablespace_name from dba_tablespaces where contents = 'PERMANENT' and tablespace_name not in ('SYSTEM', 'SYSAUX') and block_size = (select value from v$parameter where name = 'db_block_size') and rownum <= 1)
and rownum <= 1;

create tablespace ts_to_drop datafile '&file_path.ts_to_drop.dbf' size 10M reuse autoextend off;

create tablespace ts_to_not_drop datafile '&file_path.ts_to_not_drop.dbf' size 10M reuse autoextend off;

drop table t;

purge table t;

create table t
(id number)
segment creation deferred
partition by list (id)
(
partition p_1 values (1) tablespace ts_to_not_drop,
partition p_2 values (2)
)
tablespace ts_to_drop
;

create index t_idx on t (id) local;

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

drop tablespace ts_to_drop including contents and datafiles cascade constraints;

select def_tablespace_name from user_part_tables where table_name = 'T';

select partition_name, tablespace_name from user_tab_partitions where table_name = 'T';

select def_tablespace_name from user_part_indexes where table_name = 'T';

select partition_name, tablespace_name from user_ind_partitions where index_name = 'T_IDX';

alter table t move partition p_2 tablespace ts_to_not_drop segment creation deferred;

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

drop tablespace ts_to_drop including contents and datafiles cascade constraints;

alter index t_idx rebuild partition p_2 tablespace ts_to_not_drop;

drop tablespace ts_to_drop including contents and datafiles cascade constraints;

alter table t add partition p_3 values (3);

drop tablespace ts_to_not_drop including contents and datafiles cascade constraints;

Which gives me this output in 11.2.0.2:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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


SQL>
SQL> column file_path new_value file_path
SQL>
SQL> select
2 substr(
3 file_name
4 , 1
5 , instr(
6 file_name
7 , case when dbms_utility.port_string like '%WIN%' then '\' else '/' end
8 , -1)
9 ) as file_path
10 from
11 dba_data_files
12 where
13 tablespace_name = (select tablespace_name from dba_tablespaces where contents = 'PERMANENT' and tablespace_name not in ('SYSTEM', 'SYSAUX') and block_size = (select value from v$parameter where name = 'db_block_size') and rownum <= 1)
14 and rownum <= 1;

FILE_PATH
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
C:\APP\ORACLE\ORADATA\ORCL112\

SQL>
SQL> create tablespace ts_to_drop datafile '&file_path.ts_to_drop.dbf' size 10M reuse autoextend off;
old 1: create tablespace ts_to_drop datafile '&file_path.ts_to_drop.dbf' size 10M reuse autoextend off
new 1: create tablespace ts_to_drop datafile 'C:\APP\ORACLE\ORADATA\ORCL112\ts_to_drop.dbf' size 10M reuse autoextend off

Tablespace created.

SQL>
SQL> create tablespace ts_to_not_drop datafile '&file_path.ts_to_not_drop.dbf' size 10M reuse autoextend off;
old 1: create tablespace ts_to_not_drop datafile '&file_path.ts_to_not_drop.dbf' size 10M reuse autoextend off
new 1: create tablespace ts_to_not_drop datafile 'C:\APP\ORACLE\ORADATA\ORCL112\ts_to_not_drop.dbf' size 10M reuse autoextend off

Tablespace created.

SQL>
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> purge table t;
purge table t
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN


SQL>
SQL> create table t
2 (id number)
3 segment creation deferred
4 partition by list (id)
5 (
6 partition p_1 values (1) tablespace ts_to_not_drop,
7 partition p_2 values (2)
8 )
9 tablespace ts_to_drop
10 ;

Table created.

SQL>
SQL> create index t_idx on t (id) local;

Index created.

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

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

SQL>
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


SQL>
SQL> select def_tablespace_name from user_part_tables where table_name = 'T';

DEF_TABLESPACE_NAME
------------------------------
TS_TO_DROP

SQL>
SQL> select partition_name, tablespace_name from user_tab_partitions where table_name = 'T';

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_1 TS_TO_NOT_DROP
P_2 TS_TO_DROP

SQL>
SQL> select def_tablespace_name from user_part_indexes where table_name = 'T';

DEF_TABLESPACE_NAME
------------------------------


SQL>
SQL> select partition_name, tablespace_name from user_ind_partitions where index_name = 'T_IDX';

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_1 TS_TO_NOT_DROP
P_2 TS_TO_DROP

SQL>
SQL> alter table t move partition p_2 tablespace ts_to_not_drop segment creation deferred;

Table altered.

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

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

SQL>
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-14405: partitioned index contains partitions in a different tablespace


SQL>
SQL> alter index t_idx rebuild partition p_2 tablespace ts_to_not_drop;

Index altered.

SQL>
SQL> drop tablespace ts_to_drop including contents and datafiles cascade constraints;

Tablespace dropped.

SQL>
SQL> alter table t add partition p_3 values (3);
alter table t add partition p_3 values (3)
*
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 cascade constraints;

Tablespace dropped.

SQL>

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.