Queries include automatic expansion and non-automatic expansion of tablespace usage
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.tablespaceroomnameauthorf.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
