ORA-00942: table or view does not exist

ORA-00942: table or view does not exist oracle error indicates that you are trying to run a SQL query that refers to a table or view that does not exist. The Referencing table or view does not exist or attempting to use a table without the necessary permissions or privileges. Check that the table and view names are spelled correctly, and that the view isn’t being used in places where a table is required.

The table or view hasn’t been created, which is a typical source of the “ORA-00942: table or view does not exist.” error. If you’re not sure whether a view, table, or synonym exists, run a query on the data dictionary to get a list of all tables and views that exist. You’re referring to a table or view in another schema that you didn’t make. You must refer to the table by its schema name in order to run the query successfully from another schema.

ORA-00942: table or view does not exist error occurs if the table or view does not exist in the database or not having proper permissions and privileges to access the table or view.



When the ORA-00942 error occurs

If the table or view does not created in the database or deleted from the database the ORA-00942: table or view does not exist oracle error occurs. If the table or view does not have necessary permissions and privileges, the error will be thrown.

select * from emp;
Error starting at line : 35 in command -
select * from emp;
Error report -
ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"


Root Cause

The table or view queried does not exist in the database. The database table or view was not created or it is deleted. The sql query searches the database objects list for a table or view. The error will be thrown because the sql could not find the table or view in the database. It’s possible that the user that runs the sql query doesn’t have the necessary permissions on the table or view. The error ORA-00942: table or view does not exist is going to be thrown.



Solution 1

In the sql query, the table or view name might be misspelt. In the sql query, double-check the table and view names. If the name is misspelt, change it in the sql query to the right name. The database will not contain the misspelt table or view. The error ORA-00942: table or view does not exist will be thrown.



Solution 2

The error is frequently caused by the table or view not being created in the database or being removed from the database. Create the table or view if it isn’t already present in the database. After the table or view is created in the database, the sql query will run without the error “ORA-00942: table or view does not exist”.

create table employee
(
empid integer primary key,
me-name varchar2(100)
);

select * from employee;


Solution 3

Different schemas can be used to create the table or view. It’s possible that you’re switching schemas. The table or view from the current schema could not be found by the sql query. The schema name should be prefixed to the table name in this scenario. To call a table or view from another schema, the current schema must have the required permissions.

select * from <schema_name>.<tablename>;

select * from hr.employee;


Solution 4

The underlying table may not be available or removed when the view is created, or it may not have the necessary permissions to run. The error will be thrown in this scenario. Ascertain that the underlying database objects, such as tables, types, and sequences, are accessible and have the appropriate permissions.

create table employee 
(
empid integer primary key,
empname varchar2(100)
);

create view emplist as select * from employee;

drop table employee;

select * from emplist;


Solution 5

If the table is created using a sequence that does not have enough permission to execute, the error will be thrown. The table is created using the sequence. if you insert a row in the table, the table could not execute the sequence and find the next value to insert into the table in the auto increment column.

create sequence seq_emp;
create table employee 
(
empid integer default seq_emp.nextval primary key,
empname varchar2(100)
);

insert into employee('Yawin');

The below permissions should be added to execute the sequence if an another user executes the insert statement.

grant select on seq_emp to emp2;