Home » SQL & PL/SQL » SQL & PL/SQL » how to fetch data from multiple databases
how to fetch data from multiple databases [message #345582] Thu, 04 September 2008 04:39 Go to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
i have 2 or 3 database in which i breakup record every year.
Like i breakup data into 2006-2007 and create new namespace for year 2008-2009 i want to fetch data both databases how it is possible.
Re: how to fetch data from multiple databases [message #345583 is a reply to message #345582] Thu, 04 September 2008 04:40 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Use DBLINKS.

Regards,
Rajat
Re: how to fetch data from multiple databases [message #345585 is a reply to message #345582] Thu, 04 September 2008 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you break data into several databases, above all if you have to select data from several database?

Regards
Michel
Re: how to fetch data from multiple databases [message #345592 is a reply to message #345585] Thu, 04 September 2008 04:50 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
actually we have lot of record each year and database size grown more rapidly which make our application too slow so that's why we break data in to yearly basis
Re: how to fetch data from multiple databases [message #345594 is a reply to message #345592] Thu, 04 September 2008 04:53 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
It Slows down the database or your queries??

Oracle has the capability of handling very large databases.
You must be handling it on your personal computers
No server i think so.

Why don't you go for partitioning.

Regards,
Rajat
Re: how to fetch data from multiple databases [message #345599 is a reply to message #345592] Thu, 04 September 2008 04:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are you sure you actually mean different databases? Don't you mean different tables? Or schemas?
Ever heard of partitioning? If not, google for oracle partition table. You might be in for a big surprise..
Re: how to fetch data from multiple databases [message #345604 is a reply to message #345582] Thu, 04 September 2008 05:05 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
yes its some partition like we create different user every year like.
AX0506 for 2005-2006
AX0708 for 2007-2008
AX0809 for 2008-2009

can u tell me how i use UNION on all three users
Re: how to fetch data from multiple databases [message #345607 is a reply to message #345604] Thu, 04 September 2008 05:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is absolutely no performance benefit to be gained from having these tables owned by different users.

It sounds like you either need to review your indexing strategy to improve performance, or to pay for Partitioning, and do this properly.

Otherwise, you'll need to create a view that does a UNION ALL (note - not UNION) on all the different Schema.Table_names
Re: how to fetch data from multiple databases [message #345611 is a reply to message #345607] Thu, 04 September 2008 05:17 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
SELECT SYMBOL,
SUM(qty) AS Net_qty,
SUM(qty * price) AS Net_Amt,
SUM(DECODE(TYPE,'B',qty)) AS Buy_qty,
SUM(DECODE(TYPE,'B',qty * price)) AS Buy_Amt,
SUM(DECODE(TYPE,'S',qty)) AS Sell_qty,
SUM(DECODE(TYPE,'S',qty * price)) AS Sell_Amt
FROM tran WHERE CLIENT='AHE'
GROUP BY SYMBOL

these db name is AX0809 for 2008-2009 i would like to apply same query on AX0506 for 2005-2006 and AX0708 for 2007-2008 every year username are different like for year 2008-2009 user name is AX0809 and AX0506 for 2005-2006 and AX0708 for 2007-2008
Re: how to fetch data from multiple databases [message #345612 is a reply to message #345611] Thu, 04 September 2008 05:19 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please take the effort to actually read some of the replies..
And read the concepts-guide to see the difference between schemas and databases.
Previous Topic: Cut string and place in a separate Column
Next Topic: Date - Date
Goto Forum:
  


Current Time: Sat Dec 03 10:08:26 CST 2016

Total time taken to generate the page: 0.08083 seconds