Same table name, different schemas

Here's something I've never quite understood and can't seem to find in Oracle documentation--

Let's say there are two tables in a database with the same name (different schemas, of course):

     user1.table1
     user2.table1

Each owner has access to their own table, and the other one if permissions have been granted. But let's say I'm user3. I connect to Oracle (say, via sqlplus). Assuming both tables grant me read permission, if I submit the following, which table will I see?

     select * from table1;

I know that if I qualify the table name (e.g., user1.table1) I'll get the precise one I want, but what if I don't qualify it? How does Oracle decide which table I mean? I have a real life situation where this is exactly the case, and I get results from the query, but I'm not owner of either table.

TIA, Jack Garceau

In article
<37544f1a.22459099_at_netnews.worldnet.att.net>,   jonathan_at_gennick.com wrote:
> On Tue, 25 May 1999 13:20:46 -0700, "Filip
Hanik"
> <fhanik_at_digitalworkforce.net> wrote:
>
> >I'm pretty new to Oracle and I'm trying to
figure out the concept
> >tablespaces and users' default tablespace.
> >If two users have two different tablespaces in
the same database, are they
> >not sharing the same database schema?
>
> With Oracle, each user gets their own default
schema. The
> schema name matches the user name. Users are
allowed to
> access objects in other schemas, but the
respective schema
> owners need to grant permission first. Thus, I
can't see
> Fred's address table until Fred issues a GRANT
SELECT ON
> address TO jonathan.
>
> Tablespaces control where on disk an object is
stored. When
> you create a table, you tell oracle to store it
in a
> "tablespace". Each tablespace will have one or
more
> datafiles associated with it. All objects stored
in the
> tabelspace go into those datafiles. You can have
a
> many-to-many between objects and datafiles.
Tablespaces
> allow that to happen.
>
> Default tablespaces simply control where a
user's objects go
> if one is created with out specifying a
tablespace. If my
> default tablespace is user_data, then the
following table
> will go there:
>
> create table x (y number);
>
> I can make it go somewhere else by writing the
command like
> this:
>
> create table x (y number)
> tablespace x;
>
> Hope this helps.
>
> Jonathan Gennick
>
>
Received on Wed Jun 09 1999 - 22:22:29 CDT