-- User Schema Size

SELECT  TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss') as_of,
 SUBSTR(REPLACE(SYS.database_name,'.REGRESS.RDBMS.DEV.US.ORACLE.COM',''),1,15) db_name,
 SUBSTR(MIN(user),1,15) segment_name,
 ROUND(Sum(bytes)/1024/1024,3) AS size_in_mb,
 ROUND(Sum(bytes)/1024/1024/1024,3)  size_in_gb
FROM user_segments;

-- User Segment Details
-- For DBA segments use dba_xxx tables 
-- and comment the user tables

SELECT 
 s.Segment_Name, s.Segment_Type,
 e.extent_id, s.extents,
 s.Bytes, s.Bytes/16384 Blocks 
FROM  user_segments s, user_extents e
      -- dba_segments s, dba_extents e 
WHERE s.segment_name = e.segment_name
AND   INSTR(s.segment_name,UPPER('&i_seg')) > 0; 

-- User Segment Summary
-- For DBA segments summary use dba_xxx
-- tables and comment the user tables

SELECT
 s.Segment_Name, s.Segment_Type,
 s.extents, 
 SUM(s.Bytes) bytes,
 SUM(s.Bytes)/1024/1024 bytes_mb,
 SUM(s.Bytes)/16384  Blocks 
FROM  user_segments s, user_extents e
      -- dba_segments s, dba_extents e 
WHERE s.segment_name = e.segment_name
AND   INSTR(s.segment_name,UPPER('&i_seg')) > 0
GROUP BY
 s.Segment_Name, s.Segment_Type, s.extents
ORDER BY s.segment_name;