oracle查看表空间的方法

您所在的位置：网站首页 › oracle查看某张表的索引大小 › oracle查看表空间的方法

oracle查看表空间的方法

2024-07-09 17:14| 来源: 网络整理| 查看: 265

一、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;

【本文地址】

oracle查看表空间的方法

oracle查看表空间的方法

今日新闻

推荐新闻