How To Fix the ora-00942 Error

You occasionally see an ora-00942 error when running an SQL statement. It has a few causes and as usual, the error syntax isn’t the most descriptive. If you’re coming up against this and want to know how to fix the ora-00942 error, read on.

As far as I know, there are three main causes of the ora-00942 error:

Insufficient user privileges
The table or view doesn’t actually exist
The table or view is in a different schema

I’ll show you how to address each one.

Fix the ora-00942 error

First off, a little disclaimer. I am no DBA, I am a Windows administrator and desktop and server hardware tech. I know how to run SQL but not to any degree of expertise and certainly not to the level that can troubleshoot issues. I had to ask an Oracle DBA buddy of mine for help, so while I wrote this piece, the clever bits are all his.

This list of three causes of the ora-00942 error isn’t exhaustive. There are apparently other random causes of it but these three are apparently the most common.

Insufficient user privileges

One key cause of the ora-00942 error is that the user doesn’t have sufficient privileges to access the table in question. You can check this by running two queries.

-- list system privileges for the user or role

SELECT * FROM dba_sys_privs

WHERE grantee IN (&user_role, 'PUBLIC');

— list object privileges for the user or role

SELECT grantee, owner||'.'||table_name object, privilege, grantable

FROM dba_tab_privs

WHERE grantee IN (&user_role)

ORDER BY grantee, owner||'.'||table_name, privilege;

These two will tell you if the user in question has the correct privileges to run the command. If the user has the correct privileges, move on to the next. If the user does not have the correct privileges, grant them to them or ask your DB Admin to do it.

The ora-00942 error can also occur if the user of the schema you are using has INSERT privileges but not SELECT privileges. Again, check the privilege level and add SELECT to the list or ask a DB Admin to do it. Apparently, the specific SELECT privilege has to be granted to each schema otherwise you will still see the ora-00942 error.

The table or view doesn’t actually exist

This cause of the ora-00942 error can be caused by incorrect query syntax or if the table doesn’t exist. While this would seem the logical first place to start, I am reliably assured that user privilege is the number one cause of the error. The table not being there or the wrong table syntax being used is second.

To check if the table exists, first check the syntax of the query. If the syntax is correct, run this query.

SELECT owner, object_name, object_type

FROM all_objects

WHERE object_type IN ('TABLE','VIEW')

AND object_name = ‘YOUR_TABLE_NAME';

On that last line, insert the actual table name where you see ‘YOUR_TABLE_NAME’. This should tell you definitely whether the table you are trying to query exists or not. If it returns with no table, the table you are querying does not exist in the schema or database.

If the system you are using has a Tables menu, you can manually check for the table if you prefer but the above query gets the job done.

The table or view is in a different schema

If the user has privileges and the table exists and you are still seeing the ora-00942 error, it is likely down to the schema. If you are managing multiple schemas, it is easy to run a query against a schema that isn’t yours. When you’re busy and up against it, it is a simple error to make.

Check the schema manually if you can or add the schema name in the FROM line of your query. If you don’t have the correct privileges for the new schema, you will see the ora-00942 error once more. Return to the first user privilege fix and check the corresponding schema or get your DBA to do it for you.

As mentioned above, I consulted an Oracle DBA buddy of mine for this piece so all credit to him for the hard work. If you find any errors or omissions in here, they are mine alone. Let me know in the comments section if I missed anything or got it wrong and I will correct it.

If you know of any other way to fix the ora-00942 error, tell us about it below!