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
            ,Instance_Number
            ,begin_interval_time
            ,end_interval_time
      from   dba_hist_snapshot
      order by snap_id desc
     )
where rownum < &&Rownum_Limit
/

prompt Get Start and End snapshot ID
begin

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

end;
/

No comments:

Post a Comment