To declare the DBMS_SHARED_POOL.PURGE you have to run dbmspool.sql under ORACLE_HOME/rdbms/admin/ directory
SQL> exec DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C');
BEGIN DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
[oracle@localhost admin]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 30 16:55:58 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> @?/rdbms/admin/dbmspool.sql
Session altered.
Package created.
Grant succeeded.
Session altered.
SQL>
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';
ADDRESS HASH_VALUE
---------------- ----------
0000000064E29800 1689401402
SQL> exec DBMS_SHARED_POOL.PURGE ('0000000064E29800,1689401402','C');
PL/SQL procedure successfully completed.
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='c99yw1xkb4f1u';
no rows selected
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
Thanks!! worked smoothly
ReplyDeleteThank you Sai
ReplyDelete