Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

**********************************************************************************


Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Wednesday, October 5, 2011

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

I just did a reorg of one of our tablespace DATA_001 and dropped the tablespace and after the re org when I tried to query one of the objects got the below error




Issues was; These objects were taking 0 blocks and hence were not picked up my re-org script from the dba_segments for re-org
so these were just sitting there without occupying any blocks but still pointing to the old tablespace which we deleted after the
re-org


select table_name,blocks from dba_tables where tablespace_name='DATA_001' and blocks='0'

this will give us the list of the objects that has 0 blocks but indeed belong to the above tablespace which we dropped.


select * from USER.USER_EMPLOYEE_ERROR move
*
ERROR at line 1:
ORA-00959: tablespace '_$deleted$25$0' does not exist

so tried move this table to new tablespace

SQL> alter table USER.USER_EMPLOYEE_ERROR move tablespace DATA_001;
alter table USER.USER_EMPLOYEE_ERROR move tablespace DATA_001
*
ERROR at line 1:
ORA-00959: tablespace '_$deleted$25$0' does not exist


how about atleast move..

SQL> alter table USER.USER_EMPLOYEE_ERROR move;
alter table USER.USER_EMPLOYEE_ERROR move
*
ERROR at line 1:
ORA-00959: tablespace '_$deleted$25$0' does not exist


SQL> select tablespace_name from dba_tablespaces where tablespace_name like '%deleted%';

no rows selected


where as I can still see the objects in the database. So decided to create the ddl scripts via TOAD
and then dropped these objects and recreated them.



drop table USER.USER_EMPLOYEE_ERROR purge; <-- without purge we cannot drop the table
create table USER.USER_EMPLOYEE_ERROR (col a,b,c..);

1 comment:

Anonymous said...

Great tip! Solved my problem!

Tommy Yuen