Error message E03020003: Database error [Oracle][ODBC][Ora]ORA 00942: table or view does not exist is returned after the upgrade

Document ID : KB000049448
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

Problem:

  • Customer wanted to upgrade from Harvest R7.1 to CA Software Change Manager R12.1

  • This error message was returned after the upgrade: E03020003: Database error [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

Environment:

  • Windows 2008 R2 64 bit

  • Oracle 11.2.03

  • Harvest 12.1.02

Steps that were taken that resulted in the error message:

  • The DBA copied the database from old oracle 9 server to a new oracle 11 server.

  • The Harvest Administrator installed SCM R12.1 server and client (administrator + workbench) on the Windows 2008 workstation.

  • The Harvest Administrator ran hdbsetup and executed the following options:
    • CO for the ODBC connection

    • UR to upgrade the database

    • EP to create the encrypted userid and password file

  • The Harvest Administrator restarted the server

  • The Harvest Administrator registered the SCM Broker to run as a Windows service

  • The Harvest Administrator tested the ODBC connection. The test was successful.

  • The Harvest Administrator tested the Oracle connection using SQL plus. The test was successful.

Solution:

The database administrator needs to give access to the system view for the user which is trying to install it.

Also these things can be checked:

  1. Check if user tables and views exist in Oracle by querying the data dictionary with the following SQL statement

    select *
    from all_objects
    where object_type in ('TABLE','VIEW')
    and object_name = 'OBJECT_NAME';

    Replace OBJECT_NAME with the name of the table or view that you want to verify.

  2. If the table or view exists, verify that the user has the necessary permissions and rights to read and access (i.e. select) from the table, as certain privileges may be required to access the table. In this case, you will need to have the owner of the table or view, or an Oracle DBA grant the appropriate privileges, permissions, or rights to this object. Note that when selecting from a system view (such as v$session) the privilege or access rights to select from the view must be granted to the user directly and not via a role. This is the case if you can select from the view without problem but then get this error when trying to create a view on it.

  3. If the table or view exists but is in a different schema from the current schema where the SQL is executing (in other word, the table isn't owned by you, but owned by another user), the ORA-00942 error will be returned. Resolve this by explicitly referencing the table or view by specifying the schema name, i.e. schema_name.table_name.