3

I'm using Oracle SQL Developer version 4.02.15.21.

I need to write a query that accesses multiple databases. All that I'm trying to do is get a list of all the IDs present in "TableX" (There is an instance of Table1 in each of these databases, but with different values) in each database and union all of the results together into one big list.

My problem comes with accessing more than 4 databases -- I get this error: ORA-02020: too many database links in use. I cannot change the INIT.ORA file's open_links maximum limit.

So I've tried dynamically opening/closing these links: 

SELECT Local.PUID FROM TableX Local
UNION ALL
----
SELECT Xdb1.PUID FROM TableX@db1 Xdb1;
ALTER SESSION CLOSE DATABASE LINK db1
UNION ALL
----
SELECT Xdb2.PUID FROM TableX@db2 Xdb2;
ALTER SESSION CLOSE DATABASE LINK db2
UNION ALL
----
SELECT Xdb3.PUID FROM TableX@db3 Xdb3;
ALTER SESSION CLOSE DATABASE LINK db3
UNION ALL
----
SELECT Xdb4.PUID FROM TableX@db4 Xdb4;
ALTER SESSION CLOSE DATABASE LINK db4
UNION ALL
----
SELECT Xdb5.PUID FROM TableX@db5 Xdb5;
ALTER SESSION CLOSE DATABASE LINK db5

However this produces 'ORA-02081: database link is not open.' On whichever db is being closed out last.

Can someone please suggest an alternative or adjustment to the above?

Please provide a small sample of your suggestion with syntactically correct SQL if possible.

Improve this question
6
  • 1
    docs.oracle.com/cd/B28359_01/server.111/b28310/… (Default is 4 so limit union to 4.. see if you're hitting a default ) and stackoverflow.com/questions/10015130/…. Commented Jul 7, 2014 at 17:48
  • 4
    That can't be the SQL you are running, because there is no way to embed an ALTER SESSION inside a SELECT statement Commented Jul 7, 2014 at 17:48
  • not much used to this but i tried search engine -- **search : : ORA-02020: too many database links in use ** and got results that look good . try it . not much used to oracle Commented Jul 7, 2014 at 17:53
  • Not sure how viable this is for your situation. You could create a temp table, add an insert into tempTable for each of your select statements. Then select * from tempTable. Commented Jul 7, 2014 at 18:03
  • 1
    user3801932 Your &Username comments won't notify the people you are responding to. Only @Username will. You can only include one of those per comment. Also; please be polite in your comments. @Srinath was just trying to help. Commented Jul 7, 2014 at 19:04

1 Answer 1

Reset to default
3

If you can't change the open_links setting, you cannot have a single query that selects from all the databases you want to query.

If your requirement is to query a large number of databases via database links, it seems highly reasonable to change the open_links setting. If you have one set of people telling you that you need to do X (query data from a large number of tables) and another set of people telling you that you cannot do X, it almost always makes sense to have those two sets of people talk and figure out which imperative wins.

If we can solve the problem without writing a single query, then you have options. You can write a bit of PL/SQL, for example, that selects the data from each table in turn and does something with it. Depending on the number of database links involved, it may make sense to write a loop that generates a dynamic SQL statement for each database link, executes the SQL, and then closes the database link.

If you want need to provide a user with the ability to run a single query that returns all the data, you can write a pipelined table function that implements this sort of loop with dynamic SQL and then let the user query the pipelined table function. This isn't really a single query that fetches the data from all the tables. But it is as close as you're likely to get without modifying the open_links limit.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you for the answer! I had definitely thought there would be a way to open/close links in an easy fashion. Unfortunately I cannot change the open_links limit but I will look into your suggestion +1 helpful
Are you sure there is no way to just close links as you go? It just seems like that would be the logical solution...
@user3801932 - To close them as you go within a single SQL statement? No, that's not possible. You can close them as you go if you're writing procedural PL/SQL because there is an order of operations. The order that the optimizer chooses to execute SQL is indeterminant. Behind the scenes, if you want 1 query to hit 9 databases, it's likely going to need to do prepare a SQL statement on all 9 databases (to do things like validating syntax and data types) before executing any of them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.