ORA-00904 DBMS_XPLAN HINT DBMS_LOB GRANT

I ran this query with a hint:

SQL> select /*+ full(my_tables) */ blocks
  2  from my_tables
  3  where
  4  owner = 'SYS' and
  5  table_name = 'TAB$';

    BLOCKS
----------
      1625

I ran this select to get the plan:

select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

I was getting this error:

Column Projection Information (identified by operation id):
 1 - "BLOCKS"[NUMBER,22]
 ORA-00904: : invalid identifier

I found that my user or public needed an execute grant for DBMS_LOB to fix this:

SQL> grant execute on DBMS_LOB to PUBLIC;

Grant succeeded.

I am not sure why this grant was not in place on this database but it took a while to figure this out so I thought I would put it out there. I found the error in a trace and I suspected the issue was due to permissions. The trace was like:

PARSE ERROR ... err=904
 SELECT ST.* FROM XMLTABLE('/hint_usage/... DBMS_LOB.SUBSTR...

So that gave me the idea that I needed an execute grant on DBMS_LOB. EXECUTE ANY PROCEDURE did not do it.

After the grant it shows the hint report. This is on 19c:

Column Projection Information (identified by operation id):
 1 - "BLOCKS"[NUMBER,22]
 Hint Report (identified by...
 Total hints for statement: 1
 1 -  SEL$1 / MY_TABLES@SEL$1
            -  full(my_tables)

Bobby

P.S. Full log of the script that got the error:

Full log of the working script:

Full length trace lines:

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to ORA-00904 DBMS_XPLAN HINT DBMS_LOB GRANT

  1. Tim N. says:

    The real mystery is how the grant was revoked.

    Tim..
    =====
    oracle@dba1:1950 [/app/oracle]
    $ echo $ORACLE_HOME
    /app/oracle/base/product/1950/db_1
    oracle@dba1:1950 [/app/oracle]
    $ grep -i ‘grant.*execute.*dbms_lob’ $ORACLE_HOME/rdbms/admin/* 2>/dev/null
    /app/oracle/base/product/1950/db_1/rdbms/admin/dbmslob.sql:GRANT EXECUTE ON dbms_lob TO PUBLIC
    /app/oracle/base/product/1950/db_1/rdbms/admin/dbmslobu.sql:GRANT EXECUTE ON dbms_lobutil_inode_t TO PUBLIC;
    /app/oracle/base/product/1950/db_1/rdbms/admin/dbmslobu.sql:GRANT EXECUTE ON dbms_lobutil_lobmap_t TO PUBLIC;
    /app/oracle/base/product/1950/db_1/rdbms/admin/dbmslobu.sql:GRANT EXECUTE ON dbms_lobutil_lobextent_t TO PUBLIC;
    /app/oracle/base/product/1950/db_1/rdbms/admin/dbmslobu.sql:GRANT EXECUTE ON dbms_lobutil_lobextents_t TO PUBLIC;
    /app/oracle/base/product/1950/db_1/rdbms/admin/dbmslobu.sql:GRANT EXECUTE ON dbms_lobutil_dedupset_t TO PUBLIC;
    /app/oracle/base/product/1950/db_1/rdbms/admin/dbmslobu.sql:GRANT EXECUTE ON dbms_lobutil TO PUBLIC;
    /app/oracle/base/product/1950/db_1/rdbms/admin/owmr1120.plb:grant execute on sys.dbms_lob to wmsys with grant option ;
    /app/oracle/base/product/1950/db_1/rdbms/admin/prvtpspi.plb:grant execute on dbms_lob_am_private to dbfs_role;
    oracle@dba1:1950 [/app/oracle]
    $ grep -i ‘@dbmslob’ $ORACLE_HOME/rdbms/admin/* 2>/dev/null
    /app/oracle/base/product/1950/db_1/rdbms/admin/catpdbms.sql:@@dbmslobu.sql
    /app/oracle/base/product/1950/db_1/rdbms/admin/catpstrt.sql:@@dbmslob.sql
    oracle@dba1:1950 [/app/oracle]
    $ grep -i ‘@catpdbms’ $ORACLE_HOME/rdbms/admin/* 2>/dev/null
    /app/oracle/base/product/1950/db_1/rdbms/admin/catproc.sql:@@catpdbms.sql –CATFILE -X
    oracle@dba1:1950 [/app/oracle]
    $ grep -i ‘revoke.*dbms_lob’ $ORACLE_HOME/rdbms/admin/* 2>/dev/null
    /app/oracle/base/product/1950/db_1/rdbms/admin/e18.sql:revoke execute on dbms_lob_am_private from dbfs_role;
    oracle@dba1:1950 [/app/oracle]
    $

  2. Bobby says:

    Thanks for your comment with all the details. I am not sure how it got changed on this one database. It seems fine on others that I checked. At least I have it documented if it crops up again.

    Thanks again,
    Bobby

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.