Solved

how to recover dropped table

Posted on 2007-12-02
7
5,738 Views
Last Modified: 2013-12-19
Hi,
      I am working with Oracle 10gR2 on HP-UX 11. I have accidentally dropped a user table from my database ( a test database) and now i want to recover it. I dont have any EXPORT backup of either the database (full) or just the table. Neither do i have a RMAN backup of the database. I have some naive questions regarding this scenario:

1. Suppose if i am running Oracle 9iR2 and i just have a RMAN backup of the database. I do not have any EXPORT backup. How can i go ahead recovering the dropped table?? Is the Full recover just before the dropping only option of recovering it??

2. If I am using Oracle 10gR2 and i have just a RMAN backup of the database, in this case
 
        2.a Can i recover just the dropped table using this RMAN backup??
        2.b There is a option of Flash Recovery Area in Oracle 10gR2. If i set it ON, then i know i can recover the dropped table using flashback logs. If this option is set to OFF, how can i recover the dropped table??

3. Oracle 10gR2 has the RECYCLE BIN option. Any table that has been dropped will be stored in this BIN. Now
      3.a For how much time will this data reside in the RECYCLE BIN?? Is there any retention period for this data in the BIN??
     3.b  Has the RECYCLE BIN got anything to do with the Flash Recovery Area?

Any information will be of a great help.

Thanks,
Harris.
0
Comment
Question by:harris2107
7 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 150 total points
ID: 20393644
if your table name is EMP then,

FLASHBACK TABLE EMP TO BEFORE DROP;

Also refer to url for more details:

http://www.oracle-base.com/articles/10g/Flashback10g.php
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20393653
any dropped table will be in recyclebin until you/DBA purge the recyclebin .

select * from recyclebin;

This will show all the objects which are in the recyclebin for the logged in user.

if you want to see for all users, then

select * from dba_recyclebin;

Thanks
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 20393663
The recyclebin is only available from 10g onwards. so if your version is less than this, then we may have to try to take it from export dump files or cold/hot backup.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:schwertner
ID: 20394660
You said you have RMAN backup of the DB.

Create another instance and recover the database using RMAN.

After that export/import the dropped table.
Of course it will be recovered to the time
you made the RMAN backup.
0
 
LVL 3

Assisted Solution

by:IanStuart
IanStuart earned 150 total points
ID: 20455749
Hello Harris,

Question 1 (V9i): Easiest way would be to restore and recover the database to another destination, then export the table and import it in the other Database.

Question 2 (V10g RMAN Only):a) I'd suggest to let RMAN perform a Tablespae Point in Time Recovery, so you'll not have the overhead to restore/recover the whole instance..
b) Yes, you could recover just the table with flashback.

Question 3 (V10 Recyclebin):
a) The objects will be in the recycle bin as long as the space isn't reclaimed by another object. So if you got plenty of space in your tablespace Oracle tries to hold the objects in the bin as long as possible.
b) No
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20936942
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL - Query help 7 78
format dd/mm/yyyy parameter 16 47
error doing substr 3 33
Oracle 11gR2 Middleware: multiple domains with individual admin servers? 4 18
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question