Re: Newbie: Accessing different databases from isqlplus

On Jan 16, 11:01 am, "Jim Andersen" <jba..._at_politiSLET.dk.invalid> wrote:
> DA Morgan wrote:
> >> I normally use MS sql server, but I have attended a beginners course
> >> on Oracle (10 I think...)
> >www.psoug.organd scroll down to Microsoft SQL Server
> > 2005 Comparison for some help with verbiage and capability
> > comparisons.Great !
>
> > If you mean "multiple databases" in the sense of Oracle you can if you
> > build database links.
> > But I suspect that is not what you meanI am beginnning to suspect that too :-)
>
> > If you mean objects owned by different schemas then
> > you can provided you have been granted appropriate object privileges.Good.
>
> > In Oracle the word "database" means, and only means, a set of related
> > files (datafiles, tempfiles, log files, control files, spfile). Be
> > very careful with the word or clarify your intent when using it.Another reply (hpuxrac) wrote :> The connect command " connect username/password_at_database_alias" ...The "database_alias" is not a database then, but a schema?
>
> 'Coz right now they use sqlplus, and have a textfile containing
> spool spoolfile.lst
> connect username/password_at_database_aliasA
> Select * from tableA
> connect username/password_at_database_aliasAB
> Select * from tableA
>
> for 10 different database alias's
>
> could I make (or rather, tell them it can be done, and have them find an
> Oracle Sql guru to make it) 1 sql statement out of that, using UNION kinda
> like
> Select * from database_aliasA.TableA UNION database_aliasB.TableA UNION
> database_aliasC.TableA
> ?
>
> That way it would return just one large pretty resultset I can show in a
> GridView in my .NET front-end.
> Or should they do
> Create temptable T
> connect username/password_at_database_aliasA
> Select * from tableA into temptable
> connect username/password_at_database_aliasB
> Select * from tableA into temptable
> select * from temptable
> ?
>
> I am planning to create a dummy-user with read-access to all the databases,
> hrmmm... schemas, and my application connects to the server using that
> account.
> Do I need to put the username/password in there? Or can it just re-use the
> credentials given when I establish a connection to the Instance, given the
> dummy-user is in all the schema's?
>
> Any other tips/hints appreciated (while I am waiting to get access to the
> Oracle-instance so I can try out some of these things....)
>
> /jim

If you set up database links
(create database link foo using '<tns alias *on the server*>') and you make sure you use identical username and passwords everywhere, you don't need to use
connect to identified by
in the create database link command.

Obviously this is documented on http://tahiti.oracle.com

--
Sybrand Bakker
Senior Oracle DBA

"It can be simple once you start reading manuals"