Recycle bin

The recycle bin is a data dictionary table in principle. It stores information about database objects deleted by users, including databases and tables. Objects moved to the recycle bin still occupy physical space unless they are manually cleared by using the PURGE command or periodically deleted by the system.

Objects in the recycle bin

In the current version, the recycle bin supports the following objects: indexes, tables, databases, and tenants. Since only the sys tenant can delete a tenant, MySQL user tenants and Oracle user tenants are deleted and moved to the recycle bin of the sys tenant.

• The sys tenant can manage the following objects in the recycle bin: databases, tables, indexes, and tenants.

• A MySQL user tenant can manage the following objects in the recycle bin: databases, tables, and indexes.

• An Oracle user tenant can manage the following objects in the recycle bin: tables and indexes.

View the objects in 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 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.

• The global command for enabling or disabling the recycle bin is as follows:

  obclient> SET GLOBAL recyclebin = ON/OFF;
  

• The session-level command for enabling or disabling the recycle bin is as follows:

  obclient> SET @@recyclebin = ON/OFF;
  

Restore objects from 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 restore 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 limitations on these commands:

• When database objects are restored by using the FLASHBACK command, the restore order must follow the dependency hierarchy: 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 belonged to a table group before it was moved to the recycle bin, restoring the table after the table group has been deleted causes it to belong to no table group. If the table group still exists, the table remains in the original table group after it is restored.

Purge objects in the recycle bin

Frequent deletion and rebuilding of database objects generate a large amount of data in the recycle bin. You can execute the PURGE statement to clear the data. The PURGE command deletes an object along with its dependent objects (Database > Table > Index).

After you run the PURGE command, the object can no longer be found in the recycle bin of OceanBase Database, and the actual data is eventually garbage-collected. When the parent object of an object is purged, the child objects associated with it 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 INDEX object_name;
  

• Clear the entire recycle bin.

  obclient> PURGE RECYCLEBIN;