Purge the Shared Pool in Oracle | DBMS_SHARED_POOL vs ALTER SYSTEM FLUSH SHARED_POOL

The Shared Pool is a key component of the Oracle SGA (System Global Area) that caches parsed SQL, PL/SQL code, and data dictionary information. Over time, it may accumulate obsolete or invalid objects, leading to performance degradation or “ORA-04031” errors.

In this post, we will discuss two methods to clear or refresh the Shared Pool — either entirely or selectively — using ALTER SYSTEM FLUSH SHARED_POOL and DBMS_SHARED_POOL.PURGE.

1. What Is the Shared Pool?

The Shared Pool contains:

• Library Cache — parsed SQL and PL/SQL statements
• Dictionary Cache — metadata from the data dictionary
• Session and control structures

Heavy DML operations, frequent code recompilation, or dynamic SQL can cause fragmentation or invalid objects in the Shared Pool, impacting parsing efficiency and causing memory allocation errors.

2. Flushing the Entire Shared Pool

To clear all cached SQL and PL/SQL objects from memory, use the following command:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

Note: This operation clears the entire Shared Pool and forces Oracle to reparse all SQL statements. It may cause temporary performance degradation, so use it only during maintenance or troubleshooting windows.

When to Use:

• After major schema changes or deployments
• To resolve ORA-04031 errors related to memory fragmentation
• When performance issues are traced to a corrupted Shared Pool

3. Purging Specific Objects Using DBMS_SHARED_POOL

Instead of flushing everything, you can selectively purge specific PL/SQL packages, types, triggers, or cursors using the DBMS_SHARED_POOL package.

Grant the required privilege:

SQL> GRANT EXECUTE ON DBMS_SHARED_POOL TO your_user;

Example 1: Purge a PL/SQL Package

SQL> EXEC DBMS_SHARED_POOL.PURGE('HR.EMP_BONUS_PKG','P');

Example 2: Purge a Cursor or SQL Statement

First, find the SQL in the library cache:

SQL> SELECT ADDRESS, HASH_VALUE, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%EMPLOYEES%';

Then purge using the address and hash value:

SQL> EXEC DBMS_SHARED_POOL.PURGE('00000007FA2E3D790,1670346623','C');

4. Object Type Flags

• P — Package or Package Body
• T — Type or Type Body
• R — Trigger
• C — Cursor / SQL Statement
• Q — Sequence

5. Comparing the Two Methods

Command	Scope	Impact	Recommended Use
ALTER SYSTEM FLUSH SHARED_POOL	Entire Shared Pool	High	For major resets or memory corruption issues
DBMS_SHARED_POOL.PURGE	Specific objects	Low	Routine maintenance, selective cleanup

6. Example Scenario

Suppose the package APPS.SALARY_PKG was recompiled, but users still see old logic executing. Instead of flushing the entire pool, you can simply purge that package:

SQL> EXEC DBMS_SHARED_POOL.PURGE('APPS.SALARY_PKG','P');

This forces Oracle to reload the package from the data dictionary on the next call, ensuring the latest version is executed.

7. Best Practices

• Prefer selective purging using DBMS_SHARED_POOL
• Use ALTER SYSTEM FLUSH SHARED_POOL only during controlled maintenance
• Monitor V$SGASTAT and V$LIBRARYCACHE before and after purging
• Avoid frequent Shared Pool flushes in production

Conclusion

Clearing the Shared Pool is sometimes necessary but should be done carefully. The DBMS_SHARED_POOL package provides fine-grained control, while ALTER SYSTEM FLUSH SHARED_POOL performs a full reset. Always choose the least disruptive method based on your situation.

Oracle documentation reference: Oracle Database Reference

Please feel free to ask if you have any questions. Thank you 🙂

Toufique Khan