Recycle bin for databases, tables, and indexes

This topic describes how to manage the recycle bin for databases, tables, and indexes. This includes how to view objects in a recycle bin and how to enable, disable, and purge a recycle bin.

View objects in the recycle bin

Background information

When you drop a database object:

• If the recycle bin is enabled, the dropped object is then moved to the recycle bin and can be viewed in the recycle bin.

• If the recycle bin is disabled, the object is not moved to the recycle bin and cannot be viewed in the recycle bin.

Procedure

1. Log on to the database as a tenant administrator.

2. Execute the following statement to view objects in the recycle bin:

   obclient> SHOW RECYCLEBIN;
   +-------------------------------------+---------------+-------+----------------------------+
   | OBJECT_NAME                         | ORIGINAL_NAME | TYPE  | CREATETIME                 |
   +-------------------------------------+---------------+-------+----------------------------+
   | __recycle_$_100015_1637217861309360 | t1            | TABLE | 2021-11-18 14:44:21.310663 |
   +-------------------------------------+---------------+-------+----------------------------+
   1 row in set
   

   where

   • OBJECT_NAME indicates the name of an object in the recycle bin.

   • ORIGINAL_NAME indicates the original name of the object.

   • TYPE indicates the type of the object in the recycle bin.

     • In MySQL mode of OceanBase Database, the object type can be INDEX, TABLE, or DATABASE.

     • In Oracle mode of OceanBase Database, the object type can be INDEX or TABLE.

   • CREATETIME indicates the time when the object was placed in the recycle bin.

Enable or disable the recycle bin

After a tenant is created, the recycle bin is disabled by default. If the recycle bin is then enabled, dropped objects will be moved to the recycle bin.

1. Log on to the database as a tenant administrator.

2. Execute the following statement to view the state of the recycle bin:

   obclient> SHOW VARIABLES LIKE 'recyclebin';
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | recyclebin    | OFF   |
   +---------------+-------+
   1 row in set
   

3. Choose an appropriate method to enable or disable the recycle bin based on your business needs.

   • Execute the statements below to enable or disable the global recycle bin.

     The setting of the global recycle bin applies to the entire tenant, but not to previously created sessions. To apply the setting to an existing session, you need to log off and log on to the database again and then re-create the session.

     The recyclebin variable is Boolean and can be set to one of the following values:

     • on, 1, or true: indicates that the recycle bin is enabled.

     • off, 0, or false: indicates that the recycle bin is disabled.

     Syntax:

     obclient> SET GLOBAL recyclebin = on;
     
     obclient> SET GLOBAL recyclebin = off;
     

     or

     obclient> SET GLOBAL recyclebin = 1;
     
     obclient> SET GLOBAL recyclebin = 0;
     

     or

     obclient> SET GLOBAL recyclebin = true;
     
     obclient> SET GLOBAL recyclebin = false;
     

   • Execute the statements below to enable or disable the session-level recycle bin.

     The setting of the session-level recycle bin only applies to the current session.

     Syntax:

     obclient> SET @@recyclebin = on;
     
     obclient> SET @@recyclebin = off;
     

     or

     obclient> SET @@recyclebin = 1;
     
     obclient> SET @@recyclebin = 0;
     

     or

     obclient> SET @@recyclebin = true;
     
     obclient> SET @@recyclebin = false;
     

Manually purge the recycle bin

Frequently dropping and re-creating database objects will generate a large amount of data in the recycle bin, which can be cleaned using the PURGE statement.

Notice

The PURGE statement will delete an object and any objects that are subordinate to it. For example, purging a database will delete not only the database itself but also the tables and indexes in it.

Before executing the PURGE statement, you can view the dropped objects in the recycle bin. After executing the PURGE statement, the information about the objects will no longer be available in the recycle bin of OceanBase Database, and the actual data will eventually be garbage collected.

If an upper-level object is purged, the associated lower-level objects in the current recycle bin will also be purged.

The procedure for manually purging the recycle bin is as follows:

1. Log on to the database as a tenant administrator.

2. Execute the following statements to purge the recycle bin.

   The object_name parameter in the following statements indicates the name of the schema object in the recycle bin. To obtain this name, execute the SHOW RECYCLEBIN statement.

   • Physically purge a database from the recycle bin

     obclient> PURGE DATABASE object_name;
     

   • Physically purge a table from the recycle bin

     obclient> PURGE TABLE object_name;
     

   • Physically purge an index from the recycle bin

     obclient> PURGE INDEX object_name;
     

   • Purge the entire recycle bin

     obclient> PURGE RECYCLEBIN;
     

Automatically purge the recycle bin

OceanBase Database supports automatically cleaning up expired schema objects in the recycle bin through the cluster-level parameter recyclebin_object_expire_time. Its default value 0s.

• If the value is 0s, the schema objects in the recycle bin are not automatically purged.

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

For more information about the recyclebin_object_expire_time parameter, see recyclebin_object_expire_time.

The procedure for enabling the automatic purge feature is as follows:

1. Log on to the sys tenant as the root user.

2. Execute the following statement to enable the automatic purge feature and purge the schema objects that were put in the recycle bin, for example, seven days ago:

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

3. (Optional) Execute the following statement to check whether the objects that meet the specified conditions have been purged from the recycle bin:

   obclient> SHOW RECYCLEBIN;