Size of db

select

(select sum(bytes)/1024/1024/1024 data_size from dba_data_files)+

(select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files)+

(select sum(bytes)/1024/1024/1024 redo_size from sys.v$log)+

(select sum(BLOCK_SIZE+FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile)"Size in GB"

from dual

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select

(select sum(bytes)/1024/1024/1024 data_size from dba_segments)+

(select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files)+

(select sum(bytes)/1024/1024/1024 redo_size from sys.v$log)+

(select sum(BLOCK_SIZE+FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile)"Size in GB"

from dual

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

set pagesize 100

set linesize 200

break on report

compute sum of free_mb on report

compute sum of act_size on report

compute sum of used_mb on report

col %used format 999.99

col %Free format 999.99

col act_size format 99,99,999.99

col free_mb format 99,99,999.99

col used_mb format 99,99,999.99

col file_name for a60

col largest_extent_MB format 99,99,999.99

col tablespace for a25

select SUBSTR(a.tablespace_name,1,20) "TABLESPACE",act_size,free_mb,

free_mb*100/act_size "%FREE",

act_size-free_mb used_MB,((act_size-free_mb)*100)/act_size "%USED",largest_extent_MB

from (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,

max(bytes)/1024/1024 largest_extent_MB

FROM DBA_FREE_SPACE group by tablespace_name) a,

(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 ACT_SIZE FROM

DBA_DATA_FILES group by tablespace_name) b

where a.tablespace_name(+)=b.tablespace_name

order by 1

/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~