ORA-00959: Tablespace '_$deleted$11$0' Does Not Exist - During DML/DDL On Partitioned Table (Doc ID 1121059.1)

Last updated on MARCH 26, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 10.2.0.5 [Release 10.1 to 10.2]
Information in this document applies to any platform.
***Checked for relevance on 18-Nov-2011***


Symptoms

Consider the example below that demonstrates the behavior.

SQL> connect / as sysdba;
Connected.

SQL> drop user test cascade;

User dropped.

SQL> drop tablespace oldts including contents and datafiles;

Tablespace dropped.

SQL> create tablespace oldts datafile 'oldts.f' size 10m reuse;

Tablespace created.

SQL> create user test identified by test default tablespace oldts;

User created.

SQL> grant dba to test;

Grant succeeded.

SQL> connect test/test;
Connected.
SQL> CREATE TABLE SALES
2 (
3 C1 NUMBER
4 )
5 PARTITION BY RANGE (C1)
6 (
7 PARTITION P1 VALUES LESS THAN (5) NOCOMPRESS
8 , PARTITION P2 VALUES LESS THAN (10) NOCOMPRESS
9 );

Table created.

SQL> col owner format a10
SQL> select owner, table_name,def_tablespace_name
2 from dba_part_tables
3 where owner = 'TEST' and table_name = 'SALES';

OWNER      TABLE_NAME                     DEF_TABLESPACE_NAME
---------- ------------------------------ ------------------------------
TEST       SALES                           OLDTS

SQL> create tablespace newts datafile 'newts.f' size 10M reuse;

Tablespace created.

SQL> alter table test.sales move partition P1 tablespace newts;

Table altered.

SQL> alter table test.sales move partition P2 tablespace newts;

Table altered.

SQL> drop tablespace oldts including contents and datafiles;

Tablespace dropped.
 
SQL> ALTER TABLESPACE newts RENAME TO oldts;

Tablespace altered.

SQL> select username, default_tablespace, temporary_tablespace from sys.dba_users where username like 'TEST';

USERNAME   DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
---------- ------------------------------ ------------------------------
TEST       OLDTS                          TEMP

SQL> col username format a10
SQL> select username, default_tablespace, temporary_tablespace
2 from dba_users where username ='TEST';

USERNAME   DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
---------- ------------------------------ ------------------------------
TEST       OLDTS                          TEMP

SQL> select owner, table_name,def_tablespace_name
2 from dba_part_tables
3 where owner = 'TEST' and table_name = 'SALES';

OWNER      TABLE_NAME                     DEF_TABLESPACE_NAME
---------- ------------------------------ ------------------------------
TEST       SALES                          _$deleted$11$0

SQL> alter table sales add partition P3 values less than(25) nocompress;
alter table sales add partition P3 values less than(25) nocompress
*
ERROR at line 1:
ORA-00959: tablespace '_$deleted$11$0' does not exist


SQL> alter table sales modify default attributes tablespace oldts;

Table altered.

SQL> alter table sales add partition P3 values less than(25) nocompress;

Table altered.

SQL> spool off

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms