ERROR: no SELECT privilege on V$SQL in the remote database

[Sanket-Terex]

Hey @laurenz ,

I hope you’re doing well!🙂

We recently updated our AWS Aurora PostgreSQL database from version 14 to version 16. Along with the update, the respective extensions were also upgraded to compatible versions. For example, the "oracle_fdw" extension is now at version 2.6.0, as shown below:

_SELECT oraclediag(); _oraclefdw 2.6.0, PostgreSQL 16.3, Oracle client 21.9.0.0.0

After this update, the FDW that was working fine with "oracle_fdw 2.5.0, PostgreSQL 14.9, Oracle client 21.9.0.0.0" suddenly stopped functioning and started throwing the error specified below. There have been no changes to the FDW, Oracle user mappings in the ADG server, or any other configurations; everything remains identical to the previous version.

Could you help us understand why we started encountering this V$SQL-related error after the update and suggest possible actions we can take to resolve this issue?

Error: SQL Error [HV00L]: ERROR: no SELECT privilege on V$SQL in the remote database Detail: ORA-00942: table or view does not exist

Below is the FDW configuration in PostgreSQL format: _CREATE FOREIGN TABLE public.test_fdw ( id varchar NULL, item_description varchar NULL, "LOCATION" varchar NULL, org_id int4 NULL, facility_id int4 NULL, item_id int4 NULL, value_stream_id int4 NULL, value_stream varchar NULL, pack_qty float8 NULL, container varchar NULL, combined_containers varchar NULL, supplier_name varchar NULL, "3PL" varchar NULL, buyer varchar NULL, moq int4 NULL, std_cost float8 NULL, weight float8 NULL, netweight float8 NULL, uom varchar NULL ) SERVER adg OPTIONS (table '( -- Source Query )');

[laurenz]

Hm, that is mysterious. The error means that the Oracle user defined in the user mapping has no permissions to select from the Oracle view V$SQL. But that is something that should not change if you upgrade the PostgreSQL database server or the oracle_fdw software. On the other hand, I don't know how exactly Amazon performs upgrades; perhaps something went wrong there.

Can you use sqlplus to connect to the Oracle database with the user specified in the PostgreSQL user mapping and check if it can select from V$SQL or not?

At any rate, the solution is to give the Oracle user the required permissions in Oracle.

[mellerbeck]

Hm, that is mysterious. The error means that the Oracle user defined in the user mapping has no permissions to select from the Oracle view V$SQL. But that is something that should not change if you upgrade the PostgreSQL database server or the oracle_fdw software. On the other hand, I don't know how exactly Amazon performs upgrades; perhaps something went wrong there.

Can you use sqlplus to connect to the Oracle database with the user specified in the PostgreSQL user mapping and check if it can select from V$SQL or not?

At any rate, the solution is to give the Oracle user the required permissions in Oracle.

It is mysterious!

I checked and the user cannot query V$SQL, is this something that has been added between version 2.5 and 2.6 ? This is not happening with every Foreign Table after the upgrade. I haven't been able to figure out why one works and another throws the ERROR: no SELECT privilege on V$SQL in the remote database.

For example this one works

CREATE FOREIGN TABLE operationsactuals.tms_wip_completions (
    serial_number varchar NULL,
    site_id int4 NULL,
    transaction_date_utc timestamptz NULL,
    transaction_type_id int4 NULL
)
SERVER my_server
OPTIONS (prefetch '500', table '(SELECT
    mut.SERIAL_NUMBER, 
    mmt.ORGANIZATION_ID, 
    CAST(mmt.TRANSACTION_DATE AS TIMESTAMP) AS transaction_date_utc, 
    mmt.TRANSACTION_TYPE_ID
FROM inv.MTL_MATERIAL_TRANSACTIONS mmt 
INNER JOIN inv.MTL_UNIT_TRANSACTIONS mut ON mut.TRANSACTION_ID = mmt.TRANSACTION_ID AND mut.ORGANIZATION_ID = mmt.ORGANIZATION_ID 
WHERE mmt.TRANSACTION_TYPE_ID = 44 -- WIP Completions ONLY
AND mmt.TRANSACTION_DATE >= SYSDATE - 3
AND mmt.ORGANIZATION_ID IN (1,2,3,4,5,6)
ORDER BY mut.TRANSACTION_DATE DESC
)');

But this one does not

CREATE FOREIGN TABLE operationsactuals.tms_wip_discrete_jobs (
    wip_entity_name varchar NULL,
    description varchar NULL,
    organization_id int4 NULL
)
SERVER my_server
OPTIONS (prefetch '500', table '(SELECT
    wdj.WIP_ENTITY_NAME, 
    wdj.DESCRIPTION, 
    wdj.ORGANIZATION_ID
FROM APPS.WIP_DISCRETE_JOBS_V wdj 
WHERE wdj.STATUS_TYPE_DISP = 'Released'
  AND wdj.DESCRIPTION IS NOT NULL
ORDER BY wdj.CREATION_DATE DESC
)');

Maybe something to do with data volume?

[laurenz]

This has not changed recently.

Note that this error should only happen if you use EXPLAIN (VERBOSE), which displays the execution plan. Nothing else will query V$SQL. Perhaps you have auto_explain enabled...

Did you verify that the Oracle user cannot query V$SQL? If yes, that is the problem. You have to solve that inside Oracle.

[mellerbeck]

Also, FWIW a select with a where clause works just fine. Its only the select * that causes the issue

select * from operationsactuals.tms_wip_discrete_jobs where wip_entity_name = '6700833'

Actually, now that I check

Selecting with the column names does work

select wip_entity_name,description,organization_id from operationsactuals.tms_wip_discrete_jobs

Is there a clue there?

[laurenz]

The only clue I have is in my previous comment: if you are using auto_explain, PostgreSQL will log the execution plan of long-running statements. That could select from V$SQL. Check if chared_preload_libraries contains auto_explain. If yes, check auto_explain.log_min_duration. Perhaps the problem only happens with queries that have a runtime that exceeds that value...

[Sanket-Terex]

Yes, we do have "auto_explain" in the "shared_preload_libraries", and the "log_min_duration" is set to "5s".

Also, for your information, "log_verbose" is set to "on".

[laurenz]

Right, that's the problem then. Either allow the Oracle user to select from V$SQL or disable auto_explain.log_verbose.

[mellerbeck]

Right, that's the problem then. Either allow the Oracle user to select from V$SQL or disable auto_explain.log_verbose.

Thank you! I couldn't figure out why the explain was being triggered, but duh we turned on auto_explain. Disabling auto_explain.log_verbose does the trick, thank you very, very much!