一、Oracle查询表空间占用情况
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表占用空间大小",
ROUND((total - free) / total * 100, 2) || '%' "已使用空间百分比"
FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 free
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY (total - free) DESC;
二、Oracle查询表占用的空间
SELECT segment_name "表名",
segment_type "对象类型",
sum(bytes) / 1024 / 1024 "占用空间（MB）"
FROM dba_extents
WHERE segment_name = '表名'
GROUP BY segment_name, segment_type;
三、Oracle查询表空间使用情况
SELECT tablespace_name,
file_id,
bytes,
blocks,
maxbytes
FROM dba_data_files;
四、Oracle查询每张表占用空间
SELECT owner,
segment_name,
segment_type,
sum(bytes) / 1024 / 1024 "占用空间（MB）"
FROM dba_segments
WHERE owner = '表所有者'
GROUP BY owner, segment_name, segment_type;
五、Oracle表空间大小查询
SELECT tablespace_name,
SUM(bytes) / 1024 / 1024 "占用空间（MB）"
FROM dba_segments
WHERE segment_type IN ('TABLE', 'INDEX')
GROUP BY tablespace_name;
六、Oracle查表空间大小
SELECT tablespace_name,
SUM(bytes) / 1024 / 1024 "占用空间（MB）"
FROM dba_segments
WHERE tablespace_name = '表空间名'
GROUP BY tablespace_name;
七、Oracle查询表空间大小
SELECT tablespace_name,
sum(bytes) / 1024 / 1024 "占用空间（MB）"
FROM dba_data_files
GROUP BY tablespace_name;
八、查看Oracle各个表占空间
SELECT owner,
segment_name,
segment_type,
format_number(bytes / (1024 * 1024), 2) "已用空间（MB）",
format_number(maxbytes / (1024 * 1024), 2) "最大空间（MB）"
FROM dba_segments
WHERE owner = '表所有者'
ORDER BY bytes DESC;
select TABLE_NAME,
COLUMN_NAME,
SEGMENT_NAME
from dba_lobs
-- 对应的上面语句查到的segment_name名称，如SYS_LOB0000152921C00008$$
where SEGMENT_NAME='segment_name名称';
九、Oracle查看表空间大小
SELECT tablespace_name,
sum(blocks) * 8192 / 1024 / 1024 "表空间大小（MB）",
sum(blocks) * 8192 / 1024 / 1024 - sum(bytes) / 1024 / 1024 "剩余空间（MB）",
sum(bytes) / 1024 / 1024 "已用空间（MB）",
sum(MAXBYTES) / 1024 / 1024 "最大空间（MB）"
FROM (SELECT tablespace_name,
file_id,
MAX(blocks) MAXBLOCKS,
SUM(blocks) blocks
FROM dba_extents
GROUP BY tablespace_name, file_id),
(SELECT tablespace_name, file_id, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name, file_id)
WHERE tablespace_name NOT LIKE 'UNDO%'
AND tablespace_name NOT LIKE 'TEMP%'
AND tablespace_name NOT LIKE 'SYSAUX%'
AND tablespace_name NOT LIKE 'SYSTEM%'
AND tablespace_name NOT LIKE 'RMAN%'
AND tablespace_name NOT LIKE 'FLASH%'
GROUP BY tablespace_name;