OB V4如何查看非分区表的副本分布？请提供sql

OceanBase 技术问题
#1

【 使用环境 】生产环境 or 测试环境
【 OB or 其他组件 】OB
【 使用版本 】OB V4
【问题描述】 OB V4如何查看非分区表的副本分布？请提供sql @论坛小助手

3 个赞
#3 
SELECT ZONE,
       SVR_IP,
       ROLE,
       DATABASE_NAME,
       TABLE_NAME,
       TABLE_ID,
       TABLE_TYPE,
       TABLET_ID,
       TABLEGROUP_NAME
  FROM oceanbase.DBA_OB_TABLE_LOCATIONS
 WHERE 1 = 1
 -- AND ROLE = 'LEADER'
 AND TABLE_TYPE='USER TABLE'
 AND TABLE_NAME NOT IN(SELECT TABLE_NAME FROM oceanbase.DBA_PART_TABLES)
 ORDER BY DATABASE_NAME,TABLE_NAME;

+-------+--------------+----------+---------------+-----------------------------------------+----------+------------+-----------+-----------------+
| ZONE  | SVR_IP       | ROLE     | DATABASE_NAME | TABLE_NAME                              | TABLE_ID | TABLE_TYPE | TABLET_ID | TABLEGROUP_NAME |
+-------+--------------+----------+---------------+-----------------------------------------+----------+------------+-----------+-----------------+
| zone3 | 10.186.61.33 | FOLLOWER | test          | t1                                      |   500006 | USER TABLE |    200003 | NULL            |
| zone2 | 10.186.61.30 | FOLLOWER | test          | t1                                      |   500006 | USER TABLE |    200003 | NULL            |
| zone1 | 10.186.61.29 | LEADER   | test          | t1                                      |   500006 | USER TABLE |    200003 | NULL            |
| zone3 | 10.186.61.33 | FOLLOWER | __recyclebin  | __recycle_$_1757569992_1760080762131712 |   500004 | USER TABLE |    200002 | NULL            |
| zone2 | 10.186.61.30 | FOLLOWER | __recyclebin  | __recycle_$_1757569992_1760080762131712 |   500004 | USER TABLE |    200002 | NULL            |
| zone1 | 10.186.61.29 | LEADER   | __recyclebin  | __recycle_$_1757569992_1760080762131712 |   500004 | USER TABLE |    200002 | NULL            |
+-------+--------------+----------+---------------+-----------------------------------------+----------+------------+-----------+-----------------+
6 rows in set (0.055 sec)
3 个赞
#4

select
database_name,
table_name,
partition_name,
tablet_id,
ls_id,
zone,
svr_ip,
role
from
oceanbase.dba_ob_table_locations
where
database_name=‘test_db’ and table_name=‘np_t1’
order by
table_name,
partition_name,
role desc
;

2 个赞
#5

谢谢~

2 个赞
#6

// 根据表组 分组， 查询各个表组在各个节点上的主副本数量
SELECT tablegroup_name,svr_ip,count(1) FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE ROLE = ‘LEADER’
group by tablegroup_name,svr_ip;

// 查询指定数据库的表主副本所在节点
SELECT * FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE table_Type = ‘USER TABLE’ and ROLE = ‘LEADER’
and database_name =

4 个赞
#7

学习学习 :+1: :+1: :+1:

2 个赞
#8

多谢了~

2 个赞
#9

学习学习。 :+1:t2:

3 个赞
#10

查询这个视图

3 个赞
#11

OK

1 个赞