Here is a nice little script I like to use for showing the database size for various components.
[code language=”sql”]
#!/bin/ksh ############################################################################
# Script Name..: db_overall_size.ksh
# Description..:
# Author…….:
# Date………: 04/10/2013
# Version……:
# Modified By..:
# Date Modified:
# Comments…..:
# Schema owner.:
# Login User…:
# Run Order….:
# Dependent on.:
# Script type..:
# ############################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 200
set pages 200
spool db_size.txt set feed off
column systot format 99,990.99 heading "System |Alloc|GB"
column sysused format 99,990.99 heading "System |Used|GB"
column systm format 99,990.99 heading "System Tbsp|MB"
column sysax format 99,990.99 heading "Sys Aux|GB"
column systmusd format 99,990.99
column sysused format 99,990.99
column undotb format 99,990.99 heading "Undo|GB"
column datatot format 99,990.99 heading "Data|Alloc|GB"
column dataused format 99,990.99 heading "Data|Used|GB"
column tmptot format 99,990.99 heading "TEMP|Alloc|GB"
column redotot format 99,990.99 heading "Online|Redo|MB"
column ctltot format 99,990.99 heading "Ctrl File|MB"
column systall format 99,990.99 column systused format 99,990.99
column fratot format 99,990.99 column fraused format 99,990.99
column total_gb format 99,990.99 column free_gb format 99,990.99
select name from v\$database;
select s.system_size SYSTOT,
s.system_size-f1.free_mb SYSUSED,
st.system_size SYSTM,
sa.system_size SYSAX,
ud.system_size UNDOTB,
d.data_size DATATOT,
d.data_size-f2.free_mb DATAUSED,
t.temp_size TMPTOT,
r.redo_size REDOTOT,
c.controlfile_size CTLTOT,
s.system_size+t.temp_size+r.redo_size+c.controlfile_size SYSTALL,
s.system_size-f1.free_mb+t.temp_size+r.redo_size+c.controlfile_size SYSTUSED,
fra.fratot FRATOT,
fra.fraused FRAUSED
from (select sum(bytes)/1024/1024/1024 system_size
from dba_data_files
where tablespace_name like ‘SYSTEM’ or
tablespace_name like ‘SYSAUX’ or
tablespace_name like ‘UNDO%’ or
tablespace_name like ‘TOOLS’ or
tablespace_name like ‘USERS’) s,
(select sum(bytes)/1024/1024/1024 data_size
from dba_data_files
where tablespace_name not like ‘SYSTEM’ and
tablespace_name not like ‘SYSAUX’ and
tablespace_name not like ‘UNDO%’ and
tablespace_name not like ‘TOOLS’ and
tablespace_name not like ‘USERS’) d,
(select sum(bytes)/1024/1024 system_size
from dba_data_files
where tablespace_name like ‘SYSTEM’ ) st,
(select sum(bytes)/1024/1024/1024 system_size
from dba_data_files
where tablespace_name like ‘SYSAUX’ ) sa,
(select sum(bytes)/1024/1024/1024 system_size
from dba_data_files
where tablespace_name like ‘UNDO%’ ) ud,
(select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files) t,
(select sum(bytes)/1024/1024 redo_size
from sys.v\$log) r,
(select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v\$controlfile) c,
(select sum(bytes)/1024/1024/1024 FREE_MB
from dba_free_space
where tablespace_name like ‘SYSTEM’ or
tablespace_name like ‘SYSAUX’ and
tablespace_name like ‘UNDO%’ and
tablespace_name like ‘TOOLS’ and
tablespace_name like ‘USERS’) f1,
(select sum(bytes)/1024/1024/1024 FREE_MB
from dba_free_space
where tablespace_name not like ‘SYSTEM’ and
tablespace_name not like ‘SYSAUX’ and
tablespace_name not like ‘UNDO%’ and
tablespace_name not like ‘TOOLS’ and
tablespace_name not like ‘USERS’) f2,
(select name, (SPACE_LIMIT)/1024/1024/1024 FRATOT, (space_used)/1024/1024/1024 FRAUSED
from V\$RECOVERY_FILE_DEST) fra;
spool off
EOF
[/code]