1: --查询表空间使用情况
2: select upper(f.tablespace_name) "表空间名",
3: d.tot_grootte_mb "表空间大小(m)",
4: d.tot_grootte_mb - f.total_bytes "已使用空间(m)",
5: to_char(round(( d.tot_grootte_mb - f.total_bytes ) / d.tot_grootte_mb * 100, 2), '990.99')
6: || '%' "使用比",
7: f.total_bytes "空闲空间(m)",
8: f.max_bytes "最大块(m)",
9: d.autoextensible "是否自增长",
10: d.incrementsize "自增长大小(m)"
11: from (select tablespace_name,
12: round(sum(bytes) / ( 1024 * 1024 ), 2) total_bytes,
13: round(max(bytes) / ( 1024 * 1024 ), 2) max_bytes
14: from sys.dba_free_space
15: group by tablespace_name) f,
16: (select dd.tablespace_name,
17: round(sum(dd.bytes) / ( 1024 * 1024 ), 2) tot_grootte_mb,
18: autoextensible,
19: --increment_by的单位是数据块的大小。数据块大小一般为默认8k
20: round(increment_by * (select value/1024 from v$parameter where name='db_block_size')/1024, 2) incrementsize
21: from sys.dba_data_files dd
22: group by dd.tablespace_name,autoextensible,increment_by) d
23: where d.tablespace_name = f.tablespace_name
24: order by "表空间名"
显示结果如下：
参考：