我如何才能知道，被删除的t1表，原本属于哪个database ？

OceanBase 技术问题
#1

图片
图片756×80 31.5 KB

我如何才能知道，被删除的t1表，原本属于哪个database ？

1 个赞
#3

可以用这个DBA_OB_TABLE_LOCATIONS表 根据表名查询信息

2 个赞
#4 
-- 创建一张aaa测试表
obclient(root@obmysql)[demo]> create table demo.aaa(id int);
Query OK, 0 rows affected (0.064 sec)

-- 查看表的基础信息
obclient(root@obmysql)[demo]> select DATABASE_NAME,TABLE_NAME,TABLE_ID,TABLE_TYPE,TABLET_ID,OBJECT_ID from oceanbase.DBA_OB_TABLE_LOCATIONS where TABLE_NAME='aaa';
+---------------+------------+----------+------------+-----------+-----------+
| DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | TABLET_ID | OBJECT_ID |
+---------------+------------+----------+------------+-----------+-----------+
| demo          | aaa        |   500022 | USER TABLE |    200017 |    500022 |
+---------------+------------+----------+------------+-----------+-----------+
1 row in set (0.008 sec)

-- 删除这张表（已经提前开启了回收站）
obclient(root@obmysql)[demo]> drop table demo.aaa;
Query OK, 0 rows affected (0.063 sec)


-- 直接查看回收站只能看原来的表名称，看不到所示数据库
obclient(root@obmysql)[demo]> show recyclebin;
+-----------------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME                             | ORIGINAL_NAME | TYPE  | CREATETIME                 |
+-----------------------------------------+---------------+-------+----------------------------+
| __recycle_$_1757569994_1770357564445576 | aaa           | TABLE | 2026-02-06 13:59:24.445549 |
+-----------------------------------------+---------------+-------+----------------------------+

-- 关联多张表，获取回收站表的所有信息
SELECT DISTINCT T1.TABLE_ID,
                T1.DATABASE_NAME,   
                T1.TABLE_NAME,
                T4.database_name as ORIGINAL_DATABASE_NAME,
                T3.ORIGINAL_NAME AS ORIGINAL_TABLE_NAME,
                T1.TABLE_TYPE,
                T2.DATA_SIZE,
                T2.REQUIRED_SIZE
  FROM oceanbase.DBA_OB_TABLE_LOCATIONS T1,
       oceanbase.DBA_OB_TABLET_REPLICAS T2,
       oceanbase.__ALL_RECYCLEBIN       T3,
       oceanbase.__all_database T4
 WHERE 1 = 1
   AND T1.TABLET_ID = T2.TABLET_ID
   AND T1.TABLE_ID = T3.TABLE_ID
   AND T3.database_id=t4.database_id
   AND t1.DATABASE_NAME = '__recyclebin'
   AND t3.ORIGINAL_NAME='aaa';

-- 结果中可以基于TABLE_ID及database_id关联到删除前的原有库表信息
*************************** 1. row ***************************
              TABLE_ID: 500022
         DATABASE_NAME: __recyclebin
            TABLE_NAME: __recycle_$_1757569994_1770357564445576
ORIGINAL_DATABASE_NAME: demo
   ORIGINAL_TABLE_NAME: aaa
            TABLE_TYPE: USER TABLE
             DATA_SIZE: 0
         REQUIRED_SIZE: 0
1 row in set (0.072 sec)
#5

学习了，被删除的表也在这个视图里查么？

#6

purge可以通过这个__all_database_history 查看一下 没有purge在回收站里可以DBA_OB_TABLE_LOCATIONS这个视图查看一下