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

How to save video from Internet Explorer

-
How to save video from Internet Explorer Here I am going to share an experience with you, where I was required to get the video file that I was playing in Internet explorer out of Temporary directory and save it to some permanent disk folder. Note: It is far better if you use video download tools with the web browser. Internet Explorer Version: 7.0.6xxx [Open Internet Explorer – Help – About Internet Explorer] Operating System: Windows Vista I was not playing file from you tube [I do not see any tmp file while watching video from youtube.] I found the file in following directory: C:\Users\<UserName>\AppData\Local\Microsoft\Windows\Temporary Internet Files\Low [ Although the directory is not visible in my computer system, so I directly put it in address bar/location bar in windows explorer. ] File name was fla3870.tmp [May be in your system It will be by different name] You have to find the video file by trick technique for example: 1.
Read more

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