oracle空间统计

 

# 某个表空间大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments 
where segment_name=upper('tablename');


select * from user_segments where segment_name=upper('tablename');

 

# 库空间占用大小
select sum(bytes)/1024/1024 as "size(M)" from dba_segments where owner='schemaname'

 

# 查询某个表空间 , 存储空间最大的表
SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB 
FROM DBA_SEGMENTS 
WHERE TABLESPACE_NAME = upper('USERS') 
GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;

# 在某个账户中 最大的表
SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB 
FROM DBA_SEGMENTS WHERE owner=upper('SCHEMANAME') 
GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;