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:
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]
$
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