How to find which tables were deleted (in Oracle)

Question

A user dropped number of tables (~1449) on Development server and the user has no record of the tables name that were dropped.

Things we know: 1. Date on which the action was performed. 2. Username of the user who executed the statements.

Is it possible to get the table names (that were dropped)?

Answer 1

If you have the recyclebin turned on in your database you can query the DBA_RECYCLEBIN view to find which objects were dropped within a certain time period.

SELECT owner,
       original_name,
       operation,
       droptime,
       can_undrop
FROM   dba_recyclebin
WHERE  TO_DATE (droptime, 'YYYY-MM-DD:HH24:MI:SS') BETWEEN SYSDATE - 3
                                                       AND SYSDATE

You can issue a FLASHBACK command to restore the table;

FLASHBACK TABLE <you table's original name> TO BEFORE DROP

Alternatives are to flashback to a specific SCN, TIMESTAMP, etc.

See https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9012.htm#SQLRF01802 and http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm#ADMIN01511 for some more reading (it never hurts to read)

Answer 2

Hopefully, if you have post Oracle 10g ... and the recycle bin feature is enabled, the SYS user of you DB could do the following from sqlplus:

SQL> show recyclebin

Hopefully you'll see your missing tables. After that read this and you should be fine.