Oracle DB size with hostname,db name,instance name, version ,created date.

drwXIUGAJHGD

The size of the database is the space the files physically consume on disk.

You can find this with below query:

SQL> !cat dbsize.sql

set lines 300

col host for a12

set newpage none

set feedback off

set trimspool on

select h.host_name “host”, k.name “database”,h.instance_name “Instance”,h.version_full,k.created created,

p.perm “data_files”,(u.undo+t.temp) “tempundo”,(p.perm+u.undo+t.temp) “total”, (p.perm+u.undo+t.temp-f.free)

“used”,f.free “free_space”,trunc(((p.perm+u.undo+t.temp-f.free)/(p.perm+u.undo+t.temp))*100) “pct_used”

from (select instance_name,HOST_NAME,version_full from v$instance) h,

(select name,created from v$database) k,

(select sum(bytes/1024/1024/1024) as perm from dba_data_files a, dba_tablespaces b

where a.tablespace_name=b.tablespace_name and b.contents=’PERMANENT’) p,

(select sum(bytes/1024/1024/1024) as undo from dba_data_files a, dba_tablespaces b

where a.tablespace_name=b.tablespace_name and b.contents=’UNDO’) u,

(select sum(bytes/1024/1024/1024) as temp from dba_temp_files a,dba_tablespaces b

where a.tablespace_name=b.tablespace_name and b.contents=’TEMPORARY’) t,

(select sum(bytes/1024/1024/1024) as free from dba_free_space) f;

–The above command already copied into dbsize.sql

SQL> @dbsize.sql

host database Instance VERSION_FULL CREATED data_files tempundo total used free_space pct_used

———— ——— —————- —————– ——— ———- ———- ———- ———- ———- ———-

ORA_SRVR ORCLP orclp 19.12.0.0.0 12-AUG-15 3633.34178 325.063446 3958.40523 1056.06758 2902.33765 26

See Also:

How to find Oracle DB size along with hostname,db name,instance name, version ,created date