Prevent auto commit of DROP without SET AUTOCOMMIT OFF I dropped my table and I lost my data and I can't roll it back because DROP statement automatically commits. How can I prevent auto commit action of DROP without using SET AUTOCOMMIT OFF?
DROP and TRUNCATE are Data Definition Language commands and thus cannot be rolled back. However, in Oracle you can use the following technologies to recover your table:


Flashback Drop
Flashback Database
Tablespace Point in Time Recovery
Table-Level Recovery From Backups (new in 12c)


Flashback Drop

If the Recycle Bin is enabled in your database, you can use the following command to recover your table (if it's still in the Recycle Bin).

SQL&gt; flashback table1 to before drop;


If your table is not in the Recycle Bin (it was purged by the user manually or due to space pressure), you can attempt to recover your database from backup to another host and export/import the data and associated metadata to the production database using Data Pump Export and Import commands (or DBMS_DATAPUMP package).

Flashback Database

If you have Flashback Database enabled (check the flashback_database column in v$database view), you can flashback your database to the point before your table was dropped. But you must ensure that there's enough data in flashback logs which you can check using v$flashback_database_log view.

Tablespace Point in Time Recovery

To recover your table using tablespace point in time recovery (TSPITR), you can use the recover tablespace until ... command. But be sure to check what objects can be lost in the tablespace being recovered by checking SYS.TS_PITR_OBJECTS_TO_BE_DROPPED view. These objects were created after the recovery point you specify in this command. You would export them before TSPITR and re-import once TSPITR is complete.

Table-Level Recovery From Backups

New in Oracle Database 12c is the ability to recover individual tables. You employ it by using recover table command in RMAN, and it works very similar to TSPITR – creating auxiliary instance, – but on a more granular (table) level.

See also.


Using Flashback Drop and Managing the Recycle Bin in Database Administrator's Guide.
Using Flashback Database and Restore Points in Database Backup and Recovery User's Guide.
Performing RMAN Tablespace Point-in-Time Recovery (TSPITR) in Database Backup and Recovery User's Guide.
Table-Level Recovery From Backups in Oracle Database 12c Release 1 (12.1.0.1) New Features Guide.
Recovering Tables and Table Partitions from RMAN Backups in Oracle Database Backup and Recovery User's Guide.