SELECT
segment_name AS table_name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
AND segment_name = UPPER('你的表名')
GROUP BY segment_name;
SELECT
owner,
segment_name AS table_name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE'
AND owner = UPPER('用户名')
GROUP BY owner, segment_name
ORDER BY size_mb DESC;
⚠ 分区表不能只查 TABLE，要查 TABLE PARTITION
SELECT
segment_name AS table_name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS total_size_mb
FROM dba_segments
WHERE segment_type IN ('TABLE PARTITION')
AND segment_name = UPPER('分区表名')
GROUP BY segment_name;
SELECT
segment_name AS table_name,
partition_name,
ROUND(bytes / 1024 / 1024, 2) AS size_mb
FROM dba_segments
WHERE segment_type = 'TABLE PARTITION'
AND segment_name = UPPER('分区表名')
ORDER BY partition_name;
SELECT
segment_name,
partition_name,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM dba_segments
WHERE segment_type IN ('TABLE PARTITION','TABLE SUBPARTITION')
AND segment_name = UPPER('表名')
GROUP BY segment_name, partition_name
ORDER BY partition_name;
一个表真实占用空间 =
表 + 分区 + 索引 + 索引分区 + LOB
SELECT
owner,
segment_name,
segment_type,
ROUND(SUM(bytes) / 1024 / 1024, 2) AS size_mb
FROM dba_segments
WHERE owner = UPPER('用户名')
AND segment_name = UPPER('表名')
GROUP BY owner, segment_name, segment_type
ORDER BY size_mb DESC;
如果没有 DBA_SEGMENTS 权限，用：
SELECT
segment_name,
ROUND(SUM(bytes)/1024/1024,2) size_mb
FROM user_segments
GROUP BY segment_name
ORDER BY size_mb DESC;
SELECT partitioned
FROM dba_tables
WHERE table_name = UPPER('表名');
返回：
YES = 分区表
NO = 普通表
SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name = UPPER('表名');
SELECT
tablespace_name,
ROUND(SUM(bytes)/1024/1024,2) size_mb
FROM dba_segments
GROUP BY tablespace_name
ORDER BY size_mb DESC;
先判断是否分区表
查分区大小
查索引是否占用过大
查LOB是否异常
查表空间是否异常增长
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。