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?
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)
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> 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
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
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.
- 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 (18.104.22.168) New Features Guide.
- Recovering Tables and Table Partitions from RMAN Backups in Oracle Database Backup and Recovery User's Guide.
SET AUTOCOMMIT OFF won't help either. The oracle documentation says:
Oracle Database issues an implicit COMMIT before and after any data definition language (DDL) statement.
DROP definitely is a DDL-statement. There's no way around it. See Yasir Arsanukaev's answer for ways how you could still recover your data.