当前位置:我的异常网» 数据库 » oracle8、查看每个表空间总大小,闲空多少,空闲百
oracle8、查看每个表空间总大小,闲空多少,空闲百分比,使用百分比
www.myexceptions.net 网友分享于:2015-08-24 浏览:0次
oracle8、查看每个表空间总大小,空闲多少,空闲百分比,使用百分比
1、查看每个表占用空间大小1
select segment_name,sum(bytes)/1024/1024 from user_extents group by segment_name;
2、查看每个表占用空间大小2
select segment_name,sum(bytes)/1024/1024 from user_segments where segment_type='TABLE' group by segment_name;
3、查看用户表,索引,分区表占用空间
select segment_name,sum(bytes)/1024/1024 from user_segments group by segment_name;
4、查看每索引占用空间大小
select segment_name,sum(bytes)/1024/1024 from user_segments where segment_type='INDEX' group by segment_name;
5、查看每个表占用空间大小,按大小排序,只显示最前面9条
select * from (select segment_name,sum(bytes)/1024/1024 coun from user_extents group by segment_name order by coun desc) where rownum<10;
6、查看每个表空间占用大小
select tablespace_name,sum(bytes)/1024/1024 from dba_segments group by tablespace_name;
7、查看某个表空间下每个表占用空间大小
select blocks*8192/1024/1024,table_name from dba_tables where tablespace_name='CBS_LOG_DAT' order by blocks desc;
8、查看每个表空间总大小,空闲多少,空闲百分比,使用百分比
SELECT DF.TABLESPACE_NAME "Tablespace",
DF.BYTES / (1024 * 1024) "Total Size(MB)",
SUM(FS.BYTES) / (1024 * 1024) "Free Size(MB)",
ROUND(SUM(FS.BYTES) * 100 / DF.BYTES) "% Free",
ROUND((DF.BYTES - SUM(FS.BYTES)) * 100 / DF.BYTES) "% Used"
FROM DBA_FREE_SPACE FS,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) DF
WHERE FS.TABLESPACE_NAME = DF.TABLESPACE_NAME
GROUP BY DF.TABLESPACE_NAME, DF.BYTES;
文章评论