Same table name, different schemas 
Author Message
 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

Quote:

> On Tue, 25 May 1999 13:20:46 -0700, "Filip
Hanik"

> >I'm pretty new to Oracle and I'm trying to

figure out the concept
Quote:
> >tablespaces and users' default tablespace.
> >If two users have two different tablespaces in

the same database, are they
Quote:
> >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



Sun, 25 Nov 2001 03:00:00 GMT
 Same table name, different schemas

Jack,

Generally AFAIK you should get an error because you haven't
qualified the name of the table. Since you don't, I expect
that either a public synonym has been declared for one of
the tables, or you have a private synonym for one of them in
your user schema.

Regards, David.

Quote:

> 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


> > On Tue, 25 May 1999 13:20:46 -0700, "Filip
> Hanik"

> > >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



Mon, 26 Nov 2001 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Insert into table from different schemas

2. Foreign key references to tables in different schemas ??

3. Creating Views joining tables in two different schemas

4. Designer 2.1 (Tables in different schemas)

5. How do you compare the data in different schemas not just table diffs

6. Same Dimension but using different names gave different cube results

7. Problems with different schemas in ORACLE

8. Different views with same name for different users

9. Do FROM items of different schemas conflict?

10. RFD: schemas and different kinds of Postgres objects

11. RFD: schemas and different kinds of Postgres objects

12. Union Query between different schemas


 
Powered by phpBB® Forum Software