PURGE

Purpose

You can use this statement to purge the tables, indexes, or all objects from the recycle bin.

Syntax

PURGE  { {TABLE object_name  INDEX object_name } 
               RECYCLEBIN
              };

Parameters

Examples

Prepare the table and data used in the examples.

obclient> CREATE TABLE tbl1(col1 INT,col2 INT);
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(col1 INT,col2 INT);
Query OK, 0 rows affected

obclient> CREATE INDEX tbl1_idx1 ON tbl1(col1);
Query OK, 0 rows affected

obclient> CREATE INDEX tbl2_idx2 ON tbl2(col1);
Query OK, 0 rows affected

obclient> DROP TABLE tbl1;
Query OK, 0 rows affected

obclient> DROP TABLE tbl2;
Query OK, 0 rows affected

obclient> SHOW RECYCLEBIN;
+-----------------------------------+----------------------------------+-------+------------------------------+
 OBJECT_NAME                        ORIGINAL_NAME                     TYPE   CREATETIME                   
+-----------------------------------+----------------------------------+-------+------------------------------+
 RECYCLE_$_100004_1634626847891416  __idx_1101710651081601_TBL1_IDX1  INDEX  19-OCT-21 03.00.47.891270 PM 
 RECYCLE_$_100004_1634626847895656  TBL1                              TABLE  19-OCT-21 03.00.47.895431 PM 
 RECYCLE_$_100004_1634626853379432  __idx_1101710651081602_TBL2_IDX2  INDEX  19-OCT-21 03.00.53.378871 PM 
 RECYCLE_$_100004_1634626853383304  TBL2                              TABLE  19-OCT-21 03.00.53.383100 PM 
+-----------------------------------+----------------------------------+-------+------------------------------+
4 rows in set

1. Purge the indexes of the tbl1 table from the recycle bin.

   obclient> PURGE INDEX RECYCLE_$_100004_1634626847891416;
   Query OK, 0 rows affected
   
   obclient> SHOW RECYCLEBIN;
   +-----------------------------------+----------------------------------+-------+------------------------------+
    OBJECT_NAME                        ORIGINAL_NAME                     TYPE   CREATETIME                   
   +-----------------------------------+----------------------------------+-------+------------------------------+
    RECYCLE_$_100004_1634626847895656  TBL1                              TABLE  19-OCT-21 03.00.47.895431 PM 
    RECYCLE_$_100004_1634626853379432  __idx_1101710651081602_TBL2_IDX2  INDEX  19-OCT-21 03.00.53.378871 PM 
    RECYCLE_$_100004_1634626853383304  TBL2                              TABLE  19-OCT-21 03.00.53.383100 PM 
   +-----------------------------------+----------------------------------+-------+------------------------------+
   3 rows in set
   

2. Purge the tbl2 table from the recycle bin.

   obclient> PURGE TABLE RECYCLE_$_100004_1634626853383304;
   Query OK, 0 rows affected
   
   obclient> SHOW RECYCLEBIN;
   +-----------------------------------+---------------+-------+------------------------------+
    OBJECT_NAME                        ORIGINAL_NAME  TYPE   CREATETIME                   
   +-----------------------------------+---------------+-------+------------------------------+
    RECYCLE_$_100004_1634626847895656  TBL1           TABLE  19-OCT-21 03.00.47.895431 PM 
   +-----------------------------------+---------------+-------+------------------------------+
   1 row in set
   

3. Purge the recycle bin.

   obclient> PURGE RECYCLEBIN;
   Query OK, 0 rows affected
   
   obclient> SHOW RECYCLEBIN;
   Empty set