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)
P.S. Full log of the script that got the error:
Full log of the working script:
Full length trace lines: