Re: SELECT from multiple schemas at ones?

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Thu, 10 Jun 2004 20:51:36 GMT
Message-ID: <sR3yc.3965$l42.2133@newssvr24.news.prodigy.com>

Sten Westerback wrote:
> Hi
>
> We have a Oracle database server with a handful of
> schemas with the same set of tables (but different data).
> Each schema is accessible with different account&password.
>
> Is it possible to make a query that would search all the
> tables with same name at ones? Or can you for instance
> make a view that joins the tables together with account
> and passwords included and if so.. is that secure?
>
> - Sten
>
>
>

create database links to the other databases and if the tables are the same (same column names etc...)

then

select ... from dblink1.table1 where .... union
select ... from dblink2.table1 where .... union
select ... from dblink3.table1 where .... union
select ... from dblink4.table1 where .... Received on Thu Jun 10 2004 - 15:51:36 CDT