Friday, August 30, 2019

PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared


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>


2 comments: