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;

Generic Operational Error OR Operational error () detected in EM OR Process 0x0xc0000013d3dbf638 appears to be hung in Auto SQL Tuning task

Reason:

The Automatic SQL Tuning Task (SYS_AUTO_SQL_TUNING_TASK)  has been over-running, therefore it has auto killed to protect the system from harm caused by such over-running.This is a protective measure purely to avoid the task from over-running its time limit because of a single task and  protects the system from harm caused by such over-running.

Solution:

To disable this job and messages will not appear anymore. Eventually manually execute it when needed.   

BEGIN

   DBMS_AUTO_TASK_ADMIN.DISABLE(

   client_name => ‘sql tuning advisor’,

   operation => NULL,

   window_name => NULL);

 END;

 /

 BEGIN

   DBMS_AUTO_TASK_ADMIN.ENABLE(

   client_name => ‘sql tuning advisor’,

   operation => NULL,

   window_name => NULL);

 END;

 /

Implementation:

oracle@dcpdb1 [/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 26 14:39:25 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL>

SQL> SELECT client_name, status from dba_autotask_client;

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               ENABLED

SQL>

SQL>

SQL>

SQL>

SQL> BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

  2    3  client_name => 'sql tuning advisor',

  4  operation => NULL,

  5  window_name => NULL);

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL>

SQL>

SQL> SELECT client_name, status from dba_autotask_client;

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               DISABLED

SQL>

SQL>

SQL>