部署了OB4.3.1 版本，原来的运维脚本都不能用了。
简单的查询表的实际使用容量都查不到了。
没有使用新数据字典的常用运维脚本了吗？
可以先看看这个： 3.x 与 4.x 视图变更
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000218192
收到，多谢！
查一个表的容量都查不到，帮助文档也没说清楚。
我们要的是容量统计，给了一堆location，用途不大
dba_segments 这个用途挺大的
你这是Oracle租户的吧
用sys租户查还行，普通租户查起来是不方便
-- OB 4.x 查询租户级别空间占用
select coalesce(t1.tenant_id, -1) as tenant_id, tenant_name, sum(data_disk_in_use)/1024/1024 as data_disk_in_use_MB, sum(log_disk_in_use)/1024/1024 as log_disk_in_use_MB
from (select t1.unit_id, t1.svr_ip, t1.svr_port, t2.tenant_id, t1.data_disk_in_use, t1.log_disk_in_use
from ( select unit_id, svr_ip, svr_port, sum(data_disk_in_use) as data_disk_in_use, sum(log_disk_in_use) as log_disk_in_use
from gv$ob_units group by unit_id ) t1
join dba_ob_units t2 on t1.unit_id = t2.unit_id) t1
join dba_ob_tenants t2 on t1.tenant_id = t2.tenant_id
where tenant_type <>'meta' group by tenant_id;
-- OB 4.x 查询表级别数据大小与磁盘空间
SELECT
/*+READ_CONSISTENCY(WEAK),query_timeout(100000000)*/
c.tenant_name,
d.database_name,
-- a.svr_ip,
b.table_name,
e.num_rows,
round(sum(a.data_size) / 1024 / 1024 / 1024, 2) data_size_GB,
round(sum(a.required_size) / 1024 / 1024 / 1024, 2) required_size_GB
FROM
oceanbase.__all_virtual_tablet_meta_table a,
oceanbase.__all_virtual_table b,
oceanbase.__all_tenant c,
oceanbase.__all_virtual_database d,
oceanbase.cdb_tables e
WHERE
a.tenant_id = b.tenant_id
AND a.tablet_id = b.tablet_id
AND b.tenant_id = c.tenant_id
AND b.database_id = d.database_id
and b.tenant_id = e.con_id
and d.database_name = e.owner
and b.table_name = e.table_name
and c.tenant_name = 'test_tenant'
-- and c.tenant_type not in ('META','SYS')
-- and b.table_name like '%acc%'
group by
c.tenant_name,
b.table_name,
d.database_name
-- a.svr_ip
having
data_size_GB > 0
or required_size_GB > 0
ORDER BY
c.tenant_name,
d.database_name,
--a.svr_ip,
required_size_GB DESC;
1 个赞
666,我格式化了下，可以跑。
-- OB 4.x 查询租户级别空间占用
select
coalesce(t1.tenant_id, -1) as tenant_id,
tenant_name,
sum(data_disk_in_use) / 1024 / 1024 as data_disk_in_use_MB,
sum(log_disk_in_use) / 1024 / 1024 as log_disk_in_use_MB
from
(
select
t1.unit_id,
t1.svr_ip,
t1.svr_port,
t2.tenant_id,
t1.data_disk_in_use,
t1.log_disk_in_use
from
(
select
unit_id,
svr_ip,
svr_port,
sum(data_disk_in_use) as data_disk_in_use,
sum(log_disk_in_use) as log_disk_in_use
from
gv$ob_units
group by
unit_id
) t1
join dba_ob_units t2 on t1.unit_id = t2.unit_id
) t1
join dba_ob_tenants t2 on t1.tenant_id = t2.tenant_id
where
tenant_type <> 'meta'
group by
tenant_id;
-- OB 4.x 查询表级别数据大小与磁盘空间
SELECT
/*+ READ_CONSISTENCY(WEAK), query_timeout(100000000) */
c.tenant_name,
d.database_name,
-- a.svr_ip,
b.table_name,
e.num_rows,
round(sum(a.data_size) / 1024 / 1024 / 1024, 2) data_size_GB,
round(sum(a.required_size) / 1024 / 1024 / 1024, 2) required_size_GB
FROM
oceanbase.__all_virtual_tablet_meta_table a,
oceanbase.__all_virtual_table b,
oceanbase.__all_tenant c,
oceanbase.__all_virtual_database d,
oceanbase.cdb_tables e
WHERE
a.tenant_id = b.tenant_id
AND a.tablet_id = b.tablet_id
AND b.tenant_id = c.tenant_id
AND b.database_id = d.database_id
and b.tenant_id = e.con_id
and d.database_name = e.owner
and b.table_name = e.table_name
-- and c.tenant_name = 'test_tenant'
-- and c.tenant_type not in ('META', 'SYS')
-- and b.table_name like '% acc %'
group by
c.tenant_name,
b.table_name,
d.database_name
-- a.svr_ip
having
data_size_GB > 0
or required_size_GB > 0
ORDER BY
c.tenant_name,
d.database_name,
-- a.svr_ip,
required_size_GB DESC;
1 个赞
供参考
查看集群ID和集群名
show parameters like '%cluster%';
查看当前服务器信息
select * from DBA_OB_SERVERS;
查看集群的zone信息
SELECT * FROM dba_ob_zones;
查看集群支持的字符集
select * from information_schema.collations;
查看对应 Unit 的配置。
SELECT * FROM oceanbase.dba_ob_units;
查看租户对应的资源池（可以加 tenant_id 筛选）。
SELECT * FROM oceanbase.dba_ob_resource_pools;
查看租户基本信息。
SELECT * FROM oceanbase.dba_ob_tenants;
当前集群内的租户
select tenant_id,tenant_name,primary_zone,compatibility_mode from oceanbase.__all_tenant;
查看 RS 任务
select * from __all_rootservice_event_history order by 1 desc limit 10;
查看 rs 列表
show parameters like '%rootservice_list%';
查看 rs leader，WITH_ROOTSERVER=yes
SELECT * FROM oceanbase.DBA_OB_SERVERS;
服务器资源分配
select * from GV$OB_SERVERS;
各租户资源分配
select t1.name resource_pool_name, t2.`name` unit_config_name,
t2.max_cpu, t2.min_cpu,
round(t2.memory_size/1024/1024/1024,2) mem_size_gb,
round(t2.log_disk_size/1024/1024/1024,2) log_disk_size_gb, t2.max_iops,
t2.min_iops, t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,
t4.tenant_id, t4.tenant_name
from __all_resource_pool t1
join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id)
join __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`)
left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id)
order by t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id;
查看 observer 内存、磁盘配置大小
select zone,svr_ip,svr_port,name,value
from __all_virtual_sys_parameter_stat
where
name in ('memory_limit','memory_limit_percentage','system_memory','datafile_size','datafile_disk_percentage')
order by svr_ip,svr_port;
统计租户的大小
select t.tenant_name,
round(sum(t2.data_size)/1024/1024/1024,2) as data_size_gb,
round(sum(t2.required_size)/1024/1024/1024,2) as required_size_gb
from dba_ob_tenants t,cdb_ob_table_locations t1,cdb_ob_tablet_replicas t2
where t.tenant_id=t1.tenant_id
and t1.svr_ip=t2.svr_ip
and t1.tenant_id=t2.tenant_id
and t1.ls_id=t2.ls_id
and t1.tablet_id=t2.tablet_id
-- and t1.role='leader'
group by t.tenant_name
order by 3 desc;
统计库的大小
select t1.database_name,
round(sum(t2.data_size)/1024/1024/1024,2) as data_size_gb,
round(sum(t2.required_size)/1024/1024/1024,2) as required_size_gb
from dba_ob_tenants t,cdb_ob_table_locations t1,cdb_ob_tablet_replicas t2
where t.tenant_id=t1.tenant_id
and t1.svr_ip=t2.svr_ip
and t1.tenant_id=t2.tenant_id
and t1.ls_id=t2.ls_id
and t1.tablet_id=t2.tablet_id
-- and t1.role='leader'
and t.tenant_name='test1'
group by t1.database_name
order by 3 desc;
统计表/索引的大小
select t1.table_name,
round(sum(t2.data_size)/1024/1024/1024,2) as data_size_gb,
round(sum(t2.required_size)/1024/1024/1024,2) as required_size_gb
from dba_ob_tenants t,cdb_ob_table_locations t1,cdb_ob_tablet_replicas t2
where t.tenant_id=t1.tenant_id
and t1.svr_ip=t2.svr_ip
and t1.tenant_id=t2.tenant_id
and t1.ls_id=t2.ls_id
and t1.tablet_id=t2.tablet_id
-- and t1.role='leader'
and t.tenant_name='test1'
and t1.database_name='sbtest'
and t1.table_name='sbtest1'
group by t1.table_name
order by 3 desc;
统计表对应的分区大小
select t1.table_name,t1.partition_name,
round(sum(t2.data_size)/1024/1024/1024,2) as data_size_gb,
round(sum(t2.required_size)/1024/1024/1024,2) as required_size_gb
from dba_ob_tenants t,cdb_ob_table_locations t1,cdb_ob_tablet_replicas t2
where t.tenant_id=t1.tenant_id
and t1.svr_ip=t2.svr_ip
and t1.tenant_id=t2.tenant_id
and t1.ls_id=t2.ls_id
and t1.tablet_id=t2.tablet_id
and t1.role='leader'
and t.tenant_name='test1'
and t1.database_name='sbtest'
and t1.table_name='sbtest1_part'
group by t1.table_name,t1.partition_name;
租户内存持有：
select TENANT_ID,SVR_IP,SVR_PORT,HOLD/1024/1024/1024,FREE/1024/1024/1024
from oceanbase.GV$OB_TENANT_MEMORY
where tenant_id =1002;
租户内存模块占用：
select * from V$OB_MEMORY where tenant_id=1002;
memstore占用：
select * from V$OB_MEMSTORE where tenant_id=1002;
总体实际占用的memory 大小以及大小限制：
select * from oceanbase.GV$OB_SERVERS;
查看当前所有表详情
select * from __all_table
查看所有分区详情
select * from __all_part
查看表及分区leader分布
select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER' and table_name='xxx'
查看表及分区的预估数据量
select * from OCEANBASE.DBA_TAB_STATISTICS
日志流分布
select SVR_IP,ROLE,count(*) from CDB_OB_LS_LOCATIONS group by SVR_IP,ROLE;
查看日志流状态
select * from GV$OB_LOG_STAT;
查看日志流详情
select svr_ip,svr_port,tenant_id,ls_id,replica_type,ls_state,tablet_count from __all_virtual_ls_info;
3 个赞