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 user tenant, you can purge INDEX, TABLE, and DATABASE objects from the recycle bin.
Manually purge the recycle bin
You can execute the PURGE statement to purge the recycle bin.
Considerations
The
PURGEstatement deletes an object along with its dependent objects in the recycle bin. For example, when you purge a database from the recycle bin, tables and indexes in the database are also purged.When the upper-layer object of an object is purged, the objects dependent on the object in the recycle bin are also purged.
Before you execute the
PURGEstatement, we recommend that you check the objects to be deleted in the recycle bin. ThePURGEstatement deletes the information about an object in the recycle bin of OceanBase AI Database. The actual data is also deleted.
Procedure
Log in to the database as the tenant administrator of the
systenant or a user tenant.Note
- The tenant administrator is the
rootuser. - To purge tenant recycle bin objects, you must log in to the database as the
systenant.
Here is an example of the connection command. Use the actual database connection information in your production environment.
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -AFor more information about how to connect to a database, see Overview of connection methods.
- The tenant administrator is the
Execute the
SHOW RECYCLEBINstatement to query for the names of objects in the recycle bin.obclient [(none)]> SHOW RECYCLEBIN; +-------------------------------------+-------------------+----------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +-------------------------------------+-------------------+----------+----------------------------+ | RECYCLE_$_100017_1672050541224936 | mysql001 | 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 setPurge 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
systenant.The syntax is as follows:
obclient [(none)]> PURGE TENANT tenant_name;Here,
tenant_namespecifies 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 mysql001; obclient [(none)]> PURGE TENANT RECYCLE_$_100017_1672050541224936;Physically purge a database from the recycle bin
The
systenant and user tenants can purge database objects from the recycle bin.obclient> PURGE DATABASE object_name;Here,
object_namespecifies 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_namespecifies 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_namespecifies the name of the index in the recycle bin. You cannot set this parameter to the original name of the index.For example:
obclient [(none)]> PURGE INDEX __recycle_$_100017_1673426335319344;Purge all objects from the recycle bin
obclient [(none)]> PURGE RECYCLEBIN;
After the statement is executed successfully, you can execute the
SHOW RECYCLEBINstatement again to check whether the objects in the recycle bin are purged.obclient [(none)]> SHOW RECYCLEBIN;
Automatically purge the recycle bin
OceanBase AI Database allows you to set the cluster-level configuration item recyclebin_object_expire_time to automatically purge expired schema objects from the recycle bin. The default value of this configuration item is 0s.
When the value is
0s, schema objects in the recycle bin are not automatically purged.When the value is not
0s, schema objects that have been stored in the recycle bin for the specified period are automatically purged.
For more information about the cluster-level configuration item recyclebin_object_expire_time, see recyclebin_object_expire_time.
Log in to the
systenant as therootuser.Here is an example of the connection command. Use the actual database connection information in your production environment.
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -AFor more information about how to connect to a database, see Overview of connection methods.
Execute the following statement to view the automatic purge strategy of the recycle bin:
obclient [(none)]> SHOW PARAMETERS LIKE 'recyclebin_object_expire_time';Here is an example of the query result:
+-------+----------+----------------+----------+-------------------------------+-----------+-------+--------------------------------------------------------------------------------------------------+--------------+---------+---------+-------------------+---------------+-----------+ | 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 setEnable 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';(Optional) After a period of time, 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: