ORA-08103: object no longer exists while revoking DBA privilege from user

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

Image

 

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.

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s