ORA-00959: TABLESPACE '_$DELETED$11$0' DOES NOT EXIST

######################################################
ORA-00959: TABLESPACE '_$DELETED$11$0' DOES NOT EXIST
#######################################################
we got the above error after cutdown process for few tables, as a workaround for this error we need to identify impacted partition tables/indexes:



Here is the query to identify impacted partition tables/indexes:
-------------------------------------------------------------------------
SET lines 180
COL segment_name format a55
SELECT DISTINCT table_name segment_name, def_tablespace_name
           FROM dba_part_tables
          WHERE def_tablespace_name LIKE '%deleted%'
UNION
SELECT DISTINCT index_name segment_name, def_tablespace_name
           FROM dba_part_indexes
          WHERE def_tablespace_name LIKE '%deleted%'
       ORDER BY 1
/

Query to generate the fix commands
-------------------------------------------------------------------------
SELECT DISTINCT    'ALTER TABLE '
                || owner
                || '.'
                || b.table_name
                || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE '
                || tablespace_name
                || ';'
           FROM dba_part_tables b, dba_tab_partitions d
          WHERE d.table_name = b.table_name
            AND def_tablespace_name LIKE '%deleted%'
UNION
SELECT DISTINCT    'ALTER INDEX '
                || owner
                || '.'
                || b.index_name
                || ' MODIFY DEFAULT ATTRIBUTES TABLESPACE '
                || tablespace_name
                || ';'
           FROM dba_part_indexes b, dba_ind_partitions d
          WHERE d.index_name = b.index_name
            AND def_tablespace_name LIKE '%deleted%'
/

18:28:23 [SYS][ORQ21]>> select owner, table_name, def_tablespace_name from dba_part_tables where lower(def_tablespace_name) like '%deleted%';

OWNER                          TABLE_NAME                  DEF_TABLESPACE_NAME
------------------------------ --------------------------  ------------------------------
MGIXX                          MGIHZ_EXTR_RQST_CNTL_PARMS  _$deleted$393$0
MGIXX                          MGIAR_TRANS                 _$deleted$393$0 ? this is the table they accessed and hit with “ORA-959”
MDSYS                          OLS_DIR_BUSINESSES          _$deleted$393$0


18:36:15 [SYS][ORQ21]>> alter table MDSYS.OLS_DIR_BUSINESSES modify default attributes tablespace SYSTEM;

18:36:59 [SYS][ORQ21]>> alter table MGIXX.MGIAR_TRANS modify default attributes tablespace MGIXX_DATA;

18:37:23 [SYS][ORQ21]>> alter table MGIXX.MGIHZ_EXTR_RQST_CNTL_PARMS modify default attributes tablespace MGIXX_DATA;

18:37:44 [SYS][ORQ21]>> select owner, table_name, def_tablespace_name from dba_part_tables where lower(def_tablespace_name) like '%deleted%';