Re: access data from other tablespaces

From: Steve M <steve.mcdaniels_at_vuinteractive.com>
Date: Mon, 7 Oct 2002 13:43:40 -0700
Message-ID: <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
Received on Mon Oct 07 2002 - 22:43:40 CEST