Flushing the SGA

To avoid producing gaps in sequences when flushing the shared pool, it is recommended to keep/pin the sequences before flushing the SGA.

declare   
   v_sqlstmt varchar2(500);
begin   
   for c1 in (select object_name from user_objects
              where object_type='SEQUENCE') loop
       SYS.DBMS_SHARED_POOL.KEEP(c1.object_name,'Q');
   end loop;
   execute immediate 'alter system flush shared_pool';
end;
/