--  FILE:   tsmap9i.sql
--
--  AUTHOR: Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
--  DATE:   05/06/2004
--
--  DESCRIPTION:
--          Query to display tablespace space.
--
--          
--  REQUIREMENTS:
--          Requires Oracle9i or greater.
--
--          SELECT access to the following SYS views:
--		dba_data_files
--              dba_temp_files
--		dba_free_space
--              dba_tablespaces
--		
--  MODIFICATIONS:
--    11/03/2004, AR, Removed references to filext$ and added dba_temp_files.
--
--
-- 
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
-- 
--
SET LINESIZE 132;
SET SERVEROUTPUT on;
--
PROMPT Tablespace Space Map ;
PROMPT ;
COLUMN tablespace_name    HEADING 'Tablespace'                 FORMAT A30    TRUNCATE;
COLUMN status             HEADING 'Status'                     FORMAT A6;
COLUMN alloc              HEADING 'Alloc(MB)'                  FORMAT 9,999,999,999;
COLUMN maxfr              HEADING 'Max Contig|Alloc Free(MB)'  FORMAT 9,999,999,999;
COLUMN totfr              HEADING 'Total Free|Alloc(MB)'       FORMAT 9,999,999,999;
COLUMN xtnd               HEADING 'Extends|To(MB)'             FORMAT 9,999,999,999;
COLUMN pctfr              HEADING 'Pct Free|Spc Avail'         FORMAT 990.99;
--
SELECT data_files.tablespace_name, 
       ts.status,
       NVL(ROUND(tot_alloc_byt/1024/1024,2),0) alloc,
       NVL(ROUND(max_free_byt/1024/1024,2),0) maxfr,
       NVL(ROUND(tot_free_byt/1024/1024,2),0) totfr,
       NVL(ROUND( ( (NVL(tot_extnd_blk,0) * ts.block_size) + NVL(tot_alloc_byt,0) )/1024/1024,2 ),0) xtnd,
       NVL(ROUND( ( (( NVL(tot_free_byt,0) + DECODE( tot_extnd_blk,NULL,0,(tot_extnd_blk * ts.block_size) ) ) 
                      / DECODE( tot_extnd_blk,NULL,NVL(tot_alloc_byt,0), 
                                NVL(tot_alloc_byt,0) + (tot_extnd_blk * ts.block_size) ))
                  * 100 ), 2),0) pctfr
  FROM ( SELECT tablespace_name,
                SUM(bytes) tot_alloc_byt
           FROM sys.dba_data_files
          GROUP BY tablespace_name
          UNION ALL
         SELECT tablespace_name,
                SUM(bytes) tot_alloc_byt
           FROM sys.dba_temp_files
          GROUP BY tablespace_name ) data_files,
       ( SELECT tablespace_name,
                MAX(bytes) max_free_byt,
                SUM(bytes) tot_free_byt
           FROM sys.dba_free_space
          GROUP BY tablespace_name ) free_space,
       ( SELECT tablespace_name,
                SUM(DECODE(SIGN(maxblocks - blocks),-1,0,(maxblocks - blocks))) tot_extnd_blk
           FROM sys.dba_data_files
          GROUP BY tablespace_name
          UNION ALL
         SELECT tablespace_name,
                SUM(DECODE(SIGN(maxblocks - blocks),-1,0,(maxblocks - blocks))) tot_extnd_blk
           FROM sys.dba_temp_files
          GROUP BY tablespace_name ) extnd,
       sys.dba_tablespaces ts
 WHERE data_files.tablespace_name = free_space.tablespace_name(+)
   AND data_files.tablespace_name = extnd.tablespace_name(+)
   AND data_files.tablespace_name = ts.tablespace_name
 ORDER BY pctfr DESC;