Re-create data dictionary without disturbing database
When the data dictionary is currupted, how do I re-create it without disturbing the database?
In short, you cannot. Some data dictionary corruptions leave the database in an unusable state. The best recourse here is to restore from a backup before the corruption set in. Other data dictionary corruptions will stop certain operations from taking place. Again, restore from a backup to restore the data dictionary to a non-corrupt state.
In some cases, you can rerun catalog.sql and catproc.sql (as SYS) to re-create some data dictionary objects like views, packages and procedures. This can be run at any time. But this will not fix any corruptions with the data dictionary tables.
This was first published in June 2007