0

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?

migrated from stackoverflow.com Oct 5 '14 at 3:51

This question came from our site for professional and enthusiast programmers.

7

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:

  1. Flashback Drop
  2. Flashback Database
  3. Tablespace Point in Time Recovery
  4. 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.

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

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy