Tablespace usage

tablespace files.sql

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;

tablespace usage.sql

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
;