oracle表空间

查询表空间使用情况
1、查看数据文件名称和位置
SELECT tablespace_name, 
file_id, 
file_name, 
round(bytes / (1024 * 1024), 0) total_space_MB
FROM dba_data_files 
ORDER BY tablespace_name; 

2、查看当前数据文件中各表占用大小
select OWNER, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) as 单位M
 from dba_segments t
 where t.owner = 'PERFDBA' 
 and t.segment_type='TABLE'
 group by OWNER, t.segment_name, t.segment_type
 order by 单位M desc;


3、查看表空间使用情况
SELECT a.tablespace_name "表空间名", 
total "表空间大小", 
free "表空间剩余大小", 
(total - free) "表空间使用大小", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name