Tuesday, April 16, 2013

DBMS_SHARED_POOL.PURGE

Whitout purging the entire shared pool, single object can be purged from shared pool using DBMS_SHARED_POOL.PURGE.

DBMS_SHARED_POOL.PURGE take 3 arguments.. Name, Flag and Heaps.

Name => select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '%SQL ID...%'

Flag=>

(Optional) If this is not specified, then the package assumes that the first parameter is the name of a package/procedure/function and resolves the name.
Set to 'P' or 'p' to fully specify that the input is the name of a package/procedure/function.
Set to 'T' or 't' to specify that the input is the name of a type.
Set to 'R' or 'r' to specify that the input is the name of a trigger.
Set to 'Q' or 'q' to specify that the input is the name of a sequence.
In case the first argument is a cursor address and hash-value, the parameter should be set to any character except 'P' or 'p' or 'Q' or 'q' or 'R' or 'r' or 'T' or 't'.

Heaps => default seems suitable..

Default is 1, that is, heap 0 which means the whole object would be purged

No comments :

Post a Comment