Oracle DBA – A lifelong learning experience

ORA-00959: tablespace ‘_$deleted$5$0′ does not exist

Posted by John Hallas on March 25, 2011

Firstly the background.  I had a very large tablespace (3Tb with  only about 20% used) so I re-organised all the objects, which all belonged to one schema for which this was the default tablespace, into a new tablespace, dropped the original which was now empty and renamed the new tablespace to become the old tablespace.

alter tablespace MIS_ODS1 rename to MIS_ODS;

 

Note the filename still contains the new tablespace name +DATA/sid/datafile/mis_ods1.780.746448435 but that can be renamed later if desired

Everything was fine or so I thought and then an overnight job failed with

ORA-00959: tablespace ‘_$deleted$5$0′ does not exist

I checked dba_tablespaces and could see no reference to this tablespace but I knew full well it was to do with the tablespace rename that I had done the previous day.  I checked dba_tables and indexes and nothing belonged in that tablespace and all schema objects were correctly assigned to the MIS_ODS tablespace.

Looking at OEM I noticed that the default tablespace for my user was now _$deleted$5$0 and everything made sense. The rename had caused a problem and I just needed to amend the default tablespace which I did. I was intrigued enough to perform a  test case to see if it was a general issue with rename tablespace as I had not used that command before.

create tablespace johntest datafile '+DATA' size 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
create user johntest identified by HtF6739JKLM default tablespace johntest;
alter user johntest quota unlimited on johntest;
grant create table, create session to johntest;
connect johntest/HtF6739JKLM

create table a (a1 number, a2 varchar2(50));
begin
for i in 1..1000 loop
insert into a values (i, 'testentry');
end loop;
commit;
end;
/

connect / as sysdba

alter tablespace johntest rename to wasjohntest;
select username, default_tablespace from dba_users where username = 'ODSPROD'

create table b (a1 number, a2 varchar2(50));
begin
for i in 1..1000 loop
insert into a values (i, 'testentry');
	end loop;
	commit;
end;
 /

and everything was hunky dory, to quote a David Bowie album title. So I put it down to a ‘funny’ and moved on to something else. So later the failing batch job was re-run with the same failure and I was beginning to get a bit worried. We broke the pl/sql procedure down until we identified some failing code

 alter table ODS_SLS_TRAN_HEADER add partition ODS_SLS_TRAN_HEADER_20110323 values ('20110323') tablespace MIS_ODS_20110323 update indexes;
alter table ODS_SLS_ITEM_DETAIL add partition ODS_SLS_ITEM_DETAIL_20110323 values ('20110323') tablespace MIS_ODS_20110323 update indexes;
alter table ODS_SLS_PAYMENT_DETAIL add partition ODS_SLS_PAYMENT_DTL_20110323 values ('20110323') tablespace MIS_ODS_20110323 update indexes;

I started an online search, firstly Google as that is always my first option but that took me to MoS where I found note 1121059.1 ORA-00959: Tablespace ‘_$deleted$11$0′ Does Not Exist – During DML/DDL On Partitoned Table. The symptons were identical to mine

 

 

  • A new tablespace was created
  • Objects were moved to the new tablespace
  • The original tablespace was dropped
  • The new tablespace was renamed to have the same name as the original tablespace which was dropped
  • DML against a partitioned table is failing with ORA-00959
  • Adding a new partition to the partitioned table fails with ORA-00959
  • The solution steps outlined in Note 604648.1 have been followed but the ORA-00959 persists

I therefore ran their recommended fix which was to alter the table properties to ensure that the correct table was in place and not the ‘intermin’ one.

Firstly a check that I was hitting the problem

select table_name, def_tablespace_name
from user_part_tables
where lower(def_tablespace_name) = '_$deleted$5$0';

 

When 306 rows were returned  I wrote the dynamic sql to fix it and ran the code.

 Select 'alter table '||table_name||' modify default attributes tablespace MIS_ODS;'
from user_part_tables where lower(def_tablespace_name) = '_$deleted$5$0';

and still my create partition code was failing with the same error and I had done everything mentioned in the note.

However it then occurred to me that indexes could still be an issue and that indeed was the case so a quick amendment of the dynamic sql fixed the problem.


This problem is fixed in 11.2 and the MoS note was a big help but don’t forget to look at the index partitions as well.

 Select 'alter index '||index_name||' modify default attributes tablespace MIS_ODS;'
from user_part_indexes where lower(def_tablespace_name) = '_$deleted$5$0';
About these ads

2 Responses to “ORA-00959: tablespace ‘_$deleted$5$0′ does not exist”

  1. procrastination will get you everywhere!

    I have a note pending correct write up and simplification for this exact issue dating back to November 2009 (be interesting to see where the open unpublished bug comes from because we did raise an SR but didn’t get the note you reference). In our case we were moving away from bigfile tablespaces to smallfile tablespaces (because the architect had been under the incorrect impression that bigfile tablespaces were larger than smallfile tablespaces) and had partitions, subpartitions and partition templates etc in place for the object DDL. Creating a nice small non confusing test case was the bit that I hadn’t done.

    • John Hallas said

      Cheer Niall. That was a very simple test case compared to some I have seen, of which I am always in awe.
      It is a bit of an odd bug though and for it to have gone on so long is pretty poor really.

      John

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 190 other followers

%d bloggers like this: