Find out the Schema/User Size

Find out the Schema/User Size

If you are freaks who work around with Oracle as backend, you would face the sitution how to find the schema or user size.

This script finds out the Schema/User Size

 SELECT NVL(tablespace_name, 'UNKNOWN') TABLESPACE,
   SUM(BYTES)/1024/1024 AS "Total Size(MB)"
   FROM DBA_SEGMENTS
  WHERE owner = UPPER('&SCHEMA_NAME')
GROUP BY owner,
   ROLLUP (tablespace_name)

TABLESPACE	Total Size(MB)
USERS	2.875
EXAMPLE	6.25
UNKNOWN	9.125

This script finds out the tablespace's Size

 SELECT NVL(b.tablespace_name, NVL(a.tablespace_name,'UNKNOWN')) "Tablespace",
   kbytes_alloc "Allocated MB"                                               ,
   kbytes_alloc-NVL(kbytes_free,0) "Used MB"                                 ,
   NVL(kbytes_free,0) "Free MB"                                              ,  
   data_files "No. Data Files"
   FROM
   (
       SELECT SUM(bytes)/1024/1024 Kbytes_free,
         MAX(bytes)     /1024/1024 largest    ,
         tablespace_name
         FROM sys.dba_free_space
     GROUP BY tablespace_name
   ) a,
   (
       SELECT SUM(bytes)/1024/1024 Kbytes_alloc,
         tablespace_name                       ,
         COUNT(*) data_files
         FROM sys.dba_data_files
     GROUP BY tablespace_name
   ) b
  WHERE a.tablespace_name (+) = b.tablespace_name
ORDER BY 1

Tablespace	Allocated MB	Used MB	Free MB	No. Data Files
EXAMPLE	100	77.375	22.625	1
SYSAUX	370	350.1875	19.8125	1
SYSTEM	500	492.25	7.75	1
UNDOTBS1	100	11.4375	88.5625	1
USERS	6.25	5.6875	0.5625	1

Copyright - There is no copyright on the code. You can copy, change and distribute it freely. Just mentioning this site should be fair

(C) August 2008, manivelcode