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.
Answer by Andrew Mobbs ·
If your database has the RECYCLEBIN option enabled, you can easily restore a dropped table. Try the following command in SQL*Plus:
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;
FLASHBACK TABLE <originalTableName> TO BEFORE DROP RENAME TO <newTableName>;