ALL YOU WANT TO KNOW ABOUT ORACLE STATSPACK

SUBMIT A STATPACK JOB TO RUN IN EVERY 15 MINUTES

##################################################

variable jobno number;

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'))/15)+1)*15)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/15)+1)*15)/(24*60)', TRUE, :instno);

COMMIT;

END;

/

List scheduled jobs

#############################################

set lines 100 pages 999

col schema_user format a15

col fails format 999

select job

, schema_user

, to_char(last_date, 'hh24:mi dd/mm/yy') last_run

, to_char(next_date, 'hh24:mi dd/mm/yy') next_run

, failures fails

, broken

, substr(what, 1, 15) what

from dba_jobs

order by 4

/

Remove a job

#####################################

You need to be connected as the user who owns the job

exec dbms_job.remove();

Delete one or more snapshots

#####################################

@?/rdbms/admin/sppurge;

Generate a report

####################################

@?/rdbms/admin/spreport.sql

List snapshots

####################################

col "Date/Time" format a30

select snap_id

, snap_level

, to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Date/Time"

from stats$snapshot

, v$database

order by snap_id

/

To see the job:

#######################################

select job

, what

from dba_jobs

/

Schedule and hourly snapshot

########################################

@?/rdbms/admin/spauto.sql

Uninstall statspack

########################################

@?/rdbms/admin/spdrop

Or to specify a level...

############################################

exec statspack.snap(i_snap_level => 7, i_modify_parameter => 'true');

Run Statspack Snapshot Every 30 Minutes Starting at the Next 30 Minute Interval

##################################################################################

variable jobno number;

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;

END;

/

Run Statspack Snapshot Every 1 Hour

#################################################################

variable jobno number;

variable instno number;

BEGIN

SELECT instance_number INTO :instno FROM v$instance;

DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', TRUNC(sysdate+1/24,'HH'), 'TRUNC(SYSDATE+1/24,''HH'')', TRUE, :instno);

COMMIT;

END;

/

Run Statspack Snapshot Every 5 Minutes Starting at the Next 5 Minute Interval

###############################################################################

variable jobno number;

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'))/5)+1)*5)/(24*60),

'trunc(sysdate,''HH24'')+

((floor(to_number(to_char(sysdate,''MI''))/5)+1)*5)/(24*60)',

TRUE, :instno);

COMMIT;

END;

/

DBMS_JOB / Every 15 Minutes from Monday to Friday, Between 6 a.m. and 6 p.m.

##############################################################################

SQL> ALTER SESSION SET nls_date_format = '(DY) MON DD, YYYY HH24:MI';

Session altered.

SQL> SELECT

sysdate

, CASE

WHEN ( TO_CHAR(SYSDATE, 'HH24') BETWEEN 6 AND 17

AND

TO_CHAR(SYSDATE, 'DY') NOT IN ('SAT','SUN')

)

THEN TRUNC(sysdate) +

(TRUNC(TO_CHAR(sysdate,'sssss')/900)+1)*15/24/60

WHEN (TO_CHAR(sysdate, 'DY') NOT IN ('FRI','SAT','SUN'))

THEN TRUNC(sysdate)+1+6/24

ELSE next_day(trunc(sysdate), 'Mon') + 6/24

END interval_date

FROM dual;

#####################

You can use instruct Oracle functions to determine any date in the future:

Date / Time Math Time Description

WHERE (date) > sysdate - 7/24; Past 7 hours

WHERE (date) > sysdate - 7; Past 7 days

WHERE (date) > sysdate - 7/1440; Past 7 minutes

7/24

13/44 7 hours

13 hours

1/24/60/60

7/24/60/60 One second

Seven seconds

1/24/60

5/24/60 One minute

Five minutes

1/24

5/24 One hour

Five hours

TRUNC(SYSDATE+1/24,'HH') Every one hour starting with the next hour