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
?
3 Answers
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> 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.
Using 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.
And 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.
Rename (archive) the table. If wanted, remove all records of that table by means of a DELETE command. Then you control when you really lose the data, by means of doing the commit on that DELETE command.