Re: Newbie: Accessing different databases from isqlplus

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 16 Jan 2007 08:13:36 -0800
Message-ID: <1168964013.707629@bubbleator.drizzle.com>

Jim Andersen wrote:
> 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?

No. HPUXRAC's response was 100% accurate for Oracle and refers to databases ... not schemas.

A database link, which is what HPUXRAC is referring to, in SQL Server, would be a pointer to what in SQL Server would be called linked servers.

> 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

Here's what it appears you actually want to do (greatly simplified)

CREATE USER abc
IDENTIFIED BY abc
QUOTA UNLIMITED ON users TO abc;

GRANT create session to abc;
GRANT create table to abc;

CREATE USER def
IDENTIFIED BY def
QUOTA UNLIMITED ON users TO def;

GRANT create session to def;
GRANT create table to def;

conn abc/abc

CREATE TABLE atab (
col1 VARCHAR2(20));

INSERT INTO atab (col1) VALUES ('ABC');
COMMIT; GRANT SELECT ON atab TO def;

conn def/def

CREATE TABLE dtab (
col1 VARCHAR2(20));

INSERT INTO atab (col1) VALUES ('DEF');
COMMIT; SELECT col1 FROM dtab
UNION
SELECT col1 FROM abc.atab;

Warning: It is snowing here and I'm not where I have an Oracle database on which to test this so it is just quickly written off the top of my head.

But the critical thing here is the line that GRANTs SELECT to def and when DEF performs the query ... referring to the table owned by ABC as ABC.ATAB.

HTH
> 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....)

Go to Morgan's Library at www.psoug.org and check out the links for DATABASE LINK (not that you need it now) SELECT
USERS
CONDITIONS
OPERATORS: Built-in

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Jan 16 2007 - 10:13:36 CST