set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 120
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

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

column owner    format a18         heading "Owner"   wrap 
column tblspace format a30         heading "Tablespace Name" wrap
column tblsize  format 999,999,999 heading "Size in KB" 

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

-- allow user name to be entered.  NULL/return key is for all locks/users
accept u_name prompt 'Enter the name of Oracle user to show tablespace usage for (return for all users) : ' 

set head on

spool tablespaceUsageInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
       center "Database Space Usage By User and By Tablespace in "  &s_dbname " on " &s_machine skip 2

break on owner  skip 2 -
      on report skip 2

compute sum label "Total Space Used" of tblsize on owner
compute sum label "Grand Total Used" of tblsize on report

select us.name owner, 
      ts.name tblspace, 
      sum(seg.blocks*ts.blocksize)/1024  tblsize
from  sys.ts$ ts, 
      sys.user$ us, 
      sys.seg$ seg 
where seg.user# = us.user# 
  and ts.ts# = seg.ts#
  and nvl(us.name,1) like upper(nvl('&&u_name','%'))
group by us.name,ts.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 tablespace information was found for the database &s_dbname
PROMPT --                or no tablespace information was found for the username &u_name
PROMPT
PROMPT
PROMPT Output file tablespaceUsageInfo_&s_dbname._&s_machine..LST produced
PROMPT

undefine u_name

@sqlplus_settings