Description
You can query the sizes of data of different versions contained in major SSTables.
Statement
OceanBase Database of a version earlier than V4.0.0:
SELECT m1.svr_ip, m1.svr_port, sum(m1.size)/1024/1024/1024 data_size_gb
FROM __all_virtual_table_mgr m1
JOIN (
SELECT svr_ip, svr_port, `tenant_id`, table_id, partition_id, index_id, max(multi_version_start) max_version, count(*) cnt
FROM __all_virtual_table_mgr
WHERE table_type=1
GROUP BY svr_ip,svr_port,table_id,partition_id,index_id
HAVING cnt>1 ) m2
ON m1.svr_ip=m2.svr_ip AND m1.svr_port=m2.svr_port AND m1.tenant_id=m2.tenant_id AND m1.table_id=m2.table_id AND m1.partition_id=m2.partition_id AND m1.index_id=m2.index_id
WHERE m1.table_type=1 AND m1.multi_version_start!=m2.max_version
GROUP BY m1.svr_ip, m1.svr_port
OceanBase Database V4.0.0 and later:
SELECT m1.svr_ip, m1.svr_port, sum(m1.size)/1024/1024/1024 data_size_gb
FROM __all_virtual_table_mgr m1
JOIN (
SELECT svr_ip, svr_port, `tenant_id`, ls_id, tablet_id, max(end_log_scn) max_version, count(*) cnt
FROM __all_virtual_table_mgr
WHERE table_type=10
GROUP BY svr_ip, svr_port, `tenant_id`, ls_id, tablet_id
HAVING cnt>1 ) m2
ON m1.svr_ip=m2.svr_ip AND m1.svr_port=m2.svr_port AND m1.tenant_id=m2.tenant_id AND m1.ls_id=m2.ls_id AND m1.tablet_id=m2.tablet_id
WHERE m1.table_type=10 AND m1.end_log_scn!=m2.max_version
GROUP BY m1.svr_ip, m1.svr_port
Possible impact
For more information, see Troubleshoot issues related to temporary files or How to query the disk space occupied by temporary files in OceanBase Database.