Saturday, April 19, 2014

ORA-00942

These examples were tested on Oracle 11.2. If you get an ORA-00942, this might mean that a table does not exist:
 
SQL> conn / as sysdba
Connected.
SQL> drop user andrew cascade
  2  /
 
User dropped.
 
SQL> drop user john cascade
  2  /
 
User dropped.
 
SQL> grant dba to andrew identified by reid
  2  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> select count(*) from blah
  2  /
select count(*) from blah
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 

If that is the case, creating the table will make the error go away: 

SQL> create table blah (col1 number)
  2  /
 
Table created.
 
SQL> select count(*) from blah
  2  /
 
  COUNT(*)
----------
         0
 
SQL> 

But in the next example, table ANDREW.BLAH exists as we have just created it. The problem is that JOHN does not have permission to look at it: 

SQL> grant create session to john
  2  identified by smith
  3  /
 
Grant succeeded.
 
SQL> conn john/smith
Connected.
SQL> select count(*) from andrew.blah
  2  /
select count(*) from andrew.blah
                            *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> 

When that happens, you have to GRANT appropriate access to make the error disappear: 

SQL> conn andrew/reid
Connected.
SQL> grant select on blah to john
  2  /
 
Grant succeeded.
 
SQL> conn john/smith
Connected.
SQL> select count(*) from andrew.blah
  2  /
 
  COUNT(*)
----------
         0
 
SQL>

No comments: