Recycle bin

The recycle bin is a data dictionary table in principle. It stores information about database and table objects that are deleted by users. Information in the recycle bin still occupies physical space unless being manually cleared by using the PURGE command or periodically deleted by the system.

Objects supported by the recycle bin

The following table lists the objects that are supported or not supported by the recycle bin in the current OceanBase Database version.

Notice

• A dropped index is not stored in the recycle bin. After a table is deleted by a user, the indexes in the table are moved together with the table to the recycle bin.
• Query or DML operations are not allowed on the objects in the recycle bin. Among the DDL operations, only purge and flashback operations are supported on the objects in the recycle bin.
• The recycle bin is managed by the SYS tenant.

View the recycle bin

The tenant administrator can run the following command to view the objects in the recycle bin:

obclient> SHOW RECYCLEBIN;

Enable or disable the recycle bin

After a tenant is created, the recycle bin is disabled by default. If the recycle bin is enabled, after a TRUNCATE or a DROP operation is performed on a database object, the database object is moved to the recycle bin. OceanBase Database provides global and session-level commands for enabling or disabling the recycle bin.

• Global command for enabling or disabling the recycle bin:

  obclient> SET GLOBAL recyclebin = ON/OFF;
  

• Session-level command for enabling or disabling the recycle bin:

  obclient> SET @@recyclebin = ON/OFF;
  

Restore objects in the recycle bin

The tenant administrator can run the FLASHBACK command to restore database and table objects from the recycle bin. The object name can be modified upon restoration but must be different from an existing object name.

The following list shows the commands for restoring tenants, databases, and tables from the recycle bin:

• Restore a tenant

  obclient> FLASHBACK TENANT tenant_name TO BEFORE DROP [RENAME to new_tenant_name];
  

• Restore a database

  obclient> FLASHBACK DATABASE object_name TO BEFORE DROP [RENAME TO database_name];
  

• Restore a table

  obclient> FLASHBACK TABLE object_name TO BEFORE DROP [RENAME to table_name];
  

The following list describes the limits on these commands:

• When database objects are restored by using the FLASHBACK command, the objects are restored in the sequence of database > table.

• When a table is restored, the indexes in this table are also restored.

• Indexes can be separately purged by using the PURGE command, but cannot be separately restored by using the FLASHBACK command.

• If a table in a table group is deleted and moved to the recycle bin and then the table group is deleted, the table does not belong to any table group after it is restored. If the table group is not deleted, the table still belongs to the table group after it is restored.

Clear the recycle bin

Frequent deletion and rebuilding of database objects generate a large amount of data in the recycle bin. You can run the PURGE command to clear the data. The PURGE command deletes an object along with its dependent objects. For example, when a database is purged, the tables and indexes in the database are also deleted.

The PURGE command permanently deletes the information about an object from the recycle bin of OceanBase Database, and the actual data is also recycled. When an upper-layer object of a current object is purged, the objects dependent on the current object in the recycle bin are also purged.

The following examples show how to use the PURGE command to clear specific data in the recycle bin:

• Use the SYS tenant to physically purge a specified tenant from the recycle bin.

  obclient> PURGE TENANT tenant_name;
  

• Physically purge a specified database from the recycle bin.

  obclient> PURGE DATABASE object_name;
  

• Physically purge a specified table from the recycle bin.

  obclient> PURGE TABLE object_name;
  

• Physically purge a specified index table from the recycle bin.

  obclient> PURGE TABLE object_name;
  

• Clear the entire recycle bin.

  obclient> PURGE RECYCLEBIN;