Oracle Spin – Flimatech Blog

Sharing Our Database Experience

How to flush shared pool?

Posted by Amin Jaffer on May 2, 2009

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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 39 other followers

%d bloggers like this: