Re: access data from other tablespaces

mngong_at_yahoo.com (michael ngong) wrote in message news:<ecf365d5.0210140927.3c84ae08_at_posting.google.com>...
> "Steve M" <steve.mcdaniels_at_vuinteractive.com> wrote in message news:<ansrje$dio$1_at_spiney.sierra.com>...
> > assuming that the owner of PRODUCE objects has a username of
> > PRODUCE_USER and that the owner of objects in SHOPPING has
> > a username of SHOPPING_USER
> >
> > SQL> connect PRODUCE_USER/<password_at_dbname
> > SQL> grant select on fruit to SHOPPING_USER
> > SQL> connect SHOPPING_USER/<password>_at_dbname
> > SQL> select produce.fruit.name
> > from produce.fruit a, list b
> > where a.fruit_id = b.fruit_id
> >
> >
> > "dave" <davidvantassell_at_yahoo.com> wrote in message
> > news:5a7c603c.0210070745.7df0dce1_at_posting.google.com...
> > > I'm trying to learn the syntax (if it exists) on how to reference data
> > > from a different tablespace. I'm new to oracle and creating a new
> > > database, and having read that it's good to sepearate the database
> > > into different tablespaces I thought I might try that. However if I
> > > have a table called fruit in my produce tablespace and a table called
> > > list in my shopping tablespace I can't figure out how to relate the
> > > two. (To add to the situation, if I create a table, then it creates
> > > the table in the shopping tablespace.)
> > >
> > > I tried:
> > > sqlplus> select produce.fruit.name
> > > sqlplus> from produce.fruit
> > > sqlplus> where produce.fruit.fruit_id = shopping.list.fruit_id
> > > sqlplus> ;
> > >
> > > it tells me that it value: "shopping.list.fruit_id" is not valid.
> > >
> > > Any suggestions or where I might go to find the answer?
> > >
> > > Thanks
>
> When you create a user (YOU) you should indicate a default
> tablespace if not all tables created by this user will be created in
> the system tablespace which is not the best practice.
> This means , if you create any table this table will be placed in the
> default tablespace.I will assume (may be) that your default tablespace
> was shopping that is why all your tables appear in shopping.The
> various tablespaces recommended by oracle for the most part will serve
> different purposes or will contain different information.
> Rollback tablespace,temp,users ,tools,data etc.
> If you need to specifically place a table in a certain tablespace make
> sure at the end of your table creation you indicate the tablespace
> name.
> ex
>
> create table Ngong(num number) tablespace pricelist
>
> will create your table in the tablespace pricelist if you have one.
>
> Peace
> Michael Tubuo Ngong
> " What matters most is the way you perceive it, not the way some
> other did it "

Hey,hey, wait a minute.

The tablespace name do not make any diference when refering to a certain object.

Of cource,a good practice is to give every schma a wery own tablespace. But you do not refer to an object by the tablespace name. You will use the schema name.

Just in the way Steve wrote.

Regards
Odd M Received on Wed Oct 16 2002 - 23:28:02 CEST