All Products
Search

This Product

    PolarDB:Restore a table from the table recycle bin

    Document Center

    PolarDB:Restore a table from the table recycle bin

    Last Updated:Mar 28, 2026

    Data definition language (DDL) operations like DROP TABLE cannot be rolled back. If a table is accidentally deleted, PolarDB's table recycle bin lets you recover it without restoring a full backup or creating a new cluster.

    Prerequisites

    Before you begin, make sure that:

    • Your cluster runs PolarDB for MySQL 8.0 with revision version 8.0.1.1.2 or later.

    • The table recycle bin was enabled before the table was deleted. To enable it, set the loose_recycle_bin parameter to ON. For instructions, see Configure cluster and node parameters.

    • The time elapsed since the deletion is within the retention window. The loose_recycle_bin_retention parameter sets the maximum retention period in seconds. For parameter configuration instructions, see Configure cluster and node parameters.

    For more information about the table recycle bin feature, see Table recycle bin.

    Restore a table

    Step 1: View tables in the recycle bin.

    Run the following statement to list all tables currently held in the recycle bin:

    CALL DBMS_RECYCLE.show_tables();

    Sample output:

    +-----------------+---------------+---------------+--------------+---------------------+---------------------+
| SCHEMA          | TABLE         | ORIGIN_SCHEMA | ORIGIN_TABLE | RECYCLED_TIME       | PURGE_TIME          |
+-----------------+---------------+---------------+--------------+---------------------+---------------------+
| __recycle_bin__ | __innodb_1063 | product_db    | t1           | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 |
| __recycle_bin__ | __innodb_1064 | product_db    | t2           | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 |
| __recycle_bin__ | __innodb_1065 | product_db    | parent       | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 |
| __recycle_bin__ | __innodb_1066 | product_db    | child        | 2019-08-08 11:01:46 | 2019-08-15 11:01:46 |
+-----------------+---------------+---------------+--------------+---------------------+---------------------+
4 rows in set (0.00 sec)
    ColumnDescription
    SCHEMAThe schema of the table recycle bin.
    TABLEThe name assigned to the table after it was moved to the recycle bin. Use this value as the RECYCLE_TABLE argument in Step 2.
    ORIGIN_SCHEMAThe original schema of the table before it was deleted.
    ORIGIN_TABLEThe original table name.
    RECYCLED_TIMEThe time when the table was moved to the recycle bin.
    PURGE_TIMEThe estimated time when the table will be permanently deleted from the recycle bin.

    Step 2: Restore the table.

    Run the following statement to restore the table:

    CALL DBMS_RECYCLE.restore_table('RECYCLE_TABLE', 'DEST_DB', 'DEST_TABLE');

    Replace the placeholders with actual values:

    PlaceholderDescription
    RECYCLE_TABLEThe value from the TABLE column in the show_tables() output (for example, __innodb_1063). If you specify only this parameter, the table is restored to its original database and original name.
    DEST_DBThe destination database to restore the table into.
    DEST_TABLEThe name to assign to the restored table.

    Example:

    CALL dbms_recycle.restore_table('__innodb_1063', 'testDB', 'testTable');
    The restore_table statement requires revision version 8.0.1.1.12 or later. To check your cluster's revision version, see the "Query the engine version" section of Engine versions.