Script categories

Friday, 4 November 2011

Set Start and End Snapshot ID for AWR scripts

variable p_Start_Snap_ID  number
variable p_End_Snap_ID    number

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi'
alter session set nls_timestamp_format = 'dd/mm/yyyy hh24:mi'

col Begin_interval_time format a20
col End_interval_time   format a20

set linesize 400
set pagesize 4000

select Snap_ID, Instance_Number, begin_interval_time, end_interval_time
from (
      select snap_id
      from   dba_hist_snapshot
      order by snap_id desc
where rownum < &&Rownum_Limit

prompt Get Start and End snapshot ID

  -- One must use an anonymous block when setting a Bind Variable
  select min(Snap_ID)
        ,max (Snap_ID)
  into  :p_Start_Snap_ID
  (select snap_id
   from dba_hist_snapshot
   order by snap_id desc)
  where rownum < &&Rownum_Limit;


No comments:

Post a Comment