set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 140
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

PROMPT
PROMPT ###################################################################### 
PROMPT 
PROMPT --  Name          : listTablespaceInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description   : This script lists comprehensive tablespace
PROMPT --                  info for the database
PROMPT 
PROMPT --  Input(s)     : None
PROMPT 
PROMPT --  Output       : The output is in a file named tablespaceInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT ######################################################################
PROMPT 

column tablespace_name   format a21             heading "Tablespace Name"
column file_name         format a45	        heading "File Name"
column extent_management format a10             heading "Extent|Management"
column totsize           format 99,999,990.900  heading "Total Allocated|Size (MB)"
column used              format 99,999,990.900  heading "Total Used|Size (MB)"
column autoextensible    format a6              heading "Auto|Extend"
column pct_free          format 99,999,990.9    heading "Percent|Free"
column pct_used          format 99,999,990.9    heading "Percent|Used"
column free              format 99,999,990.900  heading "Total Free|Size (MB)"
column fragments                                heading "Number of|Fragments"
column biggest                                  heading "Biggest|(Bytes)"
column smallest                                 heading "Smallest|(Bytes)"
column coalescable_extents                      heading "Coalescable|Extents"
column crsize            format 999,999,990.900 heading "Start File|Size (MB)"
column cursize           format 999,999,990.900 heading "Current File|Size (MB)"
column maxsize           format 999,999,990.900 heading "Max File|Size (MB)"
column incrby            format 99,999.999      heading "Increment|Size (MB)"

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on
set feedback off

set head on

spool tablespaceInfo_&s_dbname._&s_machine
 
ttitle left &s_curDate -
       center "Tablespace Configuration Summary for "  &s_dbname  " on " &s_machine skip 2

select d.tablespace_name,
       d.status,
       d.contents,
       d.extent_management,
       d.logging,
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') totsize,
       TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99,999,990.900') used
from sys.dba_tablespaces d,
     (select tablespace_name, sum(bytes) bytes
       from dba_data_files group by tablespace_name) a,
     (select tablespace_name, sum(bytes) bytes 
       from dba_free_space group by tablespace_name) f
where d.tablespace_name = a.tablespace_name(+)
  and d.tablespace_name = f.tablespace_name(+)
  and NOT (d.extent_management like 'LOCAL'
  and d.contents like 'TEMPORARY')
union all
select d.tablespace_name,
       d.status,
       d.contents,
       d.extent_management,
       d.logging,
       TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') totsize,
       TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99,999,990.900') used
from sys.dba_tablespaces d,
     (select tablespace_name, sum(bytes) bytes
       from dba_temp_files group by tablespace_name) a,
     (select tablespace_name, sum(bytes_cached) bytes
       from v$temp_extent_pool group by tablespace_name) t
where d.tablespace_name = a.tablespace_name(+)
  and d.tablespace_name = t.tablespace_name(+)
  and d.extent_management like 'LOCAL'
  and d.contents like 'TEMPORARY';
	
	
ttitle left &s_curDate -
       center "Tablespace Datafile Mapping for "  &s_dbname " on " &s_machine skip 2

break on tablespace_name skip 1

select tablespace_name,
       file_name,
       bytes/(1024*1024) totsize,
       autoextensible
from dba_data_files
order by tablespace_name;

ttitle left &s_curDate -
       center "Tablespace Space Utilization for "  &s_dbname " on " &s_machine skip 2

select ttl.tablespace_name,
      TO_CHAR(NVL(ttl.ttl / 1024 / 1024, 0),'99,999,990.900') totsize,
      TO_CHAR(NVL(used / 1024 / 1024, 0),'99,999,990.900') used,
      TO_CHAR(NVL(free / 1024 / 1024, 0),'99,999,990.900') free,
      100.0 * nvl(free,0) / ttl.ttl pct_free,
      100.0 * nvl(used,0) / ttl.ttl pct_used
from (select tablespace_name, sum(bytes) ttl
       from sys.dba_data_files group by tablespace_name) ttl,
     (select tablespace_name, sum(bytes) free 
       from sys.dba_free_space group by tablespace_name) spacefree,
     (select tablespace_name, sum(bytes) used 
       from sys.dba_segments group by tablespace_name) spaceused
where ttl.tablespace_name = spaceused.tablespace_name(+)
  and ttl.tablespace_name = spacefree.tablespace_name(+)
order by spacefree.tablespace_name;

ttitle left &s_curDate -
       center "Tablespace Space Fragmentation Report for "  &s_dbname " on " &s_machine skip 2

column "FRAGS"           format 999
set numformat 999,999,999,999

select distinct a.tablespace_name, 
       fragments,
       biggest,
       smallest,
       coalescable_extents
from dba_data_files a,
     (select tablespace_name,
             count(bytes) "FRAGMENTS",
             max(bytes) "BIGGEST",
             min(bytes) "SMALLEST"
      from dba_free_space
      group by tablespace_name) b,
     (select c.tablespace_name "TABLESPACE",
             count(*) "COALESCABLE_EXTENTS"
      from dba_free_space a,
           dba_free_space b,
           dba_tablespaces  c
      where a.tablespace_name = b.tablespace_name
        and a.tablespace_name = c.tablespace_name
        and a.file_id = b.file_id
        and (a.block_id + a.blocks) = b.block_id
      group by c.tablespace_name) c
where a.tablespace_name(+) = b.tablespace_name
  and a.tablespace_name = c.TABLESPACE(+)
order by a.tablespace_name, Fragments;

ttitle left &s_curDate -
       center "Tablespace files Auto Extend Report "  &s_dbname " on " &s_machine skip 2

break on tablespace_name skip 1

select tablespace_name,
       file_name,
       autoextensible,
       vd.create_bytes/(1024*1024) crsize, 
       d.bytes/(1024*1024) cursize,
       nvl(maxbytes,0)/(1024*1024) maxsize,
       (v.value * increment_by/(1024*1024)) incrby
from dba_data_files d,
     v$parameter v, 
     v$datafile vd
where v.name='db_block_size'
  and vd.file# = d.file_id
order by tablespace_name;

spool off

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on 

PROMPT
PROMPT -- If file is empty, no tablespace information was found for the database &s_dbname
PROMPT
PROMPT
PROMPT Output file tablespaceInfo_&s_dbname._&s_machine..LST produced
PROMPT

@sqlplus_settings