question

OraLearner avatar image
OraLearner asked

Can we restore a dropped table after a new table with the same name has been created?

Anyone knows can we restore a database table which was deleted if we have a new table created with the same name. If possible how to do this ? is there any special syntax for this ? Thanks in advance.

queryoracledatabaserebuild
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Andrew Mobbs avatar image
Andrew Mobbs answered

If your database has the RECYCLEBIN option enabled, you can easily restore a dropped table. Try the following command in SQL*Plus:

show recyclebin;

If you have the recyclebin enabled, you will see the dropped tables with their original name and their recyclebin name (starts with BIN$).

You can then query the dropped table with normal SQL (but you may need to quote the name):

SELECT * FROM "BIN$<restofname>"

You can also restore it (and optionally rename it) with:

FLASHBACK TABLE <originalTableName> TO BEFORE DROP;

or

FLASHBACK TABLE <originalTableName> TO BEFORE DROP RENAME TO <newTableName>;
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

OraLearner avatar image OraLearner commented ·
Excellent..its working for me. Thank you for your quick reply.
0 Likes 0 ·
KillerDBA avatar image KillerDBA commented ·
"show recyclebin;" yields "SHOW is not supported." How does one enable support for SHOW?
0 Likes 0 ·
Andrew Mobbs avatar image Andrew Mobbs commented ·
@KillerDBA Are you using the command line SQL*Plus or another tool (e.g. iSQLPlus or SQL Developer)? As far as I know, "show" is a built-in command for SQL*Plus, so should just work.
0 Likes 0 ·
OraLearner avatar image
OraLearner answered

Please refer the answer posted by 'Andrew Mobbs' which is working like gem.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

krishnaReddy avatar image
krishnaReddy answered
is it working for SQL also? if it is not usefull for sql then tell me the solution for sql
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.