Sunday, November 21, 2010

ORA-00959: tablespace '_$deleted$16$0' does not exist

Found error
ORA-00959: tablespace '_$deleted$16$0' does not exist
During add partitioned table: ALTER TABLE ... ADD PARTITION...UPDATE INDEXES

Then checked Oracle Support and fixed issue.
ORA-00959: Tablespace '_$deleted$11$0' Does Not Exist - During DML/DDL On Partitoned Table [ID 1121059.1]
The default tablespace attribute of the table is set to a system generated name that represents a dropped tablespace. (I don't explain... we can read on Oracle Support, that is useful)

On oracle support show idea with partitioned table, we can check on dba_part_tables view
select owner, table_name,def_tablespace_name from dba_part_tables where def_tablespace_name like '%delete%';
and solve by -
alter table table_name modify default attributes tablespace tablespace_existing;
but my issue, that is about partitioned index:
SQL> select owner, index_name,def_tablespace_name from dba_part_indexes where def_tablespace_name like '%delete%';

OWNER TABLE_NAME DEF_TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
USER TEST_IND01 _$deleted$16$0
USER TEST_IND02 _$deleted$16$0
SQL> alter index USER.TEST_IND01 modify default attributes tablespace TBS_EXISTING ;

Index altered.

SQL> alter index USER.TEST_IND02 modify default attributes tablespace TBS_EXISTING ;

Index altered.
and then add partitioned table again(no error).

Reference:
Oracle Support

1 comment:

Peter McLarty said...

Hi Surachart,
I like that. So you can alter deleted indexes in the recycle bin, just like any other index, thats interesting. I guess this shows that Oracle simply flags the index as deleted so a query wont use it, but truly does nothing more than that and of course the name change when the recycle bin is available. No wonder they are easy to recover from.

Peter