PURGE

Purpose

This statement is used to delete database objects from the recycle bin. It supports deleting tenants, databases, tables, and indexes.

Syntax

 PURGE {TENANT | DATABASE | TABLE | INDEX} object_name

object_name:
    tenant_name
  | database_name
  | table_name
  | index_name

Parameters

Examples

• Delete the tenant mysql from the recycle bin.

  View the objects in the recycle bin:

  obclient> SHOW RECYCLEBIN;
  

  The query result is as follows:

  +--------------------------------+---------------+--------+----------------------------+
  | OBJECT_NAME                    | ORIGINAL_NAME | TYPE   | CREATETIME                 |
  +--------------------------------+---------------+--------+----------------------------+
  | __recycle_$_1_1599722585571328 | mysql         | TENANT | 2020-09-10 15:36:54.712101 |
  +--------------------------------+---------------+--------+----------------------------+
  1 row in set
  

  Delete the tenant from the recycle bin:

  obclient> PURGE TENANT mysql;
  

  View the recycle bin again:

  obclient> SHOW RECYCLEBIN;
  

  The query result is as follows:

  Empty set
  

• Delete the database __recycle_$_1_1597384386029184 from the recycle bin.

  Create and delete the database:

  obclient> CREATE DATABASE db1;
  
  obclient> DROP DATABASE db1;
  

  View the recycle bin:

  obclient> SHOW RECYCLEBIN;
  

  The query result is as follows:

  +--------------------------------+---------------+----------+----------------------------+
  | OBJECT_NAME                    | ORIGINAL_NAME | TYPE     | CREATETIME                 |
  +--------------------------------+---------------+----------+----------------------------+
  | __recycle_$_1_1597384386029184 | db1           | DATABASE | 2020-08-14 13:53:06.029367 |
  +--------------------------------+---------------+----------+----------------------------+
  1 row in set
  

  Delete the database from the recycle bin:

  obclient> PURGE DATABASE __recycle_$_1_1597384386029184;
  

  View the recycle bin again:

  obclient> SHOW RECYCLEBIN;
  

  The query result is as follows:

  Empty set
  

• Delete a table from the recycle bin.

  You can use the original table name or the object name in the recycle bin to delete the table. The following example shows how to delete a table using the original table name:

  Create and delete the table:

  obclient> CREATE TABLE test(c1 INT);
  
  obclient> DROP TABLE test;
  

  View the recycle bin:

  obclient> SHOW RECYCLEBIN;
  

  The query result is as follows:

  +-------------------------------------------+---------------+-------+----------------------------+
  | OBJECT_NAME                               | ORIGINAL_NAME | TYPE  | CREATETIME                 |
  +-------------------------------------------+---------------+-------+----------------------------+
  | __recycle_$_1_1099511628776_1099511677778 | test          | TABLE | 2017-10-20 17:40:22.304025 |
  +-------------------------------------------+---------------+-------+----------------------------+
  1 row in set
  

  Delete the table from the recycle bin using the original table name:

  obclient> PURGE TABLE test;
  

  Or delete the table using the object name in the recycle bin:

  obclient> PURGE TABLE __recycle_$_1_1099511628776_1099511677778;
  

  View the recycle bin again:

  obclient> SHOW RECYCLEBIN;
  

  The query result is as follows:

  Empty set
  

• Delete an index from the recycle bin.

  You can use the original index name or the object name in the recycle bin to delete the index. The following example shows how to delete an index using the original index name:

  Create a table and an index, and then delete the table:

  obclient> CREATE TABLE t1(c1 INT);
  
  obclient> CREATE INDEX idx ON t1(c1);
  
  obclient> DROP TABLE t1;
  

  View the recycle bin:

  obclient> SHOW RECYCLEBIN;
  

  The query result is as follows:

  +--------------------------------+----------------------------+-------+----------------------------+
  | OBJECT_NAME                    | ORIGINAL_NAME              | TYPE  | CREATETIME                 |
  +--------------------------------+----------------------------+-------+----------------------------+
  | __recycle_$_1_1597387726700872 | __idx_1101710651081557_idx | INDEX | 2020-08-14 14:48:46.699145 |
  | __recycle_$_1_1597387726712976 | t1                         | TABLE | 2020-08-14 14:48:46.712643 |
  +--------------------------------+----------------------------+-------+----------------------------+
  2 rows in set
  

  Delete the index from the recycle bin using the original index name:

  obclient> PURGE INDEX idx;
  

  Or delete the index using the object name in the recycle bin:

  obclient> PURGE INDEX __recycle_$_1_1597387726700872;
  

  View the recycle bin again:

  obclient> SHOW RECYCLEBIN;
  

  The query result is as follows:

  +--------------------------------+----------------------------+-------+----------------------------+
  | OBJECT_NAME                    | ORIGINAL_NAME              | TYPE  | CREATETIME                 |
  +--------------------------------+----------------------------+-------+----------------------------+
  | __recycle_$_1_1597387726712976 | t1                         | TABLE | 2020-08-14 14:48:46.712643 |
  +--------------------------------+----------------------------+-------+----------------------------+
  1 rows in set