Schedule snapshot collection in Statspack reports

Schedule snapshot collection in Statspack reports

select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
 "Date/Time" from stats$snapshot,v$database order by snap_id desc; 

NAME         SNAP_ID Date/Time
--------- ---------- -------------------
ORCL1            111 31.01.2014:14:00:31
ORCL1            112 31.01.2014:14:46:55


TO generate adhoc or immediate snapshot, run the below command :
SQL> EXEC statspack.snap;

PL/SQL procedure successfully completed.

The collection of system snapshots can be automated with the DBMS_JOB package. 
The spauto.sql script can be used to schedule system snapshot collections on the hour, every hour.

SQL> @?/rdbms/admin/spauto.sql

PL/SQL procedure successfully completed.


Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

     JOBNO
----------
         4


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

       JOB NEXT_DATE NEXT_SEC
---------- --------- --------------------------------
         4 10-JUN-14 06:00:00


SQL> !date
Tue Jun 10 05:59:00 CEST 2014

SQL> set time on;
05:59:40 SQL> set linesize 130;
col what for a30;
col log_user for a12;
col interval for a30;

select job,what,log_user,
to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "Sysdate",
to_char(next_date,'dd-mon-yyyy hh24:mi:ss') "Next Date",
interval
from user_jobs;

       JOB WHAT                           LOG_USER     Sysdate                       Next Date
---------- ------------------------------ ------------ ----------------------------- -----------------------------
INTERVAL
------------------------------
         4 statspack.snap;                SYS          10-jun-2014 06:00:06          10-jun-2014 06:00:00
trunc(SYSDATE+1/24,'HH')


06:00:06 SQL> variable jobno number;
06:00:30 SQL> variable instno number;
 BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60), 'trunc
(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/30)+1)*30)/(24*60)', TRUE, :instno);
  COMMIT;
/

PL/SQL procedure successfully completed.

 set linesize 130;
col what for a30;
col log_user for a12;
col interval for a30;
select job,what,log_user,
to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "Sysdate",
to_char(next_date,'dd-mon-yyyy hh24:mi:ss') "Next Date",
interval
from user_jobs;

       JOB WHAT                           LOG_USER     Sysdate                       Next Date
---------- ------------------------------ ------------ ----------------------------- -----------------------------
INTERVAL
------------------------------
         5 statspack.snap;                SYS          10-jun-2014 06:00:43          10-jun-2014 06:30:00
trunc(sysdate,'HH24')+((floor(
to_number(to_char(sysdate,'MI'
))/30)+1)*30)/(24*60)

         4 statspack.snap;                SYS          10-jun-2014 06:00:43          10-jun-2014 07:00:00
trunc(SYSDATE+1/24,'HH')


06:00:43 SQL> execute dbms_job.remove(4);

PL/SQL procedure successfully completed.

06:01:07 SQL> set linesize 130;
col what for a30;
col log_user for a12;
col interval for a30;
select job,what,log_user,
to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "Sysdate",
to_char(next_date,'dd-mon-yyyy hh24:mi:ss') "Next Date",
interval
from user_jobs;

       JOB WHAT                           LOG_USER     Sysdate                       Next Date
---------- ------------------------------ ------------ ----------------------------- -----------------------------
INTERVAL
------------------------------
         5 statspack.snap;                SYS          10-jun-2014 06:01:15          10-jun-2014 06:30:00
trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60)

To remove a job

EXEC DBMS_JOB.REMOVE(2);


Setting a job to run every hour on the 1/2 hour
SQL> exec dbms_job.interval(4, 'TRUNC(SYSDATE, ''HH'')+(1.5/24)');
SQL> commit;

or
exec dbms_job.change(2, null, null, 'SYSDATE + 30/1440');


to check the interval set

 ALTER SESSION SET NLS_DATE_FORMAT = 'HH24:MI:SS';
SQL> select  SYSDATE + 30/1440 from dual;

SYSDATE+
--------
08:50:26
SQL> select  trunc(SYSDATE+1/24,'HH') from dual;

TRUNC(SY
--------
09:00:00
SQL>