Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SELECT from multiple schemas at ones?

Re: SELECT from multiple schemas at ones?

From: Daniel Roy <danielroy10junk_at_hotmail.com>
Date: 10 Jun 2004 12:31:08 -0700
Message-ID: <3722db.0406101131.34d0422b@posting.google.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?
>
 

In order to see tables from different tables, you'll need to define database links, which can be "public" or "private" (read docs for details). The username/password are not stored in the view. They are stored with the database link. But note that, according to Oracle, "If you omit this clause, the database link uses the username and password of each user who is connected to the database" (while describing the "identified by" clause of the create link statement).

If you'd like to automate the generation of the queries depending on the fact that the same table names exist in various databases, you could do it the long way, or write a simple PL/SQL script which would scan through the dictionary views ALL_VIEWS of the various databases. As long as the links are there, and the script has visibility to these links, you should be able to figure it out easily.

Daniel Received on Thu Jun 10 2004 - 14:31:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US