Re: Newbie: Accessing different databases from isqlplus

DA Morgan wrote:
>> I normally use MS sql server, but I have attended a beginners course >> on Oracle (10 I think...)

> www.psoug.org and 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 mean

I 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 Received on Tue Jan 16 2007 - 04:01:57 CST