Tablespace usage

set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;


set lines 500
 set pages 500
 column ts format a8
 column tablespace_name format a30
 column allocated format 999,999,999
 column unallocated format 999,999,999
 column used format 999,999,999
 column allocated_free format 999,999,999
 column total_free format 999,999,999
 column allocated_used_pct format 990.00
 column total_used_pct format 990.00
 select 'TS' as TS,
        ts.tablespace_name,
        allocs.allocated allocated,
        allocs.unallocated unallocated,
        trunc((allocs.allocated - trunc(free.MB))/(allocated+unallocated)*100,2) total_used_pct
 from dba_tablespaces ts
 inner join
 (select tablespace_name, sum(allocated) allocated, sum(unallocated) unallocated
 from
    (select tablespace_name, sum(bytes)/1024/1024 allocated, sum(maxbytes-bytes)/1024/1024 unallocated
    from dba_data_files
    where autoextensible = 'YES'
    group by tablespace_name
    union
    select tablespace_name, sum(bytes)/1024/1024 allocated, 0
    from dba_data_files
    where autoextensible = 'NO'
    group by tablespace_name) inner_allocs
 group by tablespace_name) allocs
 on ts.tablespace_name = allocs.tablespace_name
 left join (select tablespace_name, sum(bytes)/1024/1024 MB
            from dba_free_space
            group by tablespace_name) free
 on ts.tablespace_name = free.tablespace_name
 where ts.contents = 'PERMANENT'
order by 5 desc;





set lines 150
column file_name format a80
column MB format 999,999,999
column MAXMB format 999,999,999
select file_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 MAXMB
from dba_data_files
where tablespace_name = upper('&ts_name');


col tablespace format A16
 SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
Order by 4;

 set lines 152
 col FreeSpaceGB format 999.999
 col UsedSpaceGB format 999.999
 col TotalSpaceGB format 999.999
 col host_name format a30
 col tablespace_name format a30
 select tablespace_name,
  (free_blocks*8)/1024/1024 FreeSpaceGB,
  (used_blocks*8)/1024/1024 UsedSpaceGB,
  (total_blocks*8)/1024/1024 TotalSpaceGB,
  i.instance_name,i.host_name
  from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
  i.inst_id=ss.inst_id;