set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 132
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

PROMPT #################################################################
PROMPT --  Name          :  listTablesMappingInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description   : This script will list all the tables (and their sizes) in the 
PROMPT                     specified tablespace OR all the tablespaces
PROMPT 
PROMPT --  Inputs(s)     :  None
PROMPT 
PROMPT --  Output       : The output is in a file named tablesMappingInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT #################################################################
PROMPT 

column owner           format a20             heading "Owner"           wrap
column tablespace_name format a30             heading "Tablespace Name" wrap
column segment_type    format a18             heading "Segment Type"    wrap
column segment_name    format a30             heading "Segment Name"    wrap
column size            format 999,999,999.900 heading "Size (MB)"
column count           format 999999999       heading "Count"

-- 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

PROMPT The database has the following tablespaces: 
select tablespace_name from dba_tablespaces;
PROMPT

set verify off
accept u_tblspace_name prompt 'Enter the name of a tablespace (return for all tablespaces) : ' 

set head on

spool tablesMappingInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
       center "SUMMARY of DB Objects in Tablespace By Owner for " &s_dbname " on " &s_machine skip 2

break on owner nodup skip 2
compute sum label "TOTAL" of count size on owner

select owner,
       segment_type,
       count(*) count,
       sum(bytes)/(1024*1024) "size"
from dba_segments 
where nvl(tablespace_name,1) like upper(nvl('&&u_tblspace_name','%'))
group by owner, segment_type;

ttitle left &s_curDate -
       center "LIST of DB Objects in Tablespace By Owner for " &s_dbname " on " &s_machine skip 2

clear computes
compute sum label "TOTAL" of size on owner

select owner,
       segment_name,
       segment_type, sum(bytes)/(1024*1024) "size"
from dba_segments 
where nvl(tablespace_name,1) like upper(nvl('&&u_tblspace_name','%'))
group by owner, segment_type, segment_name;

spool off

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

PROMPT
PROMPT -- If file is empty, no table mapping information was found for the database &s_dbname
PROMPT --                or no tablespace information was found for the tablespace name &u_tblspace_name
PROMPT
PROMPT
PROMPT Output file tableMappingsInfo_&s_dbname._&s_machine..LST produced
PROMPT

undefine u_tblspace_name

@sqlplus_settings