oracle之空间统计

*、前提：在项目开发到一定阶段时，可能会关注数据库的使用情况以及数据文件的大小，于是乎需要用到一下查询工具

 

*、查看表空间大小以M为单位

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_sizeM 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name 
GROUP BY t.tablespace_name; 

 

*、当前用户占用表空间大小

select sum(a) as sizem From (Select Sum(bytes)/1024/1024 as a From User_Extents Group By Segment_Name)

 

*、当前用户各个表占用空间大小

Select Segment_Name,Sum(bytes)/1024/1024 as sizeM From User_Extents Group By Segment_Name;

 

*、表空间物理文件大小即dbf文件大小

SELECT tablespace_name, 
file_id, 
file_name, 
round(bytes / (1024 * 1024), 0) total_space 
FROM dba_data_files 
ORDER BY tablespace_name; 

 

*、各个表空间的使用率

select tablespace_name,
       round(used_space*(select value from v$parameter where name='db_block_size')/power(2,30),2) USED_GB,
       round(tablespace_size*(select value from v$parameter where name='db_block_size')/power(2,30)) MAXSIZE_GB,
       round(used_percent,2) as "PCT%" 
from dba_tablespace_usage_metrics;