Re: SELECT from multiple schemas at ones?

From: Alan <alan_at_erols.com>
Date: Thu, 10 Jun 2004 14:11:12 -0400
Message-ID: <2irmhvFqkm0uU1@uni-berlin.de>

"Sten Westerback" <sten.westerback_at_NO_SPAMnokia.com> wrote in message news:E2_xc.18696$k4.373403_at_news1.nokia.com...
> 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
>
>
>

First, get yourselves a DBA. Quickly. In the meantime, given the necessary GRANTs, you can create a view using either UNION or UNION ALL (depending on your needs) to connect each SELECT. Something like:

CREATE VIEW my_view AS
SELECT * FROM
my_schema.first_table
UNION ALL
SELECT * FROM next_schema.first_table
UNION ALL
etc...
; Received on Thu Jun 10 2004 - 13:11:12 CDT