查看表空间和表的使用率

1.   查看表空间使用率

--查询表空间的总容量

select tablespace_name,sum(bytes) /1024/1024 as MB from dba_data_files group by tablespace_name;

 

 

--查询表空间使用率

select total.tablespace_name,
       round(total.MB, 2) as Total_MB,
       round(total.MB - free.MB, 2) as Used_MB,
       round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from
       (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
       (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
       where free.tablespace_name = total.tablespace_name order by used_pct desc;

 

--对象类型：表   分区表   索引   分区索引共四种，我们统计一下不同种类对象各多少size

 select segment_type,sum(bytes)/1024/1024 total_size from dba_segments where wner='CCARE' group by segment_type;

 

--SQL计算出某个用户下所有对象的大小

select owner,segment_name,segment_type,partition_name,bytes from dba_segments where wner='CCARE';

 

--我们经常使用的LEO1表空间使用情况已经perfect显示，现在我们需要统计的是LEO1表空间包含对象的大小，由于记录数较多，只把SQL语句写在这里了

 

 select o.object_name,o.object_type,o.owner,s.tablespace_name,s.segment_name,s.bytes from dba_objects o,dba_segments s where s.tablespace_name='LEO1' and o.owner=s.owner and o.owner='LEO1' and o.object_type=s.segment_type;