ORA-00942: table or view does not exist

-

ORA-00942: table or view does not exist

Oracle Server uses the concept of “Schema” to hold the objects 
in the database. Hence a Schema acts as a container for 
database’s object(s). It also provide security model to 
the database itself.
What it means is that the USER must have sufficient privilege(s) 
to access the database object for the intended purpose, 
like if you want to see the data in a table of Schema X 
then you must have select privilege on that table. 
You will see an example for that later on…


Reason for ORA-00942
~~~~~~~~~~~~~~~~~~~~~~~
1.  You are trying to execute a SQL statement that 
references a table or view that does not exist.
2. A synonym that is not allowed here was used.
3. You do not have access rights to that particular 
object that you are referencing in your query.
4. The table or view belongs to another schema and you 
did not reference the table by the schema name.
5. A view was referenced where a table is required.



Solution for ORA-00942
~~~~~~~~~~~~~~~~~~~~~~~
Check each of the following:
  • The spelling of the table or view name.
  • That a view is not specified where a table is required.
  • That an existing table or view name exists.
  • That you have the right privilege(s) to access the table (database object).

Contact the database administrator if the table needs to be created or if user or application privileges are required to access the table.
Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted. 


Illustrative Example
~~~~~~~~~~~~~~~~~~~~~~~~~~

[DBA Session]
SQL> create user example identified by myexample
  2  /
User created.

SQL> grant connect, resource to example
  2  /
Grant succeeded.


[USER (example) Session]
SQL> Select EMPNO, ENAME, HIREDATE, SAL
  2  From Scott.EMP
  3  /
From Scott.EMP
           *
ERROR at line 2:
ORA-00942: table or view does not exist


[DBA Session]
SQL> grant select on Scott.EMP to example
  2  /
Grant succeeded.

[USER (example) Session]
SQL> Select EMPNO, ENAME, HIREDATE, SAL
  2  From Scott.EMP
  3  /

     EMPNO ENAME      HIREDATE         SAL
---------- ---------- --------- ----------
      7369 SMITH      17-DEC-80        800
      7499 ALLEN      20-FEB-81       1600
      7521 WARD       22-FEB-81       1250
      7566 JONES      02-APR-81       2975
 ……….……………………………………………………………………………….

14 rows selected.

Comments

Post a Comment

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

-
I was trying to test SSL connection between MySQL client and server. For that I created SSL certificate and keys by following the MySQL documentation at: Creating SSL Certificates and Keys Using openssl After finishing up all the commands when I verify the certificates by using: openssl verify -CAfile ca.pem server-cert.pem client-cert.pem I got the following output: error 18 at 0 depth lookup : self signed certificate error server-cert.pem: verification failed client-cert.pem: OK  Reason : The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files will not work for servers compiled using OpenSSL. Solution : When OpenSSL prompts you for the Common Name for each certificate, use different names. Common Name (e.g. server FQDN or YOUR name) []: I used values as follows: CA Cert:  ssl-ca-cert Server Cert: ssl-ca-server-cert Client Cert:  ssl-c
Read more

How to check fragmentation in MySQL tables

-
MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted, updated and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient. We will mention how to check fragmentation in MySQL tables using SQL queries. USE < put_your_dbname_here > SELECT TABLE_NAME, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE, CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE FROM information_schema.TABLES where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00 ORDER BY DATA_FREE DESC; SELECT TABLE_NAME, ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent FROM information_schema.TABLES where table_schema = database() and TABLE_ROWS > 0
Read more

How to Drop or Remove or Decommission a Database in Oracle

-
How to Drop or Remove or Decommission a Database in Oracle Database Decommission steps for Oracle 10G and 11g databases Note: ~~~~~~~~ Every organization has its own documented way of removing or decommissioning the active/running database that is supporting an Enterprise’s Application. Here I am presenting some general steps which may coincide with your organization’s standard operation procedure (SOP). 1)   Please get the required approval from Business/Customer to proceed for database decommissioning. 2)   Send the Notification to Business/Customer that we are going to decommission the database. (if required) 3)   Raise a Request for Storage and Backup team to take the required backup (provide all the details in the raised request) and to keep the Backup for a specified period of time, say for next 2 years, (That is called the backup retention period). Retention Period may be varied based on your business needs and SLA. The backup can be on disk or
Read more