How to flush shared pool?

One can flush the shared pool using the following DDL.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

Using the following one can implement the functionality in a store procedure.
SQL> CREATE OR REPLACE procedure flush_shared_pool IS
v_cur INTEGER;
v_result INTEGER;
BEGIN
v_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur, ‘ALTER SYSTEM FLUSH SHARED_POOL’, DBMS_SQL.NATIVE);
v_result := DBMS_SQL.EXECUTE(v_cur);
DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/
And in special circumstances one can grant execute access “grant execute on sys.flush_shared_pool to SCOTT;”
to this store procedure if there is a need in a development environment.

Leave a Reply