it possible to recover drop table in oracle 9i

rajnikan...@gmail.com

unread,

May 28, 2008, 9:48:40 AM5/28/08

to

Dear All

I have accidently drop a table, Is it possible to recover this table
in oracle 9i ?
I have no backup
and My database is running in archive log mode.

Can i recover a table from log file ?

Thank you

Raj

Vladimir M. Zakharychev

unread,

May 28, 2008, 10:09:38 AM5/28/08

to

On May 28, 5:48 pm, "rajnikant.par...@gmail.com"

Since 9i doesn't have flashback functionality, your only option is to
perform incomplete recovery (point-in-time recovery) to the time just
before the drop, export the table and then restore the database (for
example, from a cold backup taken just before the incomplete
recovery,) and import the table. This obviously requires a full backup
taken before the drop which you don't seem to have, so the answer is
"regrettably no."

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

DA Morgan

unread,

May 28, 2008, 12:24:57 PM5/28/08

to

A very minor clarification. As of 9.0.1 Oracle included the
DBMS_FLASHBACK built-in package. But it won't address a dropped
table only deleted rows.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

joel garry

unread,

May 28, 2008, 12:49:56 PM5/28/08

to

On May 28, 6:48 am, "rajnikant.par...@gmail.com"

Just kind of thinking out loud here. You may be able to recover all
insert statements using logminer on all archived logs since the table
was created.

Whether that is insane depends on things like how long the table has
been around and how many inserts there have been... but even some
people with backups have had to apply months worth of arcs when they
screw up.

Having a backup strategy that allows a large spread of options is
really worthwhile.

jg
--
@home.com is bogus.
THEM! http://www.itbusiness.ca/it/client/en/CDN/News.asp?id=48425
word: dismso

Vladimir M. Zakharychev

unread,

May 28, 2008, 1:07:52 PM5/28/08

to

> damor...@x.washington.edu (replace x with u to respond)

> Puget Sound Oracle Users Groupwww.psoug.org

I stand corrected. My initial wording was sloppy, I should've stated
that "flashback DROP functionality" is not available in 9i.

Thanks,

Michael Austin

unread,

Jun 2, 2008, 8:57:09 PM6/2/08

to

Just curious, but what good is having ARCHIVE LOG MODE running without
ever doing a backup?

Second Q: do you have an updated resume? After this, you may need it.

joel garry

unread,

Jun 3, 2008, 1:59:05 PM6/3/08

to

On Jun 2, 5:57 pm, Michael Austin <maus...@firstdbasource.com> wrote:

> rajnikant.par...@gmail.com wrote:
> > Dear All
>
> > I have accidently drop a table, Is it possible to recover this table
> > in oracle 9i ?
> > I have no backup
> > and My database is running in archive log mode.
>
> > Can i recover a table from log file ?
>
> > Thank you
>
> > Raj
>
> Just curious, but what good is having ARCHIVE LOG MODE running without
> ever doing a backup?

It sure does seem stupid, but I've wound up doing this on test db's
where I need to test performance or space, when the db comes from a
restoration or other type of load. And in 10g, you gotta watch to see
if the backup jobs work after patching.

>
> Second Q: do you have an updated resume? After this, you may need it.

:-)

Depends.

jg
--
@home.com is bogus.

Damn compuserve billing systems to hell.
word: rescurea