set head off pagesize 0 linesize 500
(select dbname from v$database) || ',' || tablespace_name || ',' || round(current_usage_gb) space_used_gb || ',' || round(current_free_gb) || ',' || round(maxgb)
from
(
with tbsname as
(
select tablespace_name from dba_tablespaces
),
auto_ex_no as
(
select tablespace_name,bytes maxbytes
from
(
select tablespace_name
, sum(bytes) bytes
from dba_data_files a
where autoextensible='NO'
group by tablespace_name
)
),
auto_ex_yes as
(
select tablespace_name,bytes maxbytes
from
(
select tablespace_name,
sum(maxbytes) bytes
from dba_data_files a
where autoextensible='YES'
group by tablespace_name
)
),
auto_ex_temp_no as
(
select tablespace_name,bytes maxbytes
from
(
select tablespace_name,
sum(bytes) bytes
from dba_temp_files a
where autoextensible='NO'
group by tablespace_name
)
),
auto_ex_temp_yes as
(
select tablespace_name,bytes maxbytes
from
(
select tablespace_name,
sum(maxbytes) bytes
from dba_temp_files a
where autoextensible='YES'
group by tablespace_name
)
)
,
real_tablespac_usage as
(
select tablespace_name,
sum(bytes) bytes
from dba_segments
group by tablespace_name
)
select
tbs.tablespace_name,
case
when tbs.tablespace_name='TEMP' THEN
((round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0)+coalesce(ant.maxbytes,0)+coalesce(ayt.maxbytes,0),3)))/1073741824
when tbs.tablespace_name='SYSTEM' THEN
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0),3))/1073741824
when tbs.tablespace_name='SYSAUX' THEN
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0),3))/1073741824
when tbs.tablespace_name like 'UNDO%' THEN
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0),3))/1073741824
ELSE
(round((coalesce(an.maxbytes,0)+coalesce(ay.maxbytes,0)+coalesce(ant.maxbytes,0)+coalesce(ayt.maxbytes,0)-coalesce(tu.bytes,0)),3))/1073741824
END AS current_usage_gb,
(round((coalesce(tu.bytes,0)),3))/1073741824 current_free_gb,
(round(coalesce(ay.maxbytes,0)+coalesce(an.maxbytes,0)+coalesce(ant.maxbytes,0)+coalesce(ayt.maxbytes,0),3))/1073741824 maxgb
from tbsname tbs
full join auto_ex_yes ay
on tbs.tablespace_name=ay.tablespace_name
full join auto_ex_no an
on tbs.tablespace_name=an.tablespace_name
full join auto_ex_temp_no ant
on tbs.tablespace_name=ant.tablespace_name
full join auto_ex_temp_yes ayt
on tbs.tablespace_name=ayt.tablespace_name
full join real_tablespac_usage tu
on tbs.tablespace_name=tu.tablespace_name
);