Tablespace usage
set lines 160
set pages 200
col tablespace_name format A30
col file_name format A55
col pct_used format 999.99
set verify off
set trimout on
select f.tablespace_name, f.file_name
, round(f.bytes/1024/1024) as size_mb
, round(f.maxbytes/1024/1024) as max_size_mb
from dba_data_files f
where f. tablespace_name like nvl('&&tablespace_name','%' )
union all
select f.tablespace_name, f.file_name
, round(f.bytes/1024/1024)
, round(f.maxbytes/1024/1024)
from dba_temp_files f
where f.tablespace_name like nvl('&&tablespace_name','%' )
order by 1,2 ;
undefine tablespace_name;
SET LINES 132
SET pages 200
col tablespace_name format A30
col cont format A4
col file_name format A55
col pct_used format 999.99
SET verify off
SET trimout ON
SELECT
t.tablespace_name ,
substr(t.contents,1,4) AS "Cont" ,
(df.totalspace - nvl(fs.freespace,0)) "Used MB",
nvl(fs.freespace,0) "Free MB",
df.totalspace "Total MB",
round(100 * (( df.totalspace - nvl(fs.freespace,0)) / greatest(1,df.totalspace))) "Pct. Used",
df.autoext AutoExtensible,
df.MaxSizeMb "Max Size Mb",
round(100 * ( (df.totalspace - nvl(fs.freespace,0)) / greatest(1,df.MaxSizeMb))) "Pct. Max used",
round((df.totalspace - nvl(fs.freespace,0))/0.9 - df.totalspace , 0) AS "Miss MB to 90%"
FROM
dba_tablespaces t,
(SELECT
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace,
round(sum(decode(autoextensible, 'YES', greatest(bytes, maxbytes), bytes))/1024/1024, 0) MaxSizeMb,
max (autoextensible) AutoExt
FROM
dba_data_files
GROUP BY
tablespace_name
UNION ALL
SELECT
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace,
round(sum(decode(autoextensible, 'YES', greatest(bytes, maxbytes), bytes))/1024/1024, 0) MaxSizeMb,
max (autoextensible) AutoExt
FROM
dba_temp_files
GROUP BY
tablespace_name
) df,
(SELECT
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
FROM
dba_free_space
GROUP BY
tablespace_name
) fs
WHERE
t.tablespace_name = df.tablespace_name
AND df.tablespace_name = fs.tablespace_name (+)
ORDER BY 9 DESC, df.tablespace_name
;