oracle pmax自动扩展,查询包含自动扩展和不自动扩展表空间使用率

select tablespace_name,

max_gb,

used_gb,

round(100 * used_gb / max_gb) pct_used,

autoextensible

from (select a.tablespace_name tablespace_name,a.autoextensible,

round((a.bytes_alloc - nvl(b.bytes_free, 0)) / power(2, 30),  2) used_gb,

round(a.maxbytes / power(2, 30), 2) max_gb

from (select f.tablespace_name,

sum(f.bytes) bytes_alloc,

sum(decode(f.autoextensible, 'YES',f.maxbytes,  'NO',f.bytes)) maxbytes,

f.autoextensible

from dba_data_files f

group by tablespace_name,autoextensible) a,

(select f.tablespace_name, sum(f.bytes) bytes_free

from dba_free_space f

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

union all

select h.tablespace_name tablespace_name,f.autoextensible,

round(sum(nvl(p.bytes_used, 0)) / power(2, 30), 2) used_gb,

round(sum(decode(f.autoextensible, 'YES',  f.maxbytes, 'NO', f.bytes)) / power(2, 30), 2) max_gb

from v$temp_space_header h, v$temp_extent_pool p, dba_temp_files f

where p.file_id(+) = h.file_id

and p.tablespace_name(+) = h.tablespace_name

and f.file_id = h.file_id

and f.tablespace_name = h.tablespace_name

group by h.tablespace_name,f.autoextensible)

order by 4

select tablespace_name,

max_gb,

used_gb,

round(100 * used_gb / max_gb) pct_used,

autoextensible

from (select a.tablespace_name tablespace_name,

a.autoextensible,

round((a.bytes_alloc - nvl(b.bytes_free, 0)) / power(2, 30),

2) used_gb,

round(a.maxbytes / power(2, 30), 2) max_gb

from (select f.tablespace_name,

sum(f.bytes) bytes_alloc,

sum(decode(f.autoextensible,

'YES',

f.maxbytes,

'NO',

f.bytes)) maxbytes,

f.autoextensible

from dba_data_files f

group by tablespace_name, autoextensible) a,

(select f.tablespace_name, sum(f.bytes) bytes_free

from dba_free_space f

group by tablespace_name) b

where a.tablespace_name = b.tablespace_name(+)

union all

select h.tablespace_name tablespace_name,

f.autoextensible,

round(sum(nvl(p.bytes_used, 0)) / power(2, 30), 2) used_gb,

round(sum(decode(f.autoextensible,

'YES',

f.maxbytes,

'NO',

f.bytes)) / power(2, 30),

2) max_gb

from v$temp_space_header h, v$temp_extent_pool p, dba_temp_files f

where p.file_id(+) = h.file_id

and p.tablespace_name(+) = h.tablespace_name

and f.file_id = h.file_id

and f.tablespace_name = h.tablespace_name

group by h.tablespace_name, f.autoextensible)

order by 4