Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Same table name, different schemas

Re: Same table name, different schemas

From: David Pattinson <david_at_addease.com.au>
Date: Thu, 10 Jun 1999 14:55:01 +1000
Message-ID: <375F4524.E0D21DC9@addease.com.au>


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.

Jack Garceau wrote:

> 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 - 23:55:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US