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;


Then I created a new user with the CONNECT privilege:


User created.


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 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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


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

2 Answers 2


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.

  • 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 ?! Commented Jul 26, 2013 at 21:45
  • 5
    @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.
    – user1822
    Commented Jul 27, 2013 at 15:43

my answer is just write table name with login credentials of "sys" or/and "xe" like for my program change ur table name is EMP then write XE.EMP like "select * from XE.EMP"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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