Search code examples
oracle-databaseoracle12coracle12.1

Oracle :: identifier 'SYS.DBMS_SHARED_POOL' must be declared

My oracle database version is: 12.1.0.2.0

I'm trying to purge a cursor from share pool using dbms_shared_pool.purge procedure. It works fine if I execute it as standalone command like below -

SQL> exec sys.DBMS_SHARED_POOL.PURGE('0000000BEF761888,1227751471', 'C', 1);

PL/SQL procedure successfully completed.

SQL>

However, if I put this statement inside a procedure & invoke the procedure then it fails.

SQL> create or replace procedure test_sp
is
  s_sql varchar2(2000);
begin
  s_sql := q'[begin sys.DBMS_SHARED_POOL.PURGE('0000000BEF761888,1227751471', 'C', 1); end;]';
  execute immediate s_sql;
end;
/
  2    3    4    5    6    7    8
Procedure created.

SQL>
SQL> show err
No errors.
SQL>

Error:

SQL> exec test_sp;
BEGIN test_sp; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_SHARED_POOL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "ORADBA.TEST_SP", line 6
ORA-06512: at line 1

User executing standalone command & and the one that created & invoked procedure is same. The user has privileges to invoke dbms_share_pool.purge as standalone command executes successfully.

So, why does it fail when the same procedure is invoked via another procedure?

Thanks, Kailash

Solution

  • If the privilege is granted through a role then you won't be able to include the statement within a PL/SQL procedure. You must have the execute privilege on the object granted directly to your user.