Home » RDBMS Server » Backup & Recovery » table dropped without backup
table dropped without backup [message #198040] Sat, 14 October 2006 01:21 Go to next message
oracle_coorgi
Messages: 185
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
Re: table dropped without backup [message #198049 is a reply to message #198040] Sat, 14 October 2006 02:43 Go to previous messageGo to next message
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 .
Re: table dropped without backup [message #198061 is a reply to message #198049] Sat, 14 October 2006 03:38 Go to previous messageGo to next message
Littlefoot
Messages: 20897
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";
Re: table dropped without backup [message #198065 is a reply to message #198061] Sat, 14 October 2006 04:05 Go to previous message
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>














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


Current Time: Wed Dec 07 03:06:27 CST 2016

Total time taken to generate the page: 0.11404 seconds