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. In the sys tenant, you can purge INDEX, TABLE, DATABASE, and TENANT objects from the recycle bin. In a MySQL user tenant, you can purge INDEX, TABLE, and DATABASE objects from the recycle bin. In an Oracle user tenant, you can purge INDEX and TABLE objects from the recycle bin.

Manually purge the recycle bin

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

Considerations

• The PURGE statement deletes an object along with its dependent objects in the recycle bin. For example, when you purge a database from the recycle bin in MySQL mode, tables and indexes in the database are also purged.

• 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.

• Before you execute the PURGE statement, we recommend that you check the objects to be deleted in the recycle bin. The PURGE statement deletes the information about an object in the recycle bin of OceanBase Database. The actual data is also deleted.

Procedure

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

   Note

   • The administrator user of a MySQL user tenant is root and that of an Oracle user tenant is SYS.
   • If you want to purge tenants from the recycle bin, log on to the database as an administrator of the sys tenant.

   Note that you must specify the corresponding fields in the following sample code based on your actual database configurations.

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

   For more information about how to connect to a database, see Overview (MySQL mode) or Overview (Oracle mode).

2. Execute the SHOW RECYCLEBIN statement to query for 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. Purge the recycle bin in an appropriate way based on your business needs.

   • Purge a tenant from the recycle bin

     If you purge a tenant from the recycle bin, all objects in the tenant are purged. You can purge tenants from the recycle bin only in the sys tenant.

     The syntax is as follows:

     obclient [(none)]> PURGE TENANT tenant_name;
     

     Here, tenant_name specifies the name of the tenant in the recycle bin or the original name of the tenant. If multiple tenants have the same original name, the first tenant moved to the recycle bin is purged.

     For example:

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

   • Physically purge a database from the recycle bin

     You can purge databases from the recycle bin only in the sys tenant or a MySQL user tenant.

     obclient> PURGE DATABASE object_name;
     

     Here, object_name specifies the name of the database in the recycle bin. You cannot set this parameter to the original name of the database.

     For example:

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

   • Physically purge a table from the recycle bin

     obclient> PURGE TABLE object_name;
     

     Here, object_name specifies the name of the table in the recycle bin or the original name of the table. If multiple tables have the same original name, the first table moved to the recycle bin is purged.

     For example:

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

   • Physically purge an index from the recycle bin

     obclient> PURGE INDEX object_name;
     

     Here, object_name specifies the name of the database in the recycle bin. You cannot set this parameter to the original name of the database.

     For example:

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

   • Purge all objects from the recycle bin

     obclient [(none)]> PURGE RECYCLEBIN;
     

4. Execute the SHOW RECYCLEBIN statement to check whether all the objects are purged from the recycle bin.

   obclient [(none)]> SHOW RECYCLEBIN;
   

Automatically purge the recycle bin

OceanBase Database allows you to set the recyclebin_object_expire_time parameter for a cluster to automatically purge expired schema objects from the recycle bin. The default value of this parameter is 0s.

• 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 more information about the recyclebin_object_expire_time parameter, see recyclebin_object_expire_time.

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

   Note that you must specify the corresponding fields in the following sample code based on your actual database configurations.

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

   For more information, see Overview.

2. Enable automatic purge of the recycle bin and specify the expiration period for objects in the recycle bin.

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

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

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

   obclient [(none)]> SHOW RECYCLEBIN;
   

References

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

• Overview

• Enable or disable the recycle bin

• View objects in the recycle bin

• Restore objects from the recycle bin