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.
- 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.
This is faced with legacy databases that have been upgraded over the years from 9i……to 11g due data files header issue.