check_oracle_tablespace.sql

SET pagesize 30
set linesize 300
column "Tablespace" format a13
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999
column "Tablespace path" format a50
ttitle center 'Application tablespace' skip 2
SELECT fs.tablespace_name "Tablespace",
  (df.totalspace - fs.freespace) "Used MB",
  fs.freespace "Free MB",
  df.totalspace "Total MB",
  ROUND(100 * (fs.freespace / df.totalspace)) "Pct. Free",
  (SELECT DISTINCT SUBSTR(file_name, 0, INSTR(file_name, '\', -1)) tablespace_file_path
     FROM dba_data_files WHERE dba_data_files.tablespace_name = fs.tablespace_name) "Tablespace path"
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace
  FROM dba_data_files
  GROUP BY tablespace_name
  ) df,
  (SELECT tablespace_name,
    ROUND(SUM(bytes) / 1048576) FreeSpace
  FROM dba_free_space
  GROUP BY tablespace_name
  ) fs
WHERE df.tablespace_name = fs.tablespace_name
ORDER BY fs.tablespace_name;

SET pagesize 30
set linesize 300
column "Tablespace" format a13
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999
column "Tablespace path" format a50
ttitle center 'System tablespace available' skip 2
SELECT A.tablespace_name Tablespace,
  SUM (A.used_blocks * D.block_size) / 1024 / 1024 "Used MB",
  D.mb_total         - SUM (A.used_blocks * D.block_size) / 1024 / 1024 "Free MB",
  D.mb_total "Total MB"
FROM v$sort_segment A,
  (SELECT B.name,
    C.block_size,
    SUM (C.bytes) / 1024 / 1024 mb_total
  FROM v$tablespace B,
    v$tempfile C
  WHERE B.ts#= C.ts#
  GROUP BY B.name,
    C.block_size
  ) D
WHERE A.tablespace_name = D.name
GROUP BY A.tablespace_name,
  D.mb_total;
  
  
SET pagesize 30
set linesize 300
column "Tablespace" format a13
column "Tablespace path" format a50
ttitle center 'System tablespace path' skip 2
SELECT v$tablespace.name as Tablespace, tablespace_file_path "Tablespace path" FROM 
(SELECT DISTINCT TS#, SUBSTR(name, 0, INSTR(name, '\', -1)) tablespace_file_path
     FROM v$tempfile) tempfile
     JOIN v$tablespace ON v$tablespace.TS# = tempfile.TS#;
  
EXIT;