Schema size in database

 Schema size in database 

set linesize 150

set pagesize 5000

col owner for a15

col segment_name for a30

col segment_type for a20

col TABLESPACE_NAME for a30

clear breaks

clear computes

compute sum of SIZE_IN_GB on report

break on report

select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;

Tablespace Size in GB 

SELECT /* + RULE */

df.tablespace_name AS "Tablespace"

,df.bytes / (1024 * 1024 * 1024) AS "Size (GB)"

,Trunc(fs.bytes / (1024 * 1024 * 1024)) AS "Free (GB)"

FROM (

SELECT tablespace_name

,Sum(bytes) AS bytes

FROM dba_free_space

GROUP BY tablespace_name

) fs

,(

SELECT tablespace_name

,SUM(bytes) AS bytes

FROM dba_data_files

GROUP BY tablespace_name

) df

WHERE fs.tablespace_name = df.tablespace_name

ORDER BY 3 DESC

DB Size in GB ;

col "Database Size" format a20

col "Free space" format a20

col "Used space" format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used

, (select sum(bytes) as p

from dba_free_space) free

group by free.p

/

Each segment size in Schema.

select
  owner as "Schema"
  , segment_name as "Object Name"
  , segment_type as "Object Type"
  , round(bytes/1024/1024,2) as "Object Size (Mb)"
  , tablespace_name as "Tablespace"
from dba_segments
order by owner;