Recycle bin for databases, tables, and indexes

View the recycle bin

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

obclient> SHOW RECYCLEBIN;

Enable and disable the recycle bin

By default, the recycle bin is enabled after a tenant is created. You can perform the Truncate or Drop operation to move a database object to the recycle bin.

You can run the following commands to enable or disable the recycle bin for tenants or sessions:

• Enable and disable the recycle bin for tenants

  obclient> SET GLOBAL recyclebin = on /off;
  

• Enable and disable the recycle bin for sessions

  obclient> SET @@recyclebin = on/off;
  

Restore objects from the recycle bin

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

Examples:

• Restore an object database

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

• Restore an object table

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

Use constraints:

• When database objects are restored by using the FLASHBACK command, the databases, tables, and indexes are restored in that sequence.

• In Oracle mode, indexes in a table are not restored along with the table and must be rebuilt.

• In MySQL mode, indexes in a table are restored along with the table.

• Table indexes can be purged by using the PURGE command, but cannot be restored by using the FLASHBACK command.

• If a table in a table group is 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.

Manually purge 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. Notice

The PURGE command deletes an object along with its dependent objects. For example, when a database is purged, tables and indexes in the database are also purged.

The PURGE command deletes the metadata of an object and its records in __all_recyclebin. The PURGE command completely deletes the information about an object from 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.

Sample commands:

• Physically delete a specified database from the recycle bin

  obclient> PURGE DATABASE object_name;
  

• Physically delete a specified table from the recycle bin

  obclient> PURGE TABLE object_name;
  

• Physically delete a specified index from the recycle bin

  obclient> PURGE INDEX object_name;
  

• Empty the entire recycle bin

  obclient> PURGE RECYCLEBIN;
  

Automatically purge the recycle bin

OceanBase Database allows you to set recyclebin_object_expire_time to automatically purge expired schema objects in the recycle bin. The default value of this parameter is 0s. Notes:

• When the value is 0s, the schema objects in the recycle bin will not be automatically purged.

• When the value is not 0s, the schema objects that have been stored in the recycle bin for the specified period of time are automatically purged.

For example, you can run the following command to automatically purge schema objects that have been stored in the recycle bin for seven days:

obclient> ALTER SYSTEM SET recyclebin_object_expire_time = "7d";