Getting “ORA-00942: table or view does not exist” while table does exist

Question

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 ?!

Answer 1

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.