'ORA-00904 invalid identifier' or 'PLS-00201' error for DBMS_LOB

book

Article ID: 160216

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

For any situation that requires LOB tablespace access (new install schema creation, incident modification, etc.), an error occurs pointing to DBMS_LOB as the cause.

One of the following errors will be seen in the appropriate log for the failed situation:

ORA-00904: : invalid identifier

PLS-00201: identifier 'DBMS_LOB' must be declared 

Cause

Errors to access DBMS_LOB can happen as part of an effort to tighten database security.  Because the DBMS_LOB package is owned by sys, DBAs may restrict access to it and other utilities in an effort to reduce hacking risks.

Resolution

From SQLPLUS (logged in as 'SYS as SYSDBA'), run the following query:

grant execute on dbms_lob to protect;