分区表管理的视图有哪些

[jeffrey75]

oceabase 4.2.1 针对分区表管理的视图有哪些，比如查看分区状况，分区表内数据量等等

[淇铭]

租户下所有表数据量大小
SELECT /*+ query_timeout(30000000) */ a.TENANT_ID, a.DATABASE_NAME,
a.TABLE_NAME, a.TABLE_ID,
sum(case when b.nested_offset = 0 then IFNULL(b.data_block_count+b.index_block_count+b.linked_block_count, 0) * 2 *
1024 * 1024 else IFNULL(b.size, 0) end) /1024.0/1024/1024 as data_size_in_GB
FROM oceanbase.CDB_OB_TABLE_LOCATIONS a inner join oceanbase.__all_virtual_table_mgr b on a.svr_ip = b.svr_ip and a.svr_port=b.svr_port and a.tenant_id = b.tenant_id and a.LS_ID = b.LS_ID and a.TABLET_ID = b.TABLET_ID and a.role =‘LEADER’ and a.tenant_id = ${租户ID}
and b.table_type >= 10 and b.size > 0 group by a.TABLE_ID;

查看 test 库下 t1 表 Leader 分布

SELECT * FROM oceanbase.DBA_OB_TABLE_LOCATIONS
WHERE DATABASE_NAME=‘test’ and TABLE_NAME=‘t1’ and ROLE=‘LEADER’ and TABLE_TYPE=‘USER TABLE’;

partition分布

SELECT svr_ip, count(1) FROM oceanbase.cdb_ob_table_locations
WHERE tenant_id = 1002 group by svr_ip order by svr_ip;