OraFAQ Forum: Backup & Recovery » table dropped without backup

Home » RDBMS Server » Backup & Recovery » table dropped without backup

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

table dropped without backup [message #198040]

Sat, 14 October 2006 01:21

oracle_coorgi
Messages: 188
Registered: September 2006
Location: INDIA-karnataka

Senior Member

hi
we have created table on tuesday 09/10/2006 but droped for some reason on friday 13/10/2006 the application guy says he needs it back
we have not taken any type (logicial/physcial)backup,how can i get that table back

pls advice me

thanxs

Report message to a moderator

Re: table dropped without backup [message #198049 is a reply to message #198040]

Sat, 14 October 2006 02:43

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

What bkp procedure ur used and Oracle Version.
if 10g. then used flashback technolog option.

plz. let me know .

Report message to a moderator

Re: table dropped without backup [message #198061 is a reply to message #198049]

Sat, 14 October 2006 03:38

Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

They do NOT have a backup.

However, yes - if your database version is 10g and Flashback is enabled you could try to recover dropped table from Recycle Bin, using something like this:

First, find out object name (I suppose you know the original name; 'droptime' column will also help:

SELECT * FROM user_recyclebin WHERE original_name = 'TEST';

Now copy & paste object name and, using CTAS, recover the table:

CREATE TABLE TEST AS 
SELECT * FROM "BIN$wVGVa3gUQZeKXB0XP5rN7A==$0";

Report message to a moderator

Re: table dropped without backup [message #198065 is a reply to message #198061]

Sat, 14 October 2006 04:05

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

no dear don't take headache doing "lengthy" name
SQL> create table "flashback" ( no number);

Table created.

SQL> drop table "flashback";

Table dropped.

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------

flashback BIN$SYw5JKJaST2z4Z10UepSwQ==$0 TABLE 2006-10-14:13:02:47

SQL> select * from "flashback";
select * from "flashback"
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> flashback table "flashback" to before drop;

Flashback complete.

SQL> select * from "flashback";

no rows selected

SQL>

Report message to a moderator

Previous Topic:	Trying to recover dropped table
Next Topic:	VNI-2024 : Couldnt fork authentication process.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 08 08:34:59 CDT 2024