Oracle查询表空间大小

查询表空间大小

select TABLESPACE_NAME 表空间名称,

SUM_SPACE 表空间总大小,

USED_SPACE 已用空间大小,

USED_RATE 使用率,

FREE_SPACE 剩余空间大小

From (Select d.tablespace_name,

Space Sum_space,

Blocks sum_blocks,

Space - nvl(free_space, 0) Used_space,

round((1 - nvl(free_space, 0) / Space) * 100, 2) Used_rate,

free_space Free_space

From (Select tablespace_name,

round(Sum(bytes) / (1024 * 1024), 2) Space,

Sum(blocks) Blocks

From dba_data_files

Group By tablespace_name) d,

(Select tablespace_name,

round(Sum(bytes) / (1024 * 1024), 2) free_space

From dba_free_space

Group By tablespace_name) f

Where d.tablespace_name = f.tablespace_name(+)

Union All

Select d.tablespace_name,

Space Sum_space,

blocks sum_blocks,

used_space Used_space,

round(nvl(used_space, 0) / Space * 100, 2) Used_rate,

nvl(free_space, 0) Free_space

From (Select tablespace_name,

round(Sum(bytes) / (1024 * 1024), 2) Space,

Sum(blocks) blocks

From dba_temp_files

Group By tablespace_name) d,

(Select tablespace_name,

round(Sum(bytes_used) / (1024 * 1024), 2) used_space,

round(Sum(bytes_free) / (1024 * 1024), 2) free_space

From v$temp_space_header

Group By tablespace_name) f

Where d.tablespace_name = f.tablespace_name(+))

order by Used_rate desc