ORA-08103: Object no longer exists

One of the table is giving us error during running the Select query. Seems like any data is corrupted in it.

Error

--Running on SQLPLUS
SELECT * FROM SCOTT.EMPLOYEES
where DEPARTMENT_ID='000772'
order by 3;
ORA-08103: object no longer exists

–On analysing the index of table getting following error
SQL> analyze index “SCOTT”.”DESCRIPTION” validate structure;
analyze index “SCOTT”.”DESCRIPTION” validate structure
*
ERROR at line 1:
ORA-08100: index is not valid – see trace file for diagnostics

–Tried to export the same table getting following error
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists

Solution
We are going to create a new table based on this old table, it make loss some data the loss data can be checked in bad_rows table, it will give you detail of loss data or no of rows lost.

Steps for creating new table based on old table

1. Create similar structure of old table as New table with following query

CREATE TABLE new_table_name
AS SELECT * FROM original_table_name WHERE 1=2;

— Example
CREATE TABLE scott.employees_new
as SELECT * FROM scott.employees WHERE 1=2;

2. Create a bad row table which give you number of rows skipped in this process.

create table bad_rows (row_id rowid, oracle_error_code number);


3. Now execute the following script for taking backup and skipping errors row by executing rows one by one in cursor.
Note:
1. Replace the ORIGINAL_TABLE_NAME and NEW_TABLE_NAME
2. Check index on ORIGINAL_TABLE_NAME and check the primary index or unique index not contain null VALUE.
3. Index name should be primary or unique key.

set serveroutput on

DECLARE
  TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

  CURSOR c1 IS select /*+ index_ffs(tab1 ) parallel(tab1) */ rowid
  from  tab1
  where  is NOT NULL
  order by rowid;

  r RowIDTab;
  rows NATURAL := 20000;
  bad_rows number := 0 ;
  errors number;
  error_code number;
  myrowid rowid;
BEGIN
  OPEN c1;
  LOOP
   FETCH c1 BULK COLLECT INTO r LIMIT rows;
   EXIT WHEN r.count=0;
   BEGIN
    FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
     insert into 
     select /*+ ROWID(A) */ 
     from  A where rowid = r(i);
   EXCEPTION
   when OTHERS then
    BEGIN
     errors := SQL%BULK_EXCEPTIONS.COUNT;
     FOR err1 IN 1..errors LOOP
      error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
      if error_code in (1410, 8103, 1578) then
       myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
       bad_rows := bad_rows + 1;
       insert into bad_rows values(myrowid, error_code);
      else
       raise;
      end if;
     END LOOP;
    END;
   END;
   commit;
  END LOOP;
  commit;
  CLOSE c1;
  dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/

4. Match the number of rows of both old and new table.

SELECT count(*) FROM original_table_name;

SELECT count(*) FROM new_Table_name;

5. Check the DDL of index created on the Original Table name.

SELECT TABLESPACE_NAME,INDEX_NAME, UNIQUENESS FROM DBA_INDEXES WHERE TABLE_NAME = 'original_table_name';

--Get DDL of indexes:
SET HEADING OFF;
SET ECHO OFF;
SET PAGES 999;
SET LONG 90000;
SELECT DBMS_METADATA.GET_DDL('INDEX','index_name','schema_name') FROM DUAL;

6. Rename the tables.

--from Original to backup table.
ALTER TABLE original_table_name RENAME TO original_table_name_backup;

--From new to Original table
ALTER TABLE new_Table_name RENAME TO original_table_name;

7. Create the index present on Original table by changing name to new table.