Tip of the Week
Tip for Week of November 22, 2004

Tablespace Owner and Usage

This tip comes from Nikhil Dave, Sr. Software Enginner, Hexaware Technologies Ltd., in Navi Mumbai, India.

This tip shows the tablespace owner and usage for all within a database.

SET ECHO OFF
set newpage 0
ttitle center 'Database Usage by user and Tablespace'-
right 'Page:' format 999 sql.pno skip skip
break on owner skip 2
col K format 999,999,999 heading 'Size K'
col ow format a24 heading 'Owner'
col ta format a30 heading 'Tablespace'
spool tfsdbspa.lst
set feedback off
set feedback 6
select us.name ow,
ts.name ta,
sum(seg.blocks*ts.blocksize)/1024 K
from sys.ts$ ts,
sys.user$ us,
sys.seg$ seg
where seg.user# = us.user#
and ts.ts# = seg.ts#
group by us.name,ts.name
/
prompt End of Report
spool off
ttitle off
clear breaks
clear columns
clear computes
set verify on