[ORACLE DB] Create a job, that autos gathering tablespace space

/* Formatted on 8/15/2017 1:40:28 PM (QP5 v5.227.12220.39724) */
--DBA_TABLESPACE_HIST--


CREATE TABLE DBA_TABLESPACE_HIST
(
   DATE_HIST         DATE,
   TABLESPACE_NAME   VARCHAR2 (20),
   STATUS            VARCHAR2 (20),
   CONTENTS          VARCHAR2 (20),
   MEGS_ALLOC        NUMBER,
   MEGS_FREE         NUMBER,
   MEGS_USED         NUMBER,
   PCT_FREE          NUMBER,
   PCT_USED          NUMBER,
   MAX               NUMBER
);

--CREATE PROCEDURE



CREATE OR REPLACE PROCEDURE PROC_GATHER_TABLESPACE
IS
   V_DATE_HIST         DBA_TABLESPACE_HIST.DATE_HIST%TYPE;
   V_TABLESPACE_NAME   DBA_TABLESPACE_HIST.TABLESPACE_NAME%TYPE;
   V_STATUS            DBA_TABLESPACE_HIST.STATUS%TYPE;
   V_CONTENTS          DBA_TABLESPACE_HIST.CONTENTS%TYPE;
   V_MEGS_ALLOC        DBA_TABLESPACE_HIST.MEGS_ALLOC%TYPE;
   V_MEGS_FREE         DBA_TABLESPACE_HIST.MEGS_FREE%TYPE;
   V_MEGS_USED         DBA_TABLESPACE_HIST.MEGS_USED%TYPE;
   V_PCT_FREE          DBA_TABLESPACE_HIST.PCT_FREE%TYPE;
   V_PCT_USED          DBA_TABLESPACE_HIST.PCT_USED%TYPE;
   V_MAX               DBA_TABLESPACE_HIST.MAX%TYPE;

   CURSOR C_GATHER
   IS
        SELECT TS.TABLESPACE_NAME,
               TS.STATUS,
               TS.CONTENTS,
               SIZE_INFO.MEGS_ALLOC,
               SIZE_INFO.MEGS_FREE,
               SIZE_INFO.MEGS_USED,
               SIZE_INFO.PCT_FREE,
               SIZE_INFO.PCT_USED,
               SIZE_INFO.MAX
          FROM (SELECT A.TABLESPACE_NAME,
                       ROUND (A.BYTES_ALLOC / 1024 / 1024) MEGS_ALLOC,
                       ROUND (NVL (B.BYTES_FREE, 0) / 1024 / 1024) MEGS_FREE,
                       ROUND (
                          (A.BYTES_ALLOC - NVL (B.BYTES_FREE, 0)) / 1024 / 1024)
                          MEGS_USED,
                       ROUND ( (NVL (B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100)
                          PCT_FREE,
                         100
                       - ROUND ( (NVL (B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100)
                          PCT_USED,
                       ROUND (MAXBYTES / 1048576) MAX
                  FROM (  SELECT F.TABLESPACE_NAME,
                                 SUM (F.BYTES) BYTES_ALLOC,
                                 SUM (
                                    DECODE (F.AUTOEXTENSIBLE,
                                            'YES', F.MAXBYTES,
                                            'NO', F.BYTES))
                                    MAXBYTES
                            FROM DBA_DATA_FILES F
                        GROUP BY TABLESPACE_NAME) A,
                       (  SELECT F.TABLESPACE_NAME, SUM (F.BYTES) BYTES_FREE
                            FROM DBA_FREE_SPACE F
                        GROUP BY TABLESPACE_NAME) B
                 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
                UNION ALL
                  SELECT H.TABLESPACE_NAME,
                         ROUND (SUM (H.BYTES_FREE + H.BYTES_USED) / 1048576)
                            MEGS_ALLOC,
                         ROUND (
                              SUM (
                                   (H.BYTES_FREE + H.BYTES_USED)
                                 - NVL (P.BYTES_USED, 0))
                            / 1048576)
                            MEGS_FREE,
                         ROUND (SUM (NVL (P.BYTES_USED, 0)) / 1048576) MEGS_USED,
                         ROUND (
                              (  SUM (
                                      (H.BYTES_FREE + H.BYTES_USED)
                                    - NVL (P.BYTES_USED, 0))
                               / SUM (H.BYTES_USED + H.BYTES_FREE))
                            * 100)
                            PCT_FREE,
                           100
                         - ROUND (
                                (  SUM (
                                        (H.BYTES_FREE + H.BYTES_USED)
                                      - NVL (P.BYTES_USED, 0))
                                 / SUM (H.BYTES_USED + H.BYTES_FREE))
                              * 100)
                            PCT_USED,
                         ROUND (SUM (F.MAXBYTES) / 1048576) MAX
                    FROM SYS.V_$TEMP_SPACE_HEADER H,
                         SYS.V_$TEMP_EXTENT_POOL P,
                         DBA_TEMP_FILES F
                   WHERE     P.FILE_ID(+) = H.FILE_ID
                         AND P.TABLESPACE_NAME(+) = H.TABLESPACE_NAME
                         AND F.FILE_ID = H.FILE_ID
                         AND F.TABLESPACE_NAME = H.TABLESPACE_NAME
                GROUP BY H.TABLESPACE_NAME) SIZE_INFO,
               SYS.DBA_TABLESPACES TS
         WHERE TS.TABLESPACE_NAME = SIZE_INFO.TABLESPACE_NAME
      ORDER BY TABLESPACE_NAME;

BEGIN
   OPEN C_GATHER;

   LOOP
      FETCH C_GATHER
         INTO V_TABLESPACE_NAME,
              V_STATUS,
              V_CONTENTS,
              V_MEGS_ALLOC,
              V_MEGS_FREE,
              V_MEGS_USED,
              V_PCT_FREE,
              V_PCT_USED,
              V_MAX;

      EXIT WHEN C_GATHER%NOTFOUND;

      INSERT INTO DBA_TABLESPACE_HIST
           VALUES (SYSDATE,
                   V_TABLESPACE_NAME,
                   V_STATUS,
                   V_CONTENTS,
                   V_MEGS_ALLOC,
                   V_MEGS_FREE,
                   V_MEGS_USED,
                   V_PCT_FREE,
                   V_PCT_USED,
                   V_MAX);

      COMMIT;
   END LOOP;

   CLOSE C_GATHER;
END;
/

--CREATE JOB RUN DAILY AT 6 AM --

BEGIN
   SYS.DBMS_SCHEDULER.CREATE_JOB (
      job_name          => 'SYS.JOB_GATHER_TABLESPACE',
      start_date        => NULL,
      repeat_interval   => 'FREQ=DAILY;INTERVAL=1;BYHOUR=6;BYMINUTE=0;BYSECOND=0',
      end_date          => NULL,
      job_class         => 'DEFAULT_JOB_CLASS',
      job_type          => 'STORED_PROCEDURE',
      job_action        => 'SYS.PROC_GATHER_TABLESPACE',
      comments          => NULL);
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_GATHER_TABLESPACE',
                                     attribute   => 'RESTARTABLE',
                                     VALUE       => FALSE);
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
      name        => 'SYS.JOB_GATHER_TABLESPACE',
      attribute   => 'MAX_FAILURES');
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
      name        => 'SYS.JOB_GATHER_TABLESPACE',
      attribute   => 'MAX_RUNS');

   BEGIN
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_GATHER_TABLESPACE',
                                        attribute   => 'STOP_ON_WINDOW_CLOSE',
                                        VALUE       => FALSE);
   EXCEPTION
      -- could fail if program is of type EXECUTABLE...
      WHEN OTHERS
      THEN
         NULL;
   END;

   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_GATHER_TABLESPACE',
                                     attribute   => 'JOB_PRIORITY',
                                     VALUE       => 3);
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
      name        => 'SYS.JOB_GATHER_TABLESPACE',
      attribute   => 'SCHEDULE_LIMIT');
   SYS.DBMS_SCHEDULER.SET_ATTRIBUTE (name        => 'SYS.JOB_GATHER_TABLESPACE',
                                     attribute   => 'AUTO_DROP',
                                     VALUE       => FALSE);
   SYS.DBMS_SCHEDULER.ENABLE (name => 'SYS.JOB_GATHER_TABLESPACE');
END;
/




BEGIN
  SYS.DBMS_SCHEDULER.RUN_JOB
    (job_name             => 'SYS.JOB_GATHER_TABLESPACE'
    ,use_current_session  => FALSE);
END;

/
--select full table--

select * from sys.DBA_TABLESPACE_HIST;

--select by date--

SELECT *
  FROM SYS.DBA_TABLESPACE_HIST
 WHERE TO_CHAR (TO_DATE (DATE_HIST, 'dd-mm-yyyy')) = '15-AUG-17';

--Sum by Date not including UNDO,TEMP, SYS%--

  SELECT TO_CHAR (TRUNC (DATE_HIST), 'DD-MM-YYYY') "Date",SUM (MEGS_USED) "Total_Used(MB)"
    FROM SYS.DBA_TABLESPACE_HIST
   WHERE    TABLESPACE_NAME NOT LIKE 'UNDO%'
         OR TABLESPACE_NAME NOT LIKE 'TEMP%'
         OR TABLESPACE_NAME NOT LIKE 'SYS%'
GROUP BY TRUNC (DATE_HIST)
ORDER BY TRUNC (DATE_HIST) ASC;