Purge the recycle bin

Frequent deletion and rebuilding of database objects generate a large amount of data in the recycle bin. You can purge the recycle bin to clear the data.

The recycle bin can be manually or automatically purged. The sys tenant can purge four types of recycle bin objects: INDEX, TABLE, DATABASE, and TENANT. MySQL-compatible user tenants can purge three types: INDEX, TABLE, and DATABASE. Oracle-compatible user tenants can purge two types: INDEX and TABLE.

Manually purge the recycle bin

You can execute the PURGE statement to purge the recycle bin.

Considerations

• The PURGE statement deletes an object and the objects that depend on it (that is, Database -> Table -> Index). For example, in MySQL-compatible mode, purging a database deletes the database and the tables and table indexes that belong to it.

• When an upper-layer object of an object is purged, the related lower-layer objects in the recycle bin are also purged.

• Before you execute the PURGE statement, we recommend that you confirm the objects to be deleted in the recycle bin. After you execute the PURGE statement, the object information can no longer be queried in the recycle bin of OceanBase Database, and the actual data will eventually be garbage collected.

Procedure

1. Log in to the database as a tenant administrator of the sys tenant or a user tenant.

   Note

   • The administrator user of a MySQL-compatible tenant is root, and that of an Oracle-compatible tenant is SYS.
   • To purge tenant recycle bin objects, you must log in to the database as the sys tenant.

   Below is a connection example. Use your actual environment when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

   For more detailed guidance on connecting to the database, see Overview of connection methods (MySQL-compatible mode) and Overview of connection methods (Oracle-compatible mode).

2. Execute the SHOW RECYCLEBIN statement to obtain the names of objects in the recycle bin.

   obclient [(none)]> SHOW RECYCLEBIN;
   +-------------------------------------+-------------------+----------+----------------------------+
   | OBJECT_NAME                         | ORIGINAL_NAME     | TYPE     | CREATETIME                 |
   +-------------------------------------+-------------------+----------+----------------------------+
   | RECYCLE_$_100017_1672050541224936   | oracle001         | TENANT   | 2023-01-16 11:01:40.258000 |
   | __recycle_$_100017_1673426335319344 | __idx_500788_idx1 | INDEX    | 2023-01-11 16:38:55.318878 |
   | __recycle_$_100017_1673426335331800 | t1                | TABLE    | 2023-01-11 16:38:55.331481 |
   | __recycle_$_100017_1673854707660504 | infotest          | DATABASE | 2023-01-16 15:38:27.660436 |
   +-------------------------------------+-------------------+----------+----------------------------+
   4 rows in set
   

3. Select an appropriate scenario based on your business needs to purge the recycle bin.

   • Permanently purge a tenant from the recycle bin

     When you permanently purge a tenant from the recycle bin, all objects under the tenant are also purged. Only the sys tenant supports purging tenant recycle bin objects.

     The syntax is as follows:

      obclient [(none)]> PURGE TENANT tenant_name;
     

     Here, tenant_name specifies the name in the recycle bin or the original name. When using the original name, if multiple tenants in the recycle bin currently have the same original name, the tenant that entered the recycle bin earliest is purged.

     Example:

      obclient [(none)]> PURGE TENANT oracle001;
     
      obclient [(none)]> PURGE TENANT RECYCLE_$_100017_1672050541224936;
     

   • Physically purge a specified database from the recycle bin

     Only the sys tenant and MySQL-compatible user tenants support purging database objects from the recycle bin.

      obclient> PURGE DATABASE object_name;
     

     Here, object_name specifies the name of the schema object in the recycle bin. The original name is not supported.

     Example:

      obclient [(none)]> PURGE DATABASE __recycle_$_100017_1673854707660504;
     

   • Physically purge a specified table from the recycle bin

      obclient> PURGE TABLE object_name;
     

     Here, object_name specifies the name of the schema object in the recycle bin or the original name. When using the original name, if multiple objects in the recycle bin currently have the same original name, the table that entered the recycle bin earliest is purged.

     Example:

      obclient [(none)]> PURGE TABLE __recycle_$_100017_1673426335331800;
     

   • Physically purge a specified index from the recycle bin

      obclient> PURGE INDEX object_name;
     

     Here, object_name specifies the name of the schema object in the recycle bin. The original name is not supported.

     Example:

      obclient [(none)]> PURGE INDEX __recycle_$_100017_1673426335319344;
     

   • Permanently purge all objects from the recycle bin

      obclient [(none)]> PURGE RECYCLEBIN;
     

4. After the command is executed successfully, you can execute the SHOW RECYCLEBIN statement again to confirm whether the objects in the recycle bin have been successfully purged.

   obclient [(none)]> SHOW RECYCLEBIN;
   

Automatically purge the recycle bin

OceanBase Database currently supports automatically purging expired schema objects from the recycle bin through the cluster-level parameter recyclebin_object_expire_time. The default value is 0s. Where:

• When the value is 0s, the automatic purge recycle bin feature is disabled.

• When the value is not 0s, objects that entered the recycle bin before the specified period (set by recyclebin_object_expire_time) are purged.

For more information about the cluster-level parameter recyclebin_object_expire_time, see recyclebin_object_expire_time.

1. Log in to the sys tenant of the database as the root user.

   Below is a connection example. Use your actual environment when connecting to the database.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

   For more detailed guidance on connecting to the database, see Overview of connection methods (MySQL-compatible mode).

2. Execute the following statement to view the automatic purge strategy of the recycle bin.

   obclient [(none)]> SHOW PARAMETERS LIKE 'recyclebin_object_expire_time';
   

   The query result is as follows.

   +-------+----------+----------------+----------+-------------------------------+-----------+-------+--------------------------------------------------------------------------------------------------+--------------+---------+---------+-------------------+---------------+-----------+
   | zone  | svr_type | svr_ip         | svr_port | name                          | data_type | value | info                                                                                             | section      | scope   | source  | edit_level        | default_value | isdefault |
   +-------+----------+----------------+----------+-------------------------------+-----------+-------+--------------------------------------------------------------------------------------------------+--------------+---------+---------+-------------------+---------------+-----------+
   | zone1 | observer | 172.xx.xxx.xxx |     2882 | recyclebin_object_expire_time | TIME      | 0s    | recyclebin object expire time, default 0 that means auto purge recyclebin off. Range: [0s, +∞)   | ROOT_SERVICE | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | 0s            |         1 |
   +-------+----------+----------------+----------+-------------------------------+-----------+-------+--------------------------------------------------------------------------------------------------+--------------+---------+---------+-------------------+---------------+-----------+
   1 row in set
   

3. Execute the following statement to enable the automatic purge recycle bin feature and specify how long ago objects must have entered the recycle bin to be purged.

   Example: Enable the automatic purge recycle bin feature and purge schema objects that entered the recycle bin 7 days ago:

   obclient [(none)]>  ALTER SYSTEM SET recyclebin_object_expire_time = '7d';
   

4. (Optional) After completion, you can execute the following statement to confirm whether the objects that meet the specified conditions have been purged from the recycle bin:

   obclient [(none)]> SHOW RECYCLEBIN;
   

References

For more information about the recycle bin, see the following:

• Recycle bin overview

• Enable or disable the recycle bin

• View objects in the recycle bin

• Restore objects from the recycle bin