Oracle database size2

Size of Oracle database 
Oracle database size 



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.SS/1024/1024/1024) || ' GB' "Used space" 
, round(free.SS /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) SS from dba_free_space) free 
group by free.SS 
/ 


Database Size        Used space           Free space
-------------------- -------------------- --------------------
2 GB                 2 GB                 0 GB


========================================================

With Database name .. 
set linesize 300
col "Database Size"  for a20
col "Free space"       for a20
col "Used space"      for a20
col hostname   for a37
col Instance   for a15
col DB_unique_name  for a15
col Tdate                       heading 'Date'
alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
select sysdate Tdate,SYS_CONTEXT('USERENV','HOST') hostname,sys_context('USERENV', 'INSTANCE_NAME') Instance ,SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name,
round(sum(used.bytes)/1024/1024/1024 ) || ' GB' "Database Size" , round(sum(used.bytes)/1024/1024/1024 ) - round(free.SS/1024/1024/1024) || ' GB' "Used space"
, round(free.SS /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) SS from dba_free_space) free
group by free.SS

/

--- with ARCHIVELOG status
set linesize 300
col "Database Size"  	for a20
col "Free space"       	for a20
col "Used space"      	for a20
col hostname   		for a37
col Instance   		for a15
col DB_unique_name  	for a15
col Tdate               heading 'Date'
alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
select sysdate Tdate,SYS_CONTEXT('USERENV','HOST') hostname,sys_context('USERENV', 'INSTANCE_NAME') Instance ,SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name,
round(sum(used.bytes)/1024/1024/1024 ) || ' GB' "Database Size" 
,round(sum(used.bytes)/1024/1024/1024 ) - round(free.SS/1024/1024/1024) || ' GB' "Used space"
,round(free.SS /1024/1024/1024) || ' GB' "Free space",LOG_MODE
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) used , (select sum(bytes) SS from dba_free_space) free,(select LOG_MODE from v$database)
group by free.SS,LOG_MODE
/

-----


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

Size in GB
----------
1.82537842



on  standby !!
==================================

select
    ( select sum(bytes)/1024/1024/1024 data_size from v$datafile ) +
    ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from v$tempfile ) +
    ( 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 linesize 200
col "Database Size"  	for a20
col "Free space"       	for a20
col "Used space"      	for a20
col hostname   		for a35
col Instance   		for a15
col DB_unique_name  	for a15
col Tdate               heading 'Date'
alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
select sysdate Tdate,SYS_CONTEXT('USERENV','HOST') hostname,sys_context('USERENV', 'INSTANCE_NAME') Instance ,
SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name,
    ( select sum(bytes)/1024/1024/1024 data_size from v$datafile ) +
    ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from v$tempfile ) +
    ( 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;



with SGA .. 

set lines 400
col platform_name format a30
col open_mode format a20
col host_name format a20
col version format a10
col status format a20
col uptime format a60
col name format a10


col "DB size GB" for 99999999.99
SELECT D.NAME,D.PLATFORM_NAME,D.CREATED, D.OPEN_MODE,substr(I.HOST_NAME,1,20) HOST_NAME ,I.VERSION, I.ARCHIVER,I.STATUS,
 	FLOOR (SYSDATE - STARTUP_TIME) || ' DAYS(S) '|| TRUNC (24* ( (SYSDATE - STARTUP_TIME) - TRUNC (SYSDATE - STARTUP_TIME))) || ' HOUR(S) '|| MOD (
TRUNC (1440* ( (SYSDATE - STARTUP_TIME) - TRUNC (SYSDATE - STARTUP_TIME))),60)|| ' MINUTE(S) '|| MOD (TRUNC (86400* ( (SYSDATE - STARTUP_TIME) - TRUNC (SYSDATE - STARTUP_TIME))),60)|| ' SECONDS'
UPTIME,
    (select
    ( select sum(bytes)/1024/1024/1024 data_size from v$datafile ) +
    ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from v$tempfile ) +
    ( 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) 
    from   dual) "DB size GB",
	( SELECT ROUND(BYTES/1024/1024/1024) "mem GB" FROM V$SGAINFO WHERE NAME = 'Maximum SGA Size' ) "SGA max size GB"
  FROM V$DATABASE D INNER JOIN V$INSTANCE I 
  ON UPPER(D.NAME) = UPPER(I.INSTANCE_NAME);


===

file location !!!

select distinct substr(name, 1, instr(name, '/',-1)) PATH
 from (
select name from v$datafile
 union all
 select NAME from v$controlfile
 union all
 select MEMBER name from v$logfile
 union all
 select name from v$tempfile
 ) order by 1;




 
 select
'===========================================================' 			|| chr(10) 	||
'Total Physical Size = ' || round(redolog_size_gb+dbfiles_size_gb+tempfiles_size_gb+archlog_size_gb+ctlfiles_size_gb,2) || ' GB' || chr(10) ||
'===========================================================' 			|| chr(10) ||
' Redo Logs Size 		: ' || round(redolog_size_gb,3) || ' GB' 		|| chr(10) ||
' Data Files Size 		: ' || round(dbfiles_size_gb,3) || ' GB' 		|| chr(10) ||
' Temp Files Size 		: ' || round(tempfiles_size_gb,3) || ' GB' 		|| chr(10) ||
' Archive Log Size 		: ' || round(archlog_size_gb,3) || ' GB' 		|| chr(10) ||
' Control Files Size 	: ' || round(ctlfiles_size_gb,3) || ' GB' 		|| chr(10) ||
'===========================================================' 			|| chr(10) ||
'Actual Database Size = ' || db_size_gb || ' GB' 						|| chr(10) ||
'===========================================================' 			|| chr(10) ||
' Used Database Size 	: ' || used_db_size_gb || ' GB' 				|| chr(10) ||
' Free Database Size 	: ' || free_db_size_gb || ' GB' as summary
from (
select sys_context('USERENV', 'DB_NAME') 															db_name
,(select sum(bytes)/1024/1024/1024 redo_size from v$log ) 											redolog_size_gb
,(select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) 									dbfiles_size_gb
,(select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) 							tempfiles_size_gb
,(select sum(bytes)/1024/1024/1024 from v$log where sequence# in (select sequence# from v$loghist)) archlog_size_gb
,(select sum(block_size*file_size_blks)/1024/1024/1024 controlfile_size from v$controlfile) 		ctlfiles_size_gb
,round(sum(used.bytes)/1024/1024/1024,3) 															db_size_gb
,round(sum(used.bytes)/1024/1024/1024,3) - round(free.f/1024 /1024/ 1024) 							used_db_size_gb
,round(free.f/1024/1024/1024,3) 																    free_db_size_gb
from (select bytes from v$datafile
union all
select bytes from v$tempfile) used
,(select sum(bytes) as f from dba_free_space) free
group by free.f);


from web 



COLUMN name NEW_VALUE _instname NOPRINT
select lower(instance_name) name from v$instance;

COLUMN conname NEW_VALUE _conname NOPRINT
select case 
            when a.conname = 'CDB$ROOT'   then 'ROOT'
            when a.conname = 'PDB$SEED'   then 'SEED'
            else a.conname
            end as conname
from (select SYS_CONTEXT('USERENV', 'CON_NAME') conname from dual) a;

COLUMN conid NEW_VALUE _conid NOPRINT
select SYS_CONTEXT('USERENV', 'CON_ID') conid from dual;

col bytes format 999,999,999,999,999,999

WITH
sizes AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 1e.77 */
       'Data' file_type,
       SUM(bytes) bytes
  FROM v$datafile
 UNION ALL
SELECT 'Temp' file_type,
       SUM(bytes) bytes
  FROM v$tempfile
 UNION ALL
SELECT 'Log' file_type,
       SUM(bytes) * MAX(members) bytes
  FROM v$log
 UNION ALL
SELECT 'Control' file_type,
       SUM(block_size * file_size_blks) bytes
  FROM v$controlfile
),
dbsize AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 1e.77 */
       'Total' file_type,
       SUM(bytes) bytes
  FROM sizes
)
SELECT d.dbid,
       d.name db_name,
       s.file_type,
       s.bytes,
       ROUND(s.bytes/POWER(10,9),3) gb,
       CASE
       WHEN s.bytes > POWER(10,15) THEN ROUND(s.bytes/POWER(10,15),3)||' P'
       WHEN s.bytes > POWER(10,12) THEN ROUND(s.bytes/POWER(10,12),3)||' T'
       WHEN s.bytes > POWER(10,9) THEN ROUND(s.bytes/POWER(10,9),3)||' G'
       WHEN s.bytes > POWER(10,6) THEN ROUND(s.bytes/POWER(10,6),3)||' M'
       WHEN s.bytes > POWER(10,3) THEN ROUND(s.bytes/POWER(10,3),3)||' K'
       WHEN s.bytes > 0 THEN s.bytes||' B' END display
  FROM v$database d,
       sizes s
 UNION ALL
SELECT d.dbid,
       d.name db_name,
       s.file_type,
       s.bytes,
       ROUND(s.bytes/POWER(10,9),3) gb,
       CASE
       WHEN s.bytes > POWER(10,15) THEN ROUND(s.bytes/POWER(10,15),3)||' P'
       WHEN s.bytes > POWER(10,12) THEN ROUND(s.bytes/POWER(10,12),3)||' T'
       WHEN s.bytes > POWER(10,9) THEN ROUND(s.bytes/POWER(10,9),3)||' G'
       WHEN s.bytes > POWER(10,6) THEN ROUND(s.bytes/POWER(10,6),3)||' M'
       WHEN s.bytes > POWER(10,3) THEN ROUND(s.bytes/POWER(10,3),3)||' K'
       WHEN s.bytes > 0 THEN s.bytes||' B' END display
  FROM v$database d,
       dbsize s;




====

-- with Pluggable database size

compute sum of GB on report
break on report

set linesize 300 pagesize 300
col datafile_name for a100
col container for a20
col gb  for 9999999.99
select  c.name container, sum(BYTES)/1024/1024/1024 GB 
from x$kcvfh x, v$datafile d, v$containers c
where d.file# = x.hxfil
and d.con_id = c.con_id
--and c.name  in ('PDB$SEED','CDB$ROOT')
group by c.name
;

CONTAINER                     GB
-------------------- -----------
PDB9                      138.66
ANUJV                       5.19
PDB$SEED                    4.91
CDB$ROOT                    4.15
                     -----------
sum                       152.90




Via Email !!!!




Script /home/oracle/DBA/db_report.sql

SET MARKUP HTML ON SPOOL ON
set termout off
set pagesize 999
set linesize 300
set feedback off
SET MARKUP HTML ON TABLE "class=sysaud cellspacing=2 border='2' width='95%' align='center' " ENTMAP OFF

spool db_report.html

set pause off


prompt
prompt DATABASE Size
prompt
prompt

set linesize 300
col "Database Size"  for a20
col "Free space"       for a20
col "Used space"      for a20
col hostname   for a35
col Instance   for a15
col DB_unique_name  for a15
col Tdate                       heading 'Date'
alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
select sysdate Tdate,SYS_CONTEXT('USERENV','HOST') hostname,sys_context('USERENV', 'INSTANCE_NAME') Instance ,SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name,
round(sum(used.bytes)/1024/1024/1024 ) || ' GB' "Database Size" , round(sum(used.bytes)/1024/1024/1024 ) - round(free.SS/1024/1024/1024) || ' GB' "Used space"
, round(free.SS /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) SS from dba_free_space) free
group by free.SS
/

spool off
exit






======================











cat dbsize.sql



SET MARKUP HTML ON SPOOL ON
set termout off
set pagesize 999
set linesize 300
set feedback off
SET MARKUP HTML ON TABLE "class=sysaud cellspacing=2 border='2' width='95%' align='center' " ENTMAP OFF


alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
set linesize 300
col "Database Size"  for a20
col "Free space"       for a20
col "Used space"      for a20
col hostname   for a35
col Instance   for a15
col DB_unique_name  for a15
col Tdate                       heading 'Date'
spool DB.html append
select sysdate Tdate,SYS_CONTEXT('USERENV','HOST') hostname,sys_context('USERENV', 'INSTANCE_NAME') Instance ,SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') DB_unique_name,
round(sum(used.bytes)/1024/1024/1024 ) || ' GB' "Database Size" , round(sum(used.bytes)/1024/1024/1024 ) - round(free.SS/1024/1024/1024) || ' GB' "Used space"
, round(free.SS /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) SS from dba_free_space) free
group by free.SS
/






cat asm1.sql



set feedback off
var v number ;
begin :v := nvl('95',90); end;
/



alter session set nls_date_format='dd-mm-yyyy hh24:mi' ;
set feedback off pagesize 1000 head off  underline _ linesize 150




SET MARKUP HTML ON SPOOL ON
set termout off
set pagesize 999
set linesize 300
set feedback off
SET MARKUP HTML ON TABLE "class=sysaud cellspacing=2 border='2' width='95%' align='center' " ENTMAP OFF

spool ASM.html
col  total_gb        format 999,999,999      heading 'total gb '
col  mb_avail        format 999,999,999      heading 'mb avail'
col  used            format 999.99           heading '% used'
col  volume_name     format a10
col  volume_device   format a25
col  mountpath       format a15
col  attention       format a20
col  name            format a15
select    g.group_number
         , g.name
         , g.state
         , g.total_mb/1024            total_gb
         , trunc((g.free_mb/1024),2)  free_gb
         , usable_file_mb           usable_file_mb
         , count(DISK_NUMBER) Total_Disks
         , trunc(max(d.TOTAL_MB/1024),2) disk_size
         , (round((1- (g.free_mb / g.total_mb))*100, 2)) "TotalUsed%"
                 , case when (ROUND((1- (g.free_mb / g.total_mb))*100, 2)>:v) then :v||'% full ***'
          else 'Good'
         end as attention
from    v$asm_diskgroup g,v$asm_disk d
where 1=1
and  d.group_number = g.group_number
--and   g.Name not in ('GRID' )
group by g.group_number, g.name,g.state, g.total_mb/1024, trunc((g.free_mb/1024),2), usable_file_mb,(round((1- (g.free_mb / g.total_mb))*100, 2))
ORDER BY "TotalUsed%" desc ;