Skip to main content

Search

Welcome to Tibero GTS!

How to Recover Tables Deleted by DROP TABLE Command

Document Type | Technical Information

Category | Backup/Recovery

Document Number | TBATI030

 

Overview

This guide explains how to recover tables deleted by the DROP TABLE command using the Recyclebin.
If a table is accidentally deleted by a user, the recycle bin feature allows for rapid recovery of the table in emergency situations.
 
As an example, quick recovery is possible as shown below.
  • Accidentally delete the "EMP" table
SQL> DROP TABLE EMP;
  • Error occurs when querying the table
SQL> SELECT * FROM EMP;  -- Error occurs

TBR-8033: Specified schema object was not found.
  • Query the RECYCLEBIN
SQL> SELECT OWNER, OBJECT_NAME, TYPE, TS_NAME, DROPTIME
          FROM  DBA_RECYCLEBIN
          WHERE ORIGINAL_NAME='T100';

OWNER   OBJECT_NAME       TYPE     TS_NAME      DROPTIME
------- ----------------  ------   ----------   --------------------
EDU     EDU_TBL285600     TABLE    TBS_EDU      2020-09-08:05:28:14
  • Quickly recover the "EMP" table
SQL> FLASHBACK TABLE EMP TO BEFORE DROP;

SQL> SELECT * FROM EMP;  -- Executes normally

 

Method

Table Deletion and Recovery

When a table is deleted, if the recycle bin feature is enabled, it operates as follows.
  • If the parameter USE_RECYCLEBIN is set to Y, the object is stored in the RECYCLEBIN.
  • Instead of removing the table, its name and status are changed. (The pre-deletion table name is saved separately.)
  • The names of the table, indexes, and constraints are changed to names assigned by the system.
  • The segment remains in the tablespace, only the name is changed.
  • Foreign key constraints are removed.
Note
RECYCLE BIN
  • The Recycle Bin stores dropped tables instead of deleting them, allowing quick recovery of tables accidentally deleted by users.
  • When a dropped table moves to the Recycle Bin, its associated objects and constraints have their names changed. (This prevents name conflicts with tables created later.)
  • The Recycle Bin itself is a data dictionary, and users can query the list of tables in the Recycle Bin through the DBA_RECYCLEBIN view.
  • If the Recycle Bin feature is enabled, you can completely delete tables without moving them to the recycle bin using the DROP TABLE tableName PURGE; statement.
  • Dropped tables continue to occupy space in the original tablespace and can be removed from the recycle bin using the PURGE TABLE tableName; statement.
  • To completely empty the recycle bin, execute PURGE RECYCLEBIN;.

Querying Deleted Tables

Deleted tables can be checked in the RECYCLE BIN as follows.
  • Query deleted tables in DBA_RECYCLEBIN.
  • Tables are no longer visible in DBA_TABLES and DBA_INDEXES.
  • In DBA_OBJECTS, they appear with system-assigned changed OBJECT_NAME. (STATUS is INVALID) (The changed name follows the format Ownername_tblSeqNum, where SeqNum increments sequentially.)
  • In DBA_SEGMENTS, they appear with system-changed SEGMENT_NAME.
 
 

Recovery Method

You can recover using the following command.
FLASHBACK TABLE tableName TO BEFORE DROP;


Example

 FLASHBACK TABLE EDU.T100 TO BEFORE DROP;

You can also recover under a different name.
 FLASHBACK TABLE tableName TO BEFORE DROP RENAME TO tableName;


Example

 FLASHBACK TABLE EDU.T100 TO BEFORE DROP RENAME TO T101;
  • When the table is restored, related objects such as indexes and constraints are also restored.
  • The table name is restored to its previous state or changed as specified.
  • The names of indexes and constraints are not restored to their original names.
  • Foreign keys are not restored.
 

Cases Where Recovery Is Not Possible

In the following cases, even if USE_RECYCLEBIN is set to Y, objects do not enter the RECYCLEBIN and FLASHBACK TABLE is not possible.
  • When dropping EMP table objects owned by the YS account
  • When the original table has been ALTER MOVEd

 

 
 

Related to