///////////////////////////////////////////////
-- statement for tablespace management #1
///////////////////////////////////////////////
 
SET linesize 121
 
SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
  SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
  0 TOTAL_MB, 0 MAX_MB
  FROM dba_free_space
  GROUP BY tablespace_name
  UNION
  SELECT tablespace_name, 0 CURRENT_MB,
  SUM(bytes)/1024/1024 TOTAL_MB,
  SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
  FROM dba_data_files
  GROUP BY tablespace_name)
GROUP BY tablespace_name;
 
 
///////////////////////////////////////////////
-- statement for tablespace management #2
///////////////////////////////////////////////
 
col tablespace_name format a15
col alloc_size format 999.999
col pct_used format 999.999
col free_space format 999.999
col maxnext format 999.999
col definitsz format 999.999
col defnextsz format 999.999
 
SELECT a.tablespace_name, a.datafile_sz, b.alloc_size,
(b.alloc_size)/a.datafile_sz*100 PCT_USED,
(a.datafile_sz-b.alloc_size) FREE_SPACE,
b.next_extent/1024/1024 MAXNEXT,
a.initial_extent/1024/1024 DEFINITSZ,
a.next_extent/1024/1024 DEFNEXTSZ
FROM (
  SELECT a.tablespace_name, SUM(b.bytes)/1024/1024
  DATAFILE_SZ, a.initial_extent, a.next_extent
  FROM dba_tablespaces a, dba_data_files b
  WHERE a.tablespace_name = b.tablespace_name
  GROUP BY a.tablespace_name, a.initial_extent, a.next_extent) A,
    (
  SELECT a.tablespace_name, SUM(c.bytes)/1024/1024
  ALLOC_SIZE, MAX(c.next_extent) NEXT_EXTENT
  FROM dba_tablespaces a, dba_segments c
  WHERE a.tablespace_name = c.tablespace_name
  GROUP BY a.tablespace_name) B
WHERE a.tablespace_name = b.tablespace_name (+)
ORDER BY 1;
 
 
///////////////////////////////////////////////
-- statement for tablespace management #3
///////////////////////////////////////////////
SELECT dfs.tablespace_name, ddf.total_size,
ddf.total_size - dfs.total_free TOTAL_USED,
dfs.total_free,
(ddf.total_size-dfs.total_free)/ddf.total_size * 100 CAP,
dfs.total_chunks, dfs.largest_chunk
FROM (
  SELECT a.tablespace_name,
  SUM(a.bytes)/1024/1024 TOTAL_FREE,
  COUNT(a.bytes) TOTAL_CHUNKS,
  MAX(a.bytes)/1024/1024 LARGEST_CHUNK
  FROM dba_free_space a
  GROUP BY a.tablespace_name) dfs,
    (
  SELECT b.tablespace_name,
  SUM(b.bytes)/1024/1024 TOTAL_SIZE
  FROM dba_data_files b
  GROUP BY b.tablespace_name) ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name;