Table recycle bin

Retention period and cleanup mechanism

After a table enters the recycle bin, it is retained for three days by default and automatically deleted once the retention period expires. You can customize the retention period by configuring RECYCLE_BIN_EXPIRED_TIME.

• Automatic cleanup after the retention period expires: Tables in the recycle bin that exceed the retention period (RECYCLE_BIN_EXPIRED_TIME) are automatically deleted.

• Automatic cleanup when the table count limit is reached: In V3.2.3.6, V3.2.4.3, V3.2.5.0, and later versions, if the number of tables has already reached the upper limit at the time of table creation, the system automatically deletes the table in the recycle bin that is closest to expiration. One table must be deleted before a new table can be created.

  Note

  Which table does the recycle bin automatically delete?

  Suppose the recycle bin contains one table that expires tomorrow and another that expires the day after tomorrow. The table that expires tomorrow is deleted first.

• Manual cleanup: You can manually run PURGE to clean up the recycle bin. For details, see Delete tables from the recycle bin.

Naming convention

Tables from different databases are stored in a single recycle bin. To ensure unique names, each table is renamed using the format SourceDatabaseName_SourceTableName_Timestamp.

Example: A customer table from the adb_demo database, dropped on January 1, 2024 at 00:00:00, is renamed adb_demo_customer_1704038400000.

Change the retention period

SET adb_config RECYCLE_BIN_EXPIRED_TIME=<RetentionPeriodInMilliseconds>;

Example: Set the retention period to two days:

-- 2 days = 2 × 24 × 3600 × 1000 = 172,800,000 ms
SET adb_config RECYCLE_BIN_EXPIRED_TIME=172800000;

Syntax

-- List all tables in the recycle bin
SHOW RECYCLE_BIN ALL;

-- List tables from a specific source database and table name
SHOW RECYCLE_BIN TABLE <SourceDatabaseName>.<SourceTableName>;

-- List tables by source table name (across all databases)
SHOW RECYCLE_BIN TABLE <SourceTableName>;

-- List all tables from a specific source database
SHOW RECYCLE_BIN DATABASE <SourceDatabaseName>;

Examples

List all tables in the recycle bin:

SHOW RECYCLE_BIN ALL;

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
| testUser | ADB_RECYCLE_BIN | adb_demo_orders_1720086094102   | adb_demo         | orders          | 2024-07-04 17:41:37 | 2024-07-07 17:41:37 |
| testUser | ADB_RECYCLE_BIN | testdb_customer_1720085752664   | testdb           | customer        | 2024-07-04 17:35:56 | 2024-07-07 17:35:56 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

List a specific table from `adb_demo.customer`:

SHOW RECYCLE_BIN TABLE adb_demo.customer;

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

List all versions of the `customer` table across all databases:

SHOW RECYCLE_BIN TABLE customer;

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
| testUser | ADB_RECYCLE_BIN | testdb_customer_1720085752664   | testdb           | customer        | 2024-07-04 17:35:56 | 2024-07-07 17:35:56 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

List all tables from the `adb_demo` database:

SHOW RECYCLE_BIN DATABASE adb_demo;

+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| userName | schemaName      | tableName                       | originSchemaName | originTableName | recycledTime        | expiredTime         |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+
| testUser | ADB_RECYCLE_BIN | adb_demo_customer_1720086117277 | adb_demo         | customer        | 2024-07-04 17:42:00 | 2024-07-07 17:42:00 |
| testUser | ADB_RECYCLE_BIN | adb_demo_orders_1720086094102   | adb_demo         | orders          | 2024-07-04 17:41:37 | 2024-07-07 17:41:37 |
+----------+-----------------+---------------------------------+------------------+-----------------+---------------------+---------------------+

Restore rules

• Restored tables use their original names from before they were dropped.

• If the source database already contains a table with the same name, rename, delete, or move that existing table before restoring.

• If multiple versions of a table exist in the recycle bin (same source database and table name, different timestamps), RESTORE RECYCLE_BIN ALL restores the version with the latest timestamp and reports an error for the remaining versions.

Syntax

-- Restore all tables to their source databases
RESTORE RECYCLE_BIN ALL;

-- Restore a specific table using its recycle bin name
RESTORE RECYCLE_BIN TABLE <RecycleBinTableName>;

The <RecycleBinTableName> is the name shown in the tableName column of SHOW RECYCLE_BIN output (format: SourceDatabaseName_SourceTableName_Timestamp).

Restore a specific version of a table

When multiple versions of a dropped table exist in the recycle bin, use the following steps to restore a specific version:

1. Query the recycle bin to find all versions and their recycle bin names:

   SHOW RECYCLE_BIN TABLE <SourceTableName>;

2. Identify the target version by comparing the recycledTime column with the timestamp suffix in tableName.

3. Restore the specific version using its recycle bin name:

   RESTORE RECYCLE_BIN TABLE <RecycleBinTableName>;

Example: To restore the customer table as it existed before the drop on 2024-07-04 17:42:00:

RESTORE RECYCLE_BIN TABLE adb_demo_customer_1720086117277;

Syntax

-- Permanently delete all tables from the recycle bin
PURGE RECYCLE_BIN ALL;

-- Permanently delete a specific table from the recycle bin
PURGE RECYCLE_BIN TABLE <RecycleBinTableName>;

Example

Permanently delete the adb_demo_customer_1720086117277 table:

PURGE RECYCLE_BIN TABLE adb_demo_customer_1720086117277;