Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm fairly new to Oracle database. I have installed Oracle Database 11g R2 on Oracle Linux 6. I have successfully created a new database with dbca and connected to the database using:

$ sqlplus "/ as sysdba"

I successfully created a table and inserted some data and performed some selects:

SQL> CREATE TABLE Instructors (
         tid    NUMBER(7) PRIMARY KEY,
         fname  VARCHAR2(32),
         lname  VARCHAR2(32),
         tel    NUMBER(16),
         adrs   VARCHAR2(128) );

Table created.

SQL> INSERT INTO Instructors (tid, fname, lname, tel, adrs)
     VALUES (8431001, 'John', 'Smith', 654321, 'London');

1 row created.

SQL> SELECT count(*) FROM Instructors;

  COUNT(*)
----------
        1

Then I created a new user with the CONNECT privilege:

SQL> CREATE USER teacher1 IDENTIFIED BY pass1;

User created.

SQL> GRANT CONNECT TO teacher1;

Grant succeeded.

Then I created a new role with appropriate object privileges:

SQL> CREATE ROLE instructor;

Role created.

SQL> GRANT SELECT, UPDATE ON Instructors TO instructor;

Grant succeeded.

And granted the role to the user:

SQL> GRANT instructor TO teacher1;

Grant succeeded.

Next I exited sqlplus with exit; and connected as the new user to test it. I logged in successfully to the database with:

$ sqlplus teacher1

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 25 03:20:50 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password: *****

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

But when I try to select from the table it says:

SQL> SELECT * FROM Instructors;
SELECT * FROM Instructors
              *
ERROR at line 1:
ORA-00942: table or view does not exist

What I'm missing here ?!

share|improve this question
add comment

1 Answer

up vote 7 down vote accepted

You created the table in the SYS schema (which you should never, ever do. Really, never).

When you log in as teacher1 any statement looks for objects in that schema. But there is no TEACHER1.INSTRUCTORS table, because the real name is SYS.INSTRUCTORS (did I mention what a bad idea it is to create objects in the SYS schema?).

You need to run select * from sys.instructors to get access to that table. If you don't want to prefix the table name with the schema, create a synonym in the teacher1 schema:

create synonym teacher1.instructors for sys.instructors;

Then teacher1 can access the table from the SYS schema without fully qualifying it.

Again: stop using the SYS or SYSTEM account for anything that is not DBA stuff. Use a regular account for that.

share|improve this answer
    
Thanks. I have to create tables that multiple users could access it. From your explanation, I infer that I should create a new user such as dbadmin with DBA privileges, and create all the tables with this DBA user. Then all other users should access the tables from the DBADMIN schema ... Correct ?! –  Seyed Mohammad Jul 26 '13 at 21:45
2  
@SeyedMohammad: No need to create a DBA user. Create a regular user and create the tables in that schema. Then grant select on those tables to the other users. Using DBA roles for anything else than DBA work is not a good idea. –  a_horse_with_no_name Jul 27 '13 at 15:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.