Database--PLSQL-- Using Awr Tables to report the changes over time..

With Oracle 10g and above, awr tables are populated with full of statistics.. So you can directly use the Awr tables to gather those statistics in a shaped format according to your needs. You can use excel or similar tool to create visual report based on your outputs ...

Following Plsql analyzes statistics stored in Automatic Workload Repository tables to calculate the change in the size of all the tablespace in the database, as time passes...

You supply the begin and end dates to this script, and it will create the report for you. Then you can use excel to visualize the data..

/* Formatted on 09.10.2013 15:33:25 (QP5 v5.163.1008.3004) */
DECLARE
   CURSOR c2
   IS
      SELECT name
        FROM v$tablespace
       WHERE name NOT IN ('SYSTEM', 'SYSAUX', 'TEMP','GG'); -- you can extend this list
   --Variable Declaretion
   min_snap_id   NUMBER;
   max_snap_id   NUMBER;
   max_size      NUMBER;
   min_size      NUMBER;
   actual_size   NUMBER;
BEGIN
   SELECT MIN (snap_id)
     INTO min_snap_id
     FROM dba_hist_snapshot
    WHERE begin_interval_time > SYSDATE - 5;

   SELECT MAX (snap_id)
     INTO max_snap_id
     FROM dba_hist_snapshot
    WHERE begin_interval_time > SYSDATE - 5;

   FOR c2rec IN c2
   LOOP
        SELECT ROUND ( (tablespace_usedsize * 8 * 1024) / 1024 / 1024, 2)
          INTO min_size
          FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, v$TABLESPACE dt
         WHERE     snap_id IN (SELECT snap_id
                                 FROM dba_hist_snapshot
                                WHERE BEGIN_INTERVAL_TIME > SYSDATE - 5)
               AND snap_id = min_snap_id
               AND dt.name = c2rec.name
               AND dt.ts# = tsu.tablespace_id
      ORDER BY snap_id ASC;
        SELECT ROUND ( (tablespace_usedsize * 8 * 1024) / 1024 / 1024, 2)
          INTO max_size
          FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, v$TABLESPACE dt
         WHERE     snap_id IN (SELECT snap_id
                                 FROM dba_hist_snapshot
                                WHERE BEGIN_INTERVAL_TIME > SYSDATE - 5)
               AND snap_id = max_snap_id
               AND dt.name = c2rec.name
               AND dt.ts# = tsu.tablespace_id
      ORDER BY snap_id ASC;

      IF max_size <= 1
      THEN
         max_size := 1;
      END IF;

      IF min_size <= 1
      THEN
         min_size := 1;
      END IF;

        SELECT ROUND ( (tablespace_maxsize * 8 * 1024) / 1024 / 1024, 2)
          INTO actual_size
          FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, v$TABLESPACE dt
         WHERE     snap_id IN (SELECT snap_id
                                 FROM dba_hist_snapshot
                                WHERE BEGIN_INTERVAL_TIME > SYSDATE - 5)
               AND snap_id = max_snap_id
               AND dt.name = c2rec.name
               AND dt.ts# = tsu.tablespace_id
      ORDER BY snap_id ASC;

      DBMS_OUTPUT.put_line (
            c2rec.name
         || '  '
         || TO_CHAR (min_size)
         || '  '
         || TO_CHAR (max_size - min_size)
         || '  '
         || TO_CHAR (actual_size));
   END LOOP;
END;


This script will create an output like below;

CTXD 493,54 0 65535,97
OWAPUB 1 0 32767,98
APPS_TS_QUEUES 33441,25 0 262143,88
ODM 8 0 32767,98
OLAP 15,5 0 32767,98APPS_TS_TX_DATA 374292,25 1,63 1409023,33
APPS_TS_TX_IDX 324591,25 ,13 1146879,45
APPS_TS_INTERFACE 11458,88 0 98303,95
APPS_TS_NOLOGGING 1296,25 0 65535,97
APPS_TS_ARCHIVE 2845,63 0 65535,97
APPS_UNDOTS1 42692,63 -64,63 51864
TOOLS 2,81 0 32767,98
IZUX 1 0 32767,98
IZUD 1 0 32767,98APPS_TS_SUMMARY 12923,88 0 98303,95
APPS_TS_MEDIA 13993,38 0 65535,97
PORTAL 1 0 32767,98
APPS_TS_SEED 2342,88 0 65535,97

The output is in the following format:
"(Tablespace name) (min_size)  (max_size-min_size) (actual_size)"

Then you take these output in to excel and create a report like the following (for all tablespaces);

....

....

....

Using the excel graphics, following shape can be obtained by the output above..

As you see above; we obtained a clear and fine graphic for reporting the tablespace growth in time..

For RAC database, the plsql should be a little different, I didnt write it down here to keep this short. But if you want the rac version , you can contact me.
I was in a hurry while preparing this.. If you see any mistakes or things to improve, please contact me, as well..
This logic can be used to report the changes in database activity and structures with lapse of time...