Show Tablespace Check

EBS R12 Tablespace Check

REM $Header: ADZDSHOWTS.sql 120.4.12020000.2 2012/11/30 13:45:05 rmajeed ship $
REM dbdrv: none
REM +=======================================================================+
REM | Copyright (c) 2011 Oracle Corporation Redwood Shores, California, USA |
REM | All rights reserved.                                                  |
REM +=======================================================================+
REM | FILENAME
REM | ADZDSHOWTS.sql
REM |
REM | DESCRIPTION
REM |    This scipt is used to display the status of important tablespaces
REM |
REM | HISTORY
REM |   04-May-2011  SVEDI   Initial check-in
REM |
REM +=======================================================================+

SET FEEDBACK OFF;
SET ECHO OFF;

WHENEVER OSERROR EXIT FAILURE ROLLBACK;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

REM
REM  Spool results to adzdshowts.out file.
REM
spool adzdshowts.out

set linesize 200
set pagesize 2000

COLUMN file_name   FORMAT A90
COLUMN object_type FORMAT A20
COLUMN total_space FORMAT 9999.99 heading "TOTAL_SPACE (GB)"
COLUMN used_space  FORMAT 9999.99 heading "USED_SPACE (GB)"
COLUMN free_space  FORMAT 9999.99 heading "FREE_SPACE (GB)"
COLUMN pct_used    FORMAT 9999.99

prompt =========================================================================
prompt =                      Important Tablespace Status
prompt =========================================================================
SELECT
    df.tablespace_name
  , round((df.total_space/(1024*1024*1024)),2) total_space
  , round(((df.total_space - fs.free_space)/(1024*1024*1024)),2) used_space
  , round((fs.free_space/(1024*1024*1024)),2) free_space
  , round(((df.total_space-fs.free_space) / df.total_space)*100, 2) PCT_USED
FROM
    ( SELECT tablespace_name, SUM(bytes) TOTAL_SPACE
      FROM dba_data_files
      GROUP BY tablespace_name) df
  , ( SELECT tablespace_name, SUM(bytes) FREE_SPACE
      FROM dba_free_space
      GROUP BY tablespace_name) fs
--WHERE
   -- df.tablespace_name = fs.tablespace_name(+)
-- and df.tablespace_name in
 --   ('SYSTEM', 'APPS_TS_SEED', 'APPS_TS_TX_DATA', 'APPS_TS_TX_IDX')
ORDER BY fs.tablespace_name
/