how to recover dropped table

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.
harris2107Asked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
schwertnerCommented:
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
 
IanStuartConnect With a Mentor Commented:
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
 
Computer101Commented:
Forced accept.

Computer101
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.