Restore objects from the recycle bin|V4.4.2| docs|Distributed Database

Restore objects from the recycle bin

Last Updated：2026-03-06 07:02:41 Updated

You can execute the FLASHBACK statement to restore objects from the recycle bin.

Considerations

The order of flashing back objects must comply with their dependency. A database is flashed back before tables in the database.
You cannot directly restore an index. When you execute the FLASHBACK statement to restore a table, indexes on the table are restored together with the table.
You can modify the name of an object when you restore it from the recycle bin, but the object name must be different from an existing one. Otherwise, the system returns an error.
For a MySQL-compatible tenant, before restoring a table from the recycle bin, you must restore the database to which the table belongs if the database has been deleted.
If a table in the recycle bin originally belongs to a table group, it is restored to the original table group by default. If the original table group has been deleted, the restored table does not belong to any table group.

Restore tenants

You can delete tenants only in the sys tenant. Therefore, you can restore tenants from the recycle bin only in the sys tenant. You can modify the name of a tenant when you restore it from the recycle bin, but the tenant name must be different from an existing one. Otherwise, the system returns an error.

Log in to the database as the sys tenant.

Below is a connection example. Use your actual environment when connecting to the database.
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
```
For more detailed guidance on connecting to the database, see Overview of connection methods (MySQL-compatible mode) and Overview of connection methods (Oracle-compatible mode).

Execute the SHOW RECYCLEBIN statement to obtain the names of objects in the recycle bin.

obclient [(none)]> SHOW RECYCLEBIN;
+-------------------------------------+-------------------+----------+----------------------------+
| OBJECT_NAME                         | ORIGINAL_NAME     | TYPE     | CREATETIME                 |
+-------------------------------------+-------------------+----------+----------------------------+
| RECYCLE_$_100017_1672050541224936   | oracle001         | 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 set

Select an appropriate scenario based on your business needs to restore a tenant from the recycle bin.
- Restore a tenant in the recycle bin to a regular tenant with the tenant name before it was placed in the recycle bin.
  
  The syntax is as follows:
```
 FLASHBACK TENANT tenant_name TO BEFORE DROP;
```
  Here, tenant_name specifies the name of the tenant in the recycle bin or the original name of the tenant. The name in the recycle bin is globally unique and therefore can accurately specify the tenant to be restored. We recommend that you use the name in the recycle bin to restore the tenant. If you use the original name and multiple tenants in the recycle bin have the same original name, the last tenant moved to the recycle bin is restored.
  
  For example:
```
 obclient [(none)]> FLASHBACK TENANT RECYCLE_$_100017_1672050541224936 TO BEFORE DROP;
 Query OK, 0 rows affected
```
- Restore a tenant in the recycle bin to a regular tenant and rename it.
```
 FLASHBACK TENANT tenant_name TO BEFORE DROP RENAME TO new_tenant_name;
```
  where
  - tenant_name can be the name in the recycle bin or the original name of the tenant. Because the name in the recycle bin is globally unique, using it can clearly specify which tenant to restore. We recommend that you use the name in the recycle bin to restore the tenant. If you use the original name and multiple tenants in the recycle bin have the same original name, the tenant that was last moved to the recycle bin is restored.
  - new_tenant_name specifies the new name of the tenant after it is restored from the recycle bin.
  For example:
```
 obclient [(none)]> FLASHBACK TENANT RECYCLE_$_100017_1672050541224936 TO BEFORE DROP RENAME TO new_oracle;
 Query OK, 0 rows affected
```

Restore databases

You can restore databases from the recycle bin in the sys tenant or a MySQL user tenant. You can modify the name of a database when you restore it from the recycle bin, but the database name must be different from an existing one. Otherwise, the system returns an error.

Log in to the database as a tenant administrator of the sys tenant or a MySQL-compatible user tenant.

Note

The default administrator user of a MySQL-compatible tenant is root.

Below is a connection example. Use your actual environment when connecting to the database.
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
```
For more detailed guidance on connecting to the database, see Overview of connection methods (MySQL-compatible mode) and Overview of connection methods (Oracle-compatible mode).

Execute the SHOW RECYCLEBIN statement to obtain the names of objects in the recycle bin.

obclient [(none)]> SHOW RECYCLEBIN;
+-------------------------------------+-------------------+----------+----------------------------+
| OBJECT_NAME                         | ORIGINAL_NAME     | TYPE     | CREATETIME                 |
+-------------------------------------+-------------------+----------+----------------------------+
| __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 |
+-------------------------------------+-------------------+----------+----------------------------+
3 rows in set

Select an appropriate scenario based on your business needs to restore a database from the recycle bin.
- Restore a database from the recycle bin with the database name before it was placed in the recycle bin.
```
 FLASHBACK DATABASE object_name TO BEFORE DROP;
```
  Here, object_name specifies the name of the database object in the recycle bin. The original name is not supported.
  
  Example:
```
 obclient [(none)]> FLASHBACK DATABASE __recycle_$_100017_1673854707660504 TO BEFORE DROP;
 Query OK, 0 rows affected
```
  After you execute this statement, if the database to be restored has the same name as an existing database in the tenant, the system returns an error.
```
obclient [(none)]> FLASHBACK DATABASE __recycle_$_100017_1673854707660504 TO BEFORE DROP;
ERROR 1007 (HY000): Can't create database 'infotest'; database exists
```
- Restore a database from the recycle bin and rename it.
```
 FLASHBACK DATABASE object_name TO BEFORE DROP RENAME TO new_database_name;
```
  Where:
  - object_name specifies the name of the database object in the recycle bin. The original name is not supported.
  - new_database_name specifies the name of the database after it is restored from the recycle bin.
  Example:
```
 obclient [(none)]> FLASHBACK DATABASE __recycle_$_100017_1673854707660504 TO BEFORE DROP RENAME TO new_infotest;
 Query OK, 0 rows affected
```

Restore tables

You can restore tables from the recycle bin in the sys tenant, a MySQL user tenant, or an Oracle user tenant. You can modify the name of a table when you restore it from the recycle bin, but the table name must be different from an existing one. Otherwise, the system returns an error.

Log in to the database as a tenant administrator of the sys tenant or a user tenant.

Note

The default administrator user of a MySQL-compatible tenant is root, and that of an Oracle-compatible tenant is SYS.

Below is a connection example. Use your actual environment when connecting to the database.
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
```
For more detailed guidance on connecting to the database, see Overview of connection methods (MySQL-compatible mode) and Overview of connection methods (Oracle-compatible mode).

Execute the SHOW RECYCLEBIN statement to obtain the names of objects in the recycle bin.

obclient [(none)]> SHOW RECYCLEBIN;
+-------------------------------------+-------------------+----------+----------------------------+
| OBJECT_NAME                         | ORIGINAL_NAME     | TYPE     | CREATETIME                 |
+-------------------------------------+-------------------+----------+----------------------------+
| __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 |
+-------------------------------------+-------------------+----------+----------------------------+
3 rows in set

Select an appropriate scenario based on your business needs to restore a table from the recycle bin.
- Restore a table from the recycle bin with the table name before it was placed in the recycle bin.
```
 FLASHBACK TABLE object_name TO BEFORE DROP;
```
  Here, object_name specifies the name of the table in the recycle bin or the original name of the table. The name in the recycle bin is globally unique. Therefore, we recommend that you use the name in the recycle bin.
  
  For example:
```
 obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP;
 Query OK, 0 rows affected
```
  After you execute this statement, the restored table has the name it had before it was placed in the recycle bin, and the database or schema to which the table belongs remains the same as before the table was deleted. If the table name before it was placed in the recycle bin is the same as an existing table, the system returns an error.
- Restore a table from the recycle bin and rename it.
```
 FLASHBACK TABLE object_name TO BEFORE DROP RENAME To new_table_name;
```
  Here, object_name specifies the name of the table in the recycle bin or the original name of the table. The name in the recycle bin is globally unique. Therefore, we recommend that you use the name in the recycle bin. new_table_name specifies the new name of the table after it is restored from the recycle bin.
  
  For example:
```
 obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP RENAME To infotable;
 Query OK, 0 rows affected
```
  After you execute this statement, the restored table has the renamed table name, and the database or schema to which the table belongs remains the same as before the table was deleted. If the renamed table name is the same as an existing table, the system returns an error.
- Restore a table from the recycle bin to a specified database or schema and rename it.
```
 FLASHBACK TABLE object_name TO BEFORE DROP RENAME To database_name.new_table_name;
```
  Here, object_name specifies the name of the table in the recycle bin or the original name of the table. The name in the recycle bin is globally unique. Therefore, we recommend that you use the name in the recycle bin. database_name.new_table_name specifies the new name of the table after it is restored from the recycle bin, as well as the name of the database to which the table belongs. Schema object names of Oracle user tenants are the same as usernames.
  
  For example:
```
 obclient [(none)]> FLASHBACK TABLE __recycle_$_100017_1673426335331800 TO BEFORE DROP RENAME To test.infotable;
 Query OK, 0 rows affected
```
  After you execute this statement, the restored table has the renamed table name, and the database or schema to which the table belongs is the specified one. If the renamed table name is the same as an existing table, the system returns an error.

References

For more information about the recycle bin, see the following:

Restore objects from the recycle bin

Considerations

Restore tenants

Restore databases

Note

Restore tables

Note

References