How Do I recover data from a dropped Table?

Question

I accidentally dropped a table, and I recovered it with flashback statement and recycle bin information:

FLASHBACK TABLE "XXXXXXXXXXXXXXX==$0" TO BEFORE DROP;

But when I made it a select * from XXX it was empty.
Is there a way to recover the rows/information/data from that table?

OR, Does flashback recover all rows? maybe it's empty because it didn't have nothing at the first time.

score 2 · Accepted Answer · answered Mar 21 '14 at 17:03

2

Have you tried FLASHBACK TABLE "XXXXXXXXXXXXXXX==$0" TO TIMESTAMP Clause? http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmflash.htm#BRADV8702

FLASHBACK should recover the rows. This is its main purpose.

answered Mar 21 '14 at 17:03

Pellared

1,242
2
14
29

No, I haven't, but I think it was enough with "TO BEFORE DROP" right? do you know if I use that, it recovers all information? maybe the table was originally empty, and that's why I don't see nothing now – Pablo Glez Mar 21 '14 at 17:07
`XXX` is your orginal table name? – Pellared Mar 21 '14 at 17:09
It is very possible that it was empty. But maybe someone removed the data before for example using `DELETE`. This is why I would just check the `FLASHBACK TABLE XXX TO TIMESTAMP TO_TIMESTAMP('2013-10-17 09:30:00', 'YYYY-MM-DD HH:MI:SS');` – Pellared Mar 21 '14 at 17:11
but I made a Flashback table and the Recycle bin [XXXX = "BIN$9Od2TS4UJZOnPgQ34HQArn3Q==$0"] doesn't exist anymore, what can I do? – Pablo Glez Mar 21 '14 at 17:19
@PabloGlez you're SOL if you don't have offline backups. – SQLMason Mar 21 '14 at 17:22
Unless I drop it again and then I can make that, but I'm not sure, should I? – Pablo Glez Mar 21 '14 at 17:22
Sorry, but I don't know, what it "SOL"? – Pablo Glez Mar 21 '14 at 17:23
shit out of luck (not comparable) (idiomatic) Completely out of luck; in unfortunate circumstances such that all options for achieving one's goal are exhausted; http://en.wiktionary.org/wiki/shit_out_of_luck – SQLMason Mar 21 '14 at 17:27
@PabloGlez As `XXX` enter your table name. Flashback is not only for droped table. You can event flashback the whole database to given timestamp. – Pellared Mar 21 '14 at 17:28
1

THAAAANK YOU! IT WORKED! I only had to enables the row movement! – Pablo Glez Mar 21 '14 at 17:39

score 0 · Answer 2 · answered Mar 21 '14 at 17:07

0

Flashback recovers all fields and records. If it is empty, then it most probably did not had anything before it was dropped.

answered Mar 21 '14 at 17:07

Max

4,067
1
18
29

How Do I recover data from a dropped Table?

2 Answers2