Understanding OceanBase Recycle Bin: Retrieval, Multiple Same‑Name Tables, Visibility, and Flashback Behavior

Phenomenon

When using show recyclebin to view flashback tables, an error “object not in recycle bin” may appear.

Reason

During restoration the current database is used as the parent object; if the table belongs to another database, the database.table format must be specified.

Questions Raised

How to obtain the database of a table in the recycle bin?

Can the recycle bin store multiple tables with the same name, and which one is restored?

Is the object visible after the recycle bin is disabled?

Is the recycle bin visible to all tenants or only the current tenant?

Can tables be flashbacked after the recycle bin is disabled?

Can tenants be flashbacked after the recycle bin is disabled?

Tests

1. Retrieve database name of a table in the recycle bin:

MySQL [oceanbase]> create table test.a(i int) ;
Query OK, 0 rows affected (0.05 sec)
MySQL [oceanbase]> set session recyclebin=1 ;
Query OK, 0 rows affected (0.00 sec)
MySQL [oceanbase]> drop table test.a ;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> show recyclebin ;
+-----------------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME                             | ORIGINAL_NAME | TYPE  | CREATETIME                 |
+-----------------------------------------+---------------+-------+----------------------------+
| __recycle_$_1677212890_1680257357905408 | a             | TABLE | 2023-03-31 18:09:17.904933 |
+-----------------------------------------+---------------+-------+----------------------------+
MySQL [oceanbase]> select rb.tenant_id, rb.database_id, db.database_name, rb.table_id,
    rb.tablegroup_id, rb.original_name
    from __all_recyclebin rb
    inner join __all_virtual_database db
    on rb.database_id=db.database_id;
+-----------+---------------+---------------+---------------+---------------+---------------+
| tenant_id | database_id   | database_name | table_id      | tablegroup_id | original_name |
+-----------+---------------+---------------+---------------+---------------+---------------+
|         1 | 1099511628776 | test          | 1099511677793 |            -1 | a             |
+-----------+---------------+---------------+---------------+---------------+---------------+

2. Multiple same‑name tables in recycle bin:

MySQL [oceanbase]> create table test.a(i int) ;
... (first drop) ...
MySQL [oceanbase]> create table test.a(i int) ; insert into test.a values(1);
... (second drop) ...
MySQL [oceanbase]> show recyclebin ;
+-----------------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME                             | ORIGINAL_NAME | TYPE  | CREATETIME                 |
+-----------------------------------------+---------------+-------+----------------------------+
| __recycle_$_1677212890_1680258454351360 | a             | TABLE | 2023-03-31 18:27:34.351415 |
| __recycle_$_1677212890_1680258454423040 | a             | TABLE | 2023-03-31 18:27:34.422931 |
+-----------------------------------------+---------------+-------+----------------------------+
MySQL [oceanbase]> flashback table test.a to before drop ;
Query OK, 0 rows affected (0.02 sec)
... the latest dropped table is restored ...

3. Visibility after disabling recycle bin:

MySQL [oceanbase]> set session recyclebin=0 ;
Query OK, 0 rows affected (0.00 sec)
MySQL [oceanbase]> show recyclebin ;
+-----------------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME                             | ORIGINAL_NAME | TYPE  | CREATETIME                 |
+-----------------------------------------+---------------+-------+----------------------------+
| __recycle_$_1677212890_1680259040929280 | a             | TABLE | 2023-03-31 18:37:20.928638 |
+-----------------------------------------+---------------+-------+----------------------------+

4. Tenant visibility:

[root@ob-70 ~]# mysql -h10.186.63.134 -uroot@t1#oceanb_test_zhn -P2883 -c -A -e "create table test.tb1(i int);set session recyclebin=1;drop table test.tb1;show recyclebin;purge recyclebin;"
... shows __recycle_ entry for tb1 ...
[other tenant]# mysql -h10.186.63.134 -uroot@sys#'oceanb_test_zhn' -P2883 -c -p'aaAA__12' -A -e "show recyclebin;"
... does not see the entry ...

5. Flashback table after disabling recycle bin:

MySQL [oceanbase]> set session recyclebin=0;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> flashback table test.a to before drop ;
Query OK, 0 rows affected (0.02 sec)

6. Flashback tenant after disabling recycle bin:

MySQL [oceanbase]> set session recyclebin=1;
... drop tenant t1 ...
MySQL [oceanbase]> set session recyclebin=0;
MySQL [oceanbase]> flashback tenant t1 to before drop ;
Query OK, 0 rows affected (0.02 sec)

Conclusions

Enable the recycle bin to keep dropped objects; otherwise they are not stored.

Objects remain visible and operable (flashback/purge) even when the recycle bin is turned off.

The recycle bin can store multiple objects with the same name; flashback restores the most recently deleted one.

Recycle‑bin entries are tenant‑scoped; other tenants cannot see them.

Both tables and tenants can be flashbacked after the recycle bin is disabled.

Summary

Deleting objects requires the recycle bin to be enabled; otherwise the objects are not stored. Even with the recycle bin disabled, objects can still be listed and flashbacked, and multiple same‑name objects are kept, with the latest deletion taking precedence during restoration.