0

I have been working on a oracle database query that uses more than 4 database links and I keep getting this error ORA-02020: too many database links in use.

I cannot change the limit and tried using CTEs to counter this but still shows errors. How can I run this now? Are CTEs even helpful?

Improve this question
5
  • Talk to your DBA to increase it. The default value for this is rather low. Ensure DB-Link is closed after you used it.
    – Wernfried Domscheit
    Commented Jul 10 at 5:57
  • Is there a way to close db link within a query? And what about CTEs?
    – Suhaani Batra
    Commented Jul 10 at 6:33
  • No, you cannot. You can close it only after statement was executed.
    – Wernfried Domscheit
    Commented Jul 10 at 6:52
  • 1
    Within a query, no. CTEs are just one of many ways to structure a query but it's all still part of the same query. If you need more than 4 database links in the query, talk to the DBA to increase the limit. Or change the logic so that you don't need that many database links in a single query (i.e. use materialized views to replicate the data locally, pull data into temp tables locally and close the database link, etc.)
    – Justin Cave
    Commented Jul 10 at 6:52
  • Sure. Thanks for the information.
    – Suhaani Batra
    Commented Jul 11 at 6:12

1 Answer 1

Reset to default
1

In Oracle database, the number of database links that can be opened simultaneously in the same session is limited. This limit is controlled by the parameters open_links and open_links_per_instance. If this limit is exceeded, an ORA-02020 error will be thrown.

If it is necessary to open multiple database connections simultaneously, consider increasing the values of the open_links and open_links_per_instance parameters. This requires the permission of a database administrator.

ALTER SYSTEM SET open_links = 10; -- Default value is 4
ALTER SYSTEM SET open_links_per_instance = 10; -- Default value is 4
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.