This is a tad complex. Oracle 11.2.0.4 on all databases.
ORDS server pool runs APEX code in the local master database. This code (for specific reporting) connects to a remote master database (commercial app db) for selecting from a (parameterised) view.. So far, so good. No problems.
The remote master database has my custom parameterised views. These views are on pipelines I created. The PL/SQL pipeline code uses the parameters received, to determine what remote slave database to query (using the commercial app's config tables). It then uses a ref cursor (dynamic SQL) to run the custom select against a remote slave database. It fetches the rows from the slave database, pipes rows, that are then seen/fetched by the APEX report SQL on the local master database. The remote master database's pipeline code has over 10 remote slave databases to choose from, for running against.
So local master runs "select * from param_view@remote_master". Remote master pipeline for param_view runs "select * from view@remote_slave_5". There are 10+ remote slave databases.
Works very well. Except for sessions on remote master db reporting on the odd occasion "ORA-02020: too many database links in use".
The pipeline code, at the start of its execution, determine which remote slave database to use. It then proceed to close any database links to the other remote slave databases that may exist in that remote master db session.
A close db link fails when there are open cursor handles not yet closed, referencing objects via the db link. But my pipeline code explicitly opens, and explicitly closes, the ref cursor created to query the remote slave database.
Any ideas why the ORA-02020 can be triggered, in such a case?
Troubleshooting this is difficult due to the number of database layers, and the lack of access to X$ views on the remote master database to look at what db links exists and for which sessions.