Log History

set pages 200 lines 200
set lines 130;
set pages 999;
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
FROM v$log_history
GROUP BY TO_CHAR (first_time, 'YYYY-MON-DD')
ORDER BY 1;

Oracle Database Growth Monitoring

Display full database growth per week (Last Seven 7 Days).

Below query display full database growth of last 7 days. To understand query, below query divided into four layer. Upto date tablespace space used view in first layer(layer 1). Tablespace and day wise size increase in second layer(layer 2).  tablespace wise average size increase for last 7 days exclude today in third layer (layer 3). full database growth in last 7 days in fourth layer (layer 4).     

--Layer 4
SELECT SUM(cur_used_size_mb)cur_used_size_mb,SUM(avg_increas_mb)avg_increas_mb

FROM(

--Layer 3
SELECT b.tsname tablespace_name,  --tablespace wise average size increase for last 7 days exclude today  

         MAX (b.used_size_mb) cur_used_size_mb,

         ROUND (AVG (inc_used_size_mb), 2) avg_increas_mb

    FROM (

     --Layer 2
     SELECT a.days, --Tablespace and day wise tablespace size increase

                 a.tsname,

                 used_size_mb,

                   used_size_mb

                 - LAG (used_size_mb, 1) --LAG function is used to access data from a previous row

                      OVER (PARTITION BY a.tsname ORDER BY a.tsname, a.days)

                    inc_used_size_mb

       FROM (  

       --Layer 1 
      SELECT TO_CHAR (snp.begin_interval_time, 'MM-DD-YYYY') days, --Upto date Tablespace used

                           ts.tsname,

                           MAX (

                              ROUND (

                                   (usgtb.tablespace_usedsize * dt.block_size)

                                 / (1024 * 1024),

                                 2))

                              used_size_mb

                      FROM dba_hist_tbspc_space_usage usgtb, --Display historical tablespace usage statistics

                           dba_hist_tablespace_stat ts,  --Display tablespace information from the control file

                           dba_hist_snapshot snp, --Display Information about the snapshots in the Workload Repository

                           dba_tablespaces dt --Describes the tablespaces accessible to the current user

                     WHERE     usgtb.tablespace_id = ts.ts#

                           AND usgtb.snap_id = snp.snap_id

                           AND ts.tsname = dt.tablespace_name

                           AND (snp.begin_interval_time) BETWEEN  (SYSDATE -7) and (SYSDATE - 1) --Last 7 days information from  dba_hist_snapshot table exclude today 

                  GROUP BY TO_CHAR (snp.begin_interval_time, 'MM-DD-YYYY'),

                           ts.tsname

                  ORDER BY ts.tsname, days  --End Layer 1

                  ) a --End Layer 2
                  ) b --End Layer 3
                  ) --End Layer 4

Explanation

DBA_HIST_TBSPC_SPACE_USAGE --displays historical tablespace usage statistics.

DBA_HIST_SNAPSHOT --displays information about the snapshots in the Workload Repository

DBA_HIST_TABLESPACE_STAT --display tablespace information from the control file

DBA_TABLESPACES --describes the tablespaces accessible to the current user

 BEGIN_INTERVAL_TIME-- Time at the beginning of the snapshot interval, this column belongs to DBA_HIST_SNAPSHOT  table

SNAP_ID --Unique snapshot ID

TABLESPACE_USEDSIZE --Used size of the tablespace (in database blocks), DBA_HIST_TBSPC_SPACE_USAGE table

 LAG --function is used to access data from a previous row

TSNAME--tablespace name

Display tablespace level database growth per week (Last Seven 7 Days)
SELECT b.tsname tablespace_name,  --tablespace wise average size increase for last 7 days exclude today  

         MAX (b.used_size_mb) cur_used_size_mb,

         ROUND (AVG (inc_used_size_mb), 2) avg_increas_mb

    FROM

     (SELECT a.days, --Tablespace and day wise tablespace size increase

                 a.tsname,

                 used_size_mb,

                   used_size_mb

                 - LAG (used_size_mb, 1) --LAG function is used to access data from a previous row

                      OVER (PARTITION BY a.tsname ORDER BY a.tsname, a.days)

                    inc_used_size_mb

       FROM

       (  SELECT TO_CHAR (snp.begin_interval_time, 'MM-DD-YYYY') days, --Upto date Tablespace used

                           ts.tsname,

                           MAX (

                              ROUND (

                                   (usgtb.tablespace_usedsize * dt.block_size)

                                 / (1024 * 1024),

                                 2))

                              used_size_mb

                      FROM dba_hist_tbspc_space_usage usgtb, --Display historical tablespace usage statistics

                           dba_hist_tablespace_stat ts,  --Display tablespace information from the control file

                           dba_hist_snapshot snp, --Display Information about the snapshots in the Workload Repository

                           dba_tablespaces dt --Describes the tablespaces accessible to the current user

                     WHERE     usgtb.tablespace_id = ts.ts#

                           AND usgtb.snap_id = snp.snap_id

                           AND ts.tsname = dt.tablespace_name
                           AND (snp.begin_interval_time) BETWEEN  (SYSDATE -8) and (SYSDATE ) --Last 7 days information from  dba_hist_snapshot table exclude today 

                  GROUP BY TO_CHAR (snp.begin_interval_time, 'MM-DD-YYYY'),

                           ts.tsname

HAVING TO_CHAR (snp.begin_interval_time, 'MM-DD-YYYY') <> TO_CHAR(TRUNC(SYSDATE),'MM-DD-YYYY')                  
ORDER BY ts.tsname, days

                  ) a) b

GROUP BY b.tsname

ORDER BY b.tsname;

Database Monitoring Script

Total Size of Database with free space:
Select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",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;

Track Tablespace Used/Free Space:
SELECT /* + RULE */  df.tablespace_name "Tablespace",  df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs, (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1), Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs, (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Track Redo generation by day:
select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log
group by trunc(completion_time) order by 1;

Track Logon time of DB user and OS user:
Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine from v$session where type !='BACKGROUND';