Database monitor Scripts

DB UP TIME

select SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours" from sys.v_$session where sid=1 /* this is PMON */ /

DB SIZE (Database Size,Used space,Free space)

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;

Database grouth year wise

select to_char(b.CREATION_TIME,'YYYY'), count(1) "NO_OF_FILES", sum( round(b.bytes/1024/1024,2)) "SIZE_IN_MB" from v$datafile b,dba_data_files a where a.file_id=b.file# group by to_char(b.CREATION_TIME,'YYYY') order by to_char(b.CREATION_TIME,'YYYY') desc;

Database grouth monthly wise

select to_char(creation_time, 'RRRR Month') "Month", sum(bytes)/1024/1024 "Growth in Meg" from sys.v_$datafile where creation_time > SYSDATE-365 group by to_char(creation_time, 'RRRR Month');

Calculate total space for all datafiles within database.

select DF.TOTAL/1048576 "DataFile Size Mb", LOG.TOTAL/1048576 "Redo Log Size Mb", CONTROL.TOTAL/1048576 "Control File Size Mb", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual, (select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

Calculate space for list of tablespaces within primary database.

select DF.TOTAL/1048576 "DataFile Size Mb", LOG.TOTAL/1048576 "Redo Log Size Mb", CONTROL.TOTAL/1048576 "Control File Size Mb", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual, (select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in('SYSTEM','UNDOTBS1', 'SYSAUX', 'USERS')) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

Schema SIZE

select sum(bytes)/1024/1024/1024 from dba_segments where owner=upper('&owner');