recovering lost tables from oracle9i database

anuj...@yahoo.co.in

unread,

Apr 3, 2008, 8:32:56 AM4/3/08

to

in my oracle 9i database,around 150 tables were created and used
frequently. but one fine day,only around 20 tables are seen and other
tables are missing. How can I recover all the other tables. The tables
are all created by scott/tiger. pls help me out asp.

gazzag

unread,

Apr 3, 2008, 8:56:20 AM4/3/08

to

Depending on your setup, Flashback Query may be of some benefit:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#20178

Of course, at the risk of sounding like I'm shutting the gate after
the horse has bolted, your main concern is how this happened and how
you are going to prevent it happening again. You may want ot examine
your backup solution, too ;)

HTH

-g

Shakespeare

unread,

Apr 3, 2008, 8:56:28 AM4/3/08

to

<anuj...@yahoo.co.in> schreef in bericht
news:626b1d15-4e44-47f3...@u36g2000prf.googlegroups.com...

Since this does not sound like a real production system (scott/tiger!!!) I
hope you have a backup!

Shakespeare

unread,

Apr 3, 2008, 9:13:12 AM4/3/08

to

"gazzag" <gar...@jamms.org> schreef in bericht
news:0e7f83fa-a005-4adf...@q27g2000prf.googlegroups.com...

a.. Flashback query does not work through DDL operations that modify
columns, or drop or truncate tables.

I fear the worst....

Shakespeare

gazzag

unread,

Apr 3, 2008, 9:22:54 AM4/3/08

to

On 3 Apr, 14:13, "Shakespeare" <what...@xs4all.nl> wrote:
> a.. Flashback query does not work through DDL operations that modify
> columns, or drop or truncate tables.
>
> I fear the worst....
>

> Shakespeare- Hide quoted text -
>

You are, of course, correct. I hope he's got a backup :-)

-g

Ed Prochak

unread,

Apr 3, 2008, 9:26:11 AM4/3/08

to

On Apr 3, 8:56 am, "Shakespeare" <what...@xs4all.nl> wrote:
> <anujit...@yahoo.co.in> schreef in berichtnews:626b1d15-4e44-47f3...@u36g2000prf.googlegroups.com...

Yes restoring the backup is the solution.

It is good practice as well to do a restore regularly to make sure you
know how to do it. (so when you need to do it in production, you are
confident of your restore process when the pressure is on!)
Ed

Mark D Powell

unread,

Apr 3, 2008, 11:59:37 AM4/3/08

to

On Apr 3, 8:56 am, "Shakespeare" <what...@xs4all.nl> wrote:

> <anujit...@yahoo.co.in> schreef in berichtnews:626b1d15-4e44-47f3...@u36g2000prf.googlegroups.com...

anujit, Shakespeare brings up the first thought I had which is why is
the scott/tiger username and password being used. This is a training
Id for which the password should always be changed in test and the ID
should be dropped or at least locked out of production.

On a production system you should have rman or at least hot backs that
could be used to restore the database to a duplicate then advanced to
a point just before the objects were dropped.

Do you take exports? For test this should be good enough.

This is all providing you actually determined that you really need to
restore these objects.

HTH -- Mark D Powell --