掌握高效SQL查询技巧，提升数据检索效率

在Oracle数据库中，查询表占用空间的方法有多种。以下是三种常用的方法：

方法一：使用DBA_SEGMENTS视图

DBA_SEGMENTS视图包含了数据库中所有段（segment）的信息，包括表、索引、LOB等。通过查询该视图，可以获取特定表或所有表占用的空间大小。

查询单个表占用的空间大小：


SELECT segment_name AS "表名",
       segment_type AS "对象类型",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM dba_segments
WHERE segment_name = '表名'
GROUP BY segment_name, segment_type;
SELECT segment_name AS "表名",
       segment_type AS "对象类型",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM dba_segments
WHERE segment_name = '表名'
GROUP BY segment_name, segment_type;
SELECT segment_name AS "表名",
       segment_type AS "对象类型",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM dba_segments
WHERE segment_name = '表名'
GROUP BY segment_name, segment_type;

将'表名'替换为你要查询的表的名称。

查询所有表占用的空间大小（按表空间分组）：


SELECT tablespace_name AS "表空间名",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM dba_segments
WHERE segment_type IN ('TABLE', 'INDEX')
GROUP BY tablespace_name;
SELECT tablespace_name AS "表空间名",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM dba_segments
WHERE segment_type IN ('TABLE', 'INDEX')
GROUP BY tablespace_name;
SELECT tablespace_name AS "表空间名",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM dba_segments
WHERE segment_type IN ('TABLE', 'INDEX')
GROUP BY tablespace_name;

方法二：使用USER_SEGMENTS视图

USER_SEGMENTS视图是DBA_SEGMENTS视图的子集，只包含当前用户拥有的段的信息。因此，它更适合用于查询当前用户下的表占用的空间大小。

查询当前用户下单个表占用的空间大小：


SELECT segment_name AS "表名",
       segment_type AS "对象类型",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM user_segments
WHERE segment_name = '表名'
GROUP BY segment_name, segment_type;
SELECT segment_name AS "表名",
       segment_type AS "对象类型",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM user_segments
WHERE segment_name = '表名'
GROUP BY segment_name, segment_type;
SELECT segment_name AS "表名",
       segment_type AS "对象类型",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM user_segments
WHERE segment_name = '表名'
GROUP BY segment_name, segment_type;

查询当前用户下所有表占用的空间大小：


SELECT segment_name AS "表名",
       segment_type AS "对象类型",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM user_segments
WHERE segment_type = 'TABLE'
GROUP BY segment_name, segment_type
ORDER BY SUM(bytes) DESC;
SELECT segment_name AS "表名",
       segment_type AS "对象类型",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM user_segments
WHERE segment_type = 'TABLE'
GROUP BY segment_name, segment_type
ORDER BY SUM(bytes) DESC;
SELECT segment_name AS "表名",
       segment_type AS "对象类型",
       SUM(bytes) / 1024 / 1024 AS "占用空间(MB)"
FROM user_segments
WHERE segment_type = 'TABLE'
GROUP BY segment_name, segment_type
ORDER BY SUM(bytes) DESC;

方法三：使用DBA_DATA_FILES和DBA_FREE_SPACE视图结合查询

这种方法相对复杂一些，但可以得到更详细的信息，包括表空间的总大小、已使用大小和剩余大小等。

首先，需要分别查询DBA_DATA_FILES和DBA_FREE_SPACE视图，然后将结果结合起来计算已使用空间。

查询表空间的总大小和剩余大小：


SELECT a.tablespace_name AS "表空间名",
       ROUND(SUM(a.bytes) / 1024 / 1024, 2) AS "表空间总大小(MB)",
       ROUND(SUM(b.bytes) / 1024 / 1024, 2) AS "表空间剩余大小(MB)",
       ROUND(SUM(a.bytes) / 1024 / 1024 - SUM(b.bytes) / 1024 / 1024, 2) AS "表占用空间大小(MB)",
       ROUND((SUM(a.bytes) - SUM(b.bytes)) / SUM(a.bytes) * 100, 2) || '%' AS "已使用空间百分比"
FROM (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_data_files GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
GROUP BY a.tablespace_name
ORDER BY "已使用空间百分比" DESC;
SELECT a.tablespace_name AS "表空间名",
       ROUND(SUM(a.bytes) / 1024 / 1024, 2) AS "表空间总大小(MB)",
       ROUND(SUM(b.bytes) / 1024 / 1024, 2) AS "表空间剩余大小(MB)",
       ROUND(SUM(a.bytes) / 1024 / 1024 - SUM(b.bytes) / 1024 / 1024, 2) AS "表占用空间大小(MB)",
       ROUND((SUM(a.bytes) - SUM(b.bytes)) / SUM(a.bytes) * 100, 2) || '%' AS "已使用空间百分比"
FROM (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_data_files GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
GROUP BY a.tablespace_name
ORDER BY "已使用空间百分比" DESC;
SELECT a.tablespace_name AS "表空间名",
       ROUND(SUM(a.bytes) / 1024 / 1024, 2) AS "表空间总大小(MB)",
       ROUND(SUM(b.bytes) / 1024 / 1024, 2) AS "表空间剩余大小(MB)",
       ROUND(SUM(a.bytes) / 1024 / 1024 - SUM(b.bytes) / 1024 / 1024, 2) AS "表占用空间大小(MB)",
       ROUND((SUM(a.bytes) - SUM(b.bytes)) / SUM(a.bytes) * 100, 2) || '%' AS "已使用空间百分比"
FROM (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_data_files GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) AS bytes FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
GROUP BY a.tablespace_name
ORDER BY "已使用空间百分比" DESC;