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