while revoking “DBA” role from an Oracle users, i faced the following error:

SQL> revoke DBA from oracle_user;

ORA-08103: object no longer exists

 

you have 2 methods to resolve this issue:

METHOD I :

• create a new tablespace with new “data files”.
• take a backup of the sql definition of the schema user.
• use the data pump utility (expdp) to export the schema’s data.
• drop the user schema.
• drop the tablespace with the datafiles using the sql command:
• DROP TABLESPACE TS_EMAPLOYEE_DATA INCLUDING CONTENTS AND DATAFILES;
• re-create the tablespace with “NEW” data file(s) and and create the user schema again.
• import the schema’s data again.

 

METHOD II:

• create a new tablespace with new data files.
• alter user and grant the schema user quota on the new tablespace:
• alter user emaployee quota unlimited on tablespace ts_employee_data_02;
• use the “ALTER MOVE” command to move oracle tables and indexes from tablepsace 1 to tablespace 2 (NEW)
• you may require rebuilding indexes.

 

Important Remark:

This is faced with legacy databases that have been upgraded over the years from 9i……to 11g due data files header issue.