Xref: alice comp.databases.oracle.misc:29658 comp.databases.oracle.server:47055
Path: alice!news-feed.fnsi.net!hammer.uoregon.edu!qualcomm.com!uwvax!enews.sgi.com!news-feed.inet.tele.dk!bofh.vszbr.cz!masternews.telia.net!News.Amsterdam.UnisourceCS!news.wxs.nl!not-for-mail
From: Erwin Dondorp <erwindon@wxs.nl>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server
Subject: Re: Urgent: Accidentally dropped tables
Date: Sun, 18 Apr 1999 20:36:23 +0200
Organization: World Access / Planet Internet
Lines: 57
Message-ID: <371A2627.BC8A6E7E@wxs.nl>
References: <7fcn4s$3nh$1@nnrp1.dejanews.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: reader1.wxs.nl 924460743 25842 195.121.72.192 (18 Apr 1999 18:39:03 GMT)
X-Complaints-To: abuse@wxs.nl
NNTP-Posting-Date: 18 Apr 1999 18:39:03 GMT
X-Mailer: Mozilla 4.51 [en] (Win98; I)
X-Accept-Language: en

Atif,

Someone has to tell you the bad news. So it might as well be me.

To recover from accidental SQL statements (usually DROP TABLE of DELETE
without WHERE statement),
you need to restore a backup of your database.
If your database was in archive logmode and you kept the archived logs you
can roll-forward the database to just
the point in time before the accidental statements.

Since you don't have a backup to start with, you will not be able to do
that.

The rollback segments only contain information DURING a transaction.
The session in which you dropped the table has probably been ended already,
but that does not matter.
All the DDL statements (DROP, ALTER, CREATE) are auto-committing.
The information in a rollback segment is discarded on every COMMIT or
ROLLBACK.

Erwin

Atif wrote:

> I accidentally dropped the most important tables in my schema.
> It is next to impossible to get all the valuable data back.
> From what I have read so far, it seems that I can only recover
> my tables back if Oracle was running in archive log mode.
>
> The only way I know how to check if it is. is by typing :
>
> SVRMGR> archive log list
> Database log mode              No Archive Mode
> Automatic archival             Disabled
> Archive destination            ?/dbs/arch
> Oldest online log sequence     0
> Current log sequence           1
>
> From this it doesn't seem that my database wasn't running in
> ARCHIVELOG mode.  Is this a safe assumption?  Does Oracle not
> run in ARCHIVELOG made by default.
>
> Do the rollback segments not record all the changes being made
> to the database?  Can these be somehow used to construct my
> tables back?
>
> And I didn't have backup on tape etc.  Is there any way to get
> my tables back?
>
> Thanks a million in advance.
>
> atif55@my-dejanews.com
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own