查询oracle各个表所占空间大小

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) AS MB
from dba_segments t
where t.segment_type='TABLE' and (t.segment_name like '%yyy%' or t.segment_name like '%xxx%')
group by OWNER,t.segment_name, t.segment_type order by MB DESC

 

 

快速清空表数据保留表结构

truncate table xxx drop storage

 

查看表空间大小

SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

 

查看指定表空间各表的大小

SELECT *
FROM (SELECT BYTES, segment_name, segment_type, owner
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY BYTES DESC)
WHERE ROWNUM < 100;