Home » SQL & PL/SQL » SQL & PL/SQL » Access data fom multiple schemas in a single query
Access data fom multiple schemas in a single query [message #300610] Sat, 16 February 2008 13:50 Go to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
I have a database setup such as i have to get data from 200 individual schemas for same table name in each schema and insert the union of all these data into one seprate schema



If i use the select query for each schema like

"select * from table_name@dblink_name" where the dblink_name is dynamic i have to make a union these from these 200 select queries and make final query.But when i use like this i am facing the error "To many databse links in Use".


So how can we access data across different schemas in a single select query.

Re: Access data fom multiple schemas in a single query [message #300615 is a reply to message #300610] Sat, 16 February 2008 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You mean you have to access 200 databases at the same time in the same query?
The likelyhood it ever workeds is little.
Or are they in the SAME database?

Anyway, your design is really weak.

Regards
Michel
Re: Access data fom multiple schemas in a single query [message #300616 is a reply to message #300615] Sat, 16 February 2008 14:50 Go to previous messageGo to next message
cgk1983
Messages: 28
Registered: December 2005
Junior Member
I am working on a finanacial bank database setup i have HOST & BRANCH database setup.

For Ex:- if the bank has 200 branches each branch will have its separate database.

So the design is no problem and we are working on the same setup from years.

Now i need to take data from these 200 branch databases in a single query.
Re: Access data fom multiple schemas in a single query [message #300617 is a reply to message #300616] Sat, 16 February 2008 14:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
ORA-02020: too many database links in use
Cause: The current session has exceeded the INIT.ORA open_links maximum.
Action: Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases.


http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams154.htm#REFRN10138
Re: Access data fom multiple schemas in a single query [message #300621 is a reply to message #300616] Sat, 16 February 2008 15:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now i need to take data from these 200 branch databases in a single query.

What is the likelyhood than 1 out of the 200 databases is not up or not reachable?
If servers are distant, I think it is very likely.
If servers are on same site, I think all branches should be in the same database if not in the same schema.

And yes the design is the problem, the proof is that you have now an error. Opening 200 database links in a single query is just... like make a remote meeting with 200 people all over the world at the same time.

Regards
Michel

[Updated on: Sat, 16 February 2008 15:35]

Report message to a moderator

Re: Access data fom multiple schemas in a single query [message #300675 is a reply to message #300610] Sun, 17 February 2008 14:13 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Do 200 individual queries to copy data to your local schema, then do whatever aggregation you need on that once you have the data. then you can open a single DB link at a time, and code around databases being down.

If there are 200 branches, you can almost bet somewhere there is a warehouse holding all that data. Perhaps you can find that warehouse, and query that?

Alternatively, investigate Oracle replication technologies. THere are many ways to get data from remote sites in Oracle.
Previous Topic: why i cannot do it;PLS-00231" Function 'my_fun' cannot be used in this SQL"
Next Topic: urgent help required(recursive triggers)
Goto Forum:
  


Current Time: Sun Dec 04 18:57:18 CST 2016

Total time taken to generate the page: 0.14651 seconds