*、前提:在项目开发到一定阶段时,可能会关注数据库的使用情况以及数据文件的大小,于是乎需要用到一下查询工具
*、查看表空间大小以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;