Resolving Oracle "ORA-00942: table or view does not exist " Error

smallwei 6:44 pm on November 10, 2009 | Answers: 1    
Tags: error (74), knowledge (9), ora (7), ora-00942, oracle (120), oracle database (10), resolving (9), table (27), view (19)

Rate Knowledge:

 Loading ...

• smallwei 6:44 pm on November 10, 2009
  

  Official Oracle explanation:
  Cause: The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it.
  Action: 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.

  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.

  Here are the things you should verify:

  • Make sure the spelling of the object is correct.
  • Double check the object you are referring to actually exists. Login to the target schema and use the following sql to verify:

  -- Replace YOUR_OBJECT_NAME with the object name in question. (use upper case)
  select owner, object_type from all_objects where object_name = 'YOUR_OBJECT_NAME';

  • If you are using DB LINK, or the object is in another schema, make sure your user has permission to the object in the target schema.
  • If ORA-00942 occurred during materialized view refresh, try to enable SQL TRACE for your session (ALTER SESSION SET SQL_TRACE = TRUE;) and verify the error in the trace file.
  • If you are doing import and encounter ORA-00942, it could relate to another table that is not being imported. Basically, when it is thrown after imports, the system was trying to locate the parent table of the imported child table. Try to do the following things to resolve:
    1. Enable log output for your import. (parameter: log=[LOG_FILE_NAME])
    2. Use import Show = Y to see what import intends to do, Show = Y will not really import anything. You can see see if your table has a child constraint on it.
    3. Import the table with Show = N and Rows = N to build the new table without data, then disable all constraints on new tables.
    4. Import the table again with Ignore = Y.
    5. Enable the constraints again on a new table.