ORA-00942: table or view does not exist 2

[babe1898]

Hi all,

Here's my issue:

I created a a table called "TEST" with user student. The user student has the following tablespace:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
USERNAME CREATED PROFILE DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

student 22-JUL-08 DEFAULT USERS TEMP
teacher 22-JUL-08 DEFAULT USERS TEMP
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

So, the the table "TEST" is in the table_space USERS as shown below:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SEGMENT_NAME TABLESPACE_NAME
TEST USERS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


So when I logged in as another user, let's say, "teacher" which has the same tablespace as the user "student", and try to do a desc or select on the table "TEST". I got the following error message.

SQL> select * from TEST;
select * from TEST
*
ERROR at line 1:
ORA-00942: table or view does not exist


I did a search on the the erro message and I found the link below.

http://www.orafaq.com/forum/t/86885/0/

In this link it requires the user "student" to create a role and basically create some kind of table student.TEST. Then, grant a right on this table to user "teacher". So, the user "teacher" will be able to access the "TEST" table as follows:

select * from student.TEST;


So, my question is:

Is there any other way so that the user "teacher" can access the table(s) created by the user "student" directly? For instance,

select * from TEST;


Thank you in advance for your help.


Have a great day!

babe1898

 

[Dagon]

You need to use public or private synonyms.

 

[SantaMufasa]

Babe,

Dagon is exactly right. If you have never before done what he is suggesting, here is an example.

(Logged in as STUDENT)
SQL> grant SELECT on TEST to TEACHER;

(logged in as TEACHER)
SQL> CREATE SYNONYM TEST for STUDENT.TEST;

The above allows TEACHER to do queries such as:

SELECT * FROM TEST;

...without having to qualify TEST with its owning schema name, STUDENT.

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[babe1898]

Hi Dagon and SantaMufasa,

Thank you very much for your BIG help. It worked!

Have a great day!