[sql] SQL Error: ORA-00942 table or view does not exist

I use SQL developer and i made a connection to my database with the system user, after I created a user and made a another connection with that user with all needed privileges.

But when I try to proceed following I get the SQL Error

ORA-00942 table or view does not exist.:


INSERT INTO customer (c_id,name,surname) VALUES ('1','Micheal','Jackson')

This question is related to sql oracle oracle11g

The answer is


You cannot directly access the table with the name 'customer'. Either it should be 'user1.customer' or create a synonym 'customer' for user2 pointing to 'user1.customer'. hope this helps..


Case sensitive Tables (table names created with double-quotes) can throw this same error as well. See this answer for more information.

Simply wrap the table in double quotes:

INSERT INTO "customer" (c_id,name,surname) VALUES ('1','Micheal','Jackson')

I am using Oracle Database and i had same problem. Eventually i found ORACLE DB is converting all the metadata (table/sp/view/trigger) in upper case.

And i was trying how i wrote table name (myTempTable) in sql whereas it expect how it store table name in databsae (MYTEMPTABLE). Also same applicable on column name.

It is quite common problem with developer whoever used sql and now jumped into ORACLE DB.


Here is an answer: http://www.dba-oracle.com/concepts/synonyms.htm

An Oracle synonym basically allows you to create a pointer to an object that exists somewhere else. You need Oracle synonyms because when you are logged into Oracle, it looks for all objects you are querying in your schema (account). If they are not there, it will give you an error telling you that they do not exist.


Issue could be with different table(might not exists or grant privilege is not for that table) mapped due to foreign key or synonym.

For me the issue was with a column in that table which had mapping with another schema-table, and it was missing.ex, public-synonym.


Either the user doesn't have privileges needed to see the table, the table doesn't exist or you are running the query in the wrong schema

Does the table exist?

    select owner, 
           object_name 
    from dba_objects 
    where object_name = any ('CUSTOMER','customer');

What privileges did you grant?

    grant select, insert on customer to user;

Are you running the query against the owner from the first query?


Because this post is the top one found on stackoverflow when searching for "ORA-00942: table or view does not exist insert", I want to mention another possible cause of this error (at least in Oracle 12c): a table uses a sequence to set a default value and the user executing the insert query does not have select privilege on the sequence. This was my problem and it took me an unnecessarily long time to figure it out.

To reproduce the problem, execute the following SQL as user1:

create sequence seq_customer_id;

create table customer (
c_id number(10) default seq_customer_id.nextval primary key,
name varchar(100) not null,
surname varchar(100) not null
);

grant select, insert, update, delete on customer to user2;

Then, execute this insert statement as user2:

insert into user1.customer (name,surname) values ('michael','jackson');

The result will be "ORA-00942: table or view does not exist" even though user2 does have insert and select privileges on user1.customer table and is correctly prefixing the table with the schema owner name. To avoid the problem, you must grant select privilege on the sequence:

grant select on seq_customer_id to user2;

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to oracle11g

Convert timestamp to date in Oracle SQL Query to display all tablespaces in a database and datafiles Oracle Installer:[INS-13001] Environment does not meet minimum requirements Forgot Oracle username and password, how to retrieve? Extract number from string with Oracle function How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1 Add days Oracle SQL How to determine tables size in Oracle ORA-28000: the account is locked error getting frequently Oracle listener not running and won't start