Flushing the SGA

<< Oracle 8i coexisting with 10g | Journals | Detecting Broken DBMS_JOBS >>

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;/