Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: MYDUL avaialbe for recovery, another choise of DUL.

Re: MYDUL avaialbe for recovery, another choise of DUL.

From: Jonathan Lewis <>
Date: Wed, 28 Sep 2005 07:16:05 +0000 (UTC)
Message-ID: <dhdfvl$oqp$>

<> wrote in message
> (remind people of
> the same trick that a database with a dropped column can be recovered
> by altering data dictionary, as long as it's subsequently exported and
> imported; credit to an Oracle expert well known in this newsgroup)

A (not very minor) correction to the 'trick'.

If you want to do this, then you export the entire database BEFORE you hack the dictionary.

THEN dump to file the table data that reappears. (Note - an export at this point may not be safe, as it may carry some definition error if your hacking of the data dictionary missed something). Whether you do an export or another form of extract, you need to check that the data extracted is actually correct, and that (for example) any triggers are associated with the correct columns.

Then you create an entire new database and import the entire export, drop the incorrect version of the table, and reload the table.

You create a backup of the database (or a self-consistent set of tablespaces). Hack the backup, dump the table from the backup. Drop the table from the original and reload the copy. This is probably going to be quicker in most cases - but some people may have a problem finding somewhere to put the copy of the database.

Both strategies, of course, requires you to stop people using the database for the duration (to avoid changes to the critical table), and both require you to have a method for checking that the data from the dumped table is correct, and that all the triggers, indexes, RI etc. are correct after you've rebuilt the table.

Any other strategies leaves you with a data dictionary that may not be self-consistent, and therefore may crash, corrupt, or breach the security of your system at some future date. Either strategy is messy and long-winded - but you don't want a production database which has had the sys tables updated by hand.


Jonathan Lewis
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005
The Co-operative Oracle Users' FAQ
Public Appearances - schedule updated 4th Sept 2005
Received on Wed Sep 28 2005 - 02:16:05 CDT

Original text of this message