Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

how to recover dropped table

Posted on 2007-12-02
7
Medium Priority
?
5,774 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 600 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 600 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 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