EXP error 942 Table or view does not exit on server?

[lydiattc]

Hi,

I'm doing an export on the server, Oracle 9.2.0.1.0. However, I received an error message like this: Table or view does not exist, ORA-error-942. Any idea what went wrong?

Thanks,
Lydia

 

[avjoshi]

Are you using export, import utilities from the same version?

Anand.

 

[dbtoo2001]

The following information is from the metalink website:

Limitations. ------------
The following limitations apply to the EXPORT and IMPORT utilities (see details in the matrix below):

1. Oracle export dump files can only be read by the Oracle IMPORT utility because these dump files are stored in a special Oracle-binary format.

2. Every export dump file is importable into all future major, patch, and maintenance releases of Oracle.

3. The export dump files cannot be read by IMPORT utilities of previous maintenance releases and versions. So, a version 9 export dump file cannot be imported by a version 8i IMPORT utility (possible errors: IMP-10 and IMP-21), and a version 8 export dump file cannot be imported by a version 7 IMPORT utility (possible errors: IMP-69 and IMP-21). In both cases, the import will terminate with the error IMP-0 "Import terminated unsuccessfully".

4. IMPORT can read export dump files created by EXPORT release 5.1.22 and higher (up to same version).

5. IMPORT cannot read export dump files created by the EXPORT utility of a higher maintenance releases or versions. So, a release 8.1 export dump file cannot be imported by a release 8.0 IMPORT utility, and a version 8 export dump file cannot be imported by a version 7 IMPORT utility.

6. The Oracle6 (or earlier) EXPORT utility cannot be used against an Oracle8 or Oracle8i or higher release database.

7. Whenever a lower version EXPORT utility runs with a higher version of the Oracle Server, any categories of database objects that did not exist in the lower version are excluded from the export. For example, partitioned tables are not exported by the version 7 EXPORT utility. If you need to move a version 8 partitioned table to a version 7 database, then first reorganize the table into a non-partitioned table.

Examples.
---------

1. From 7.3.3 to 8.1.6 => Use the EXPORT 7.3.3 to export the data from the
7.3.3 database and IMPORT 8.1.6 to import the data
into the 8.1.6 database.
2. From 8.1.7 to 8.1.7 => Use the EXPORT 8.1.7 to export the data from the
8.1.7 database and IMPORT 8.1.7 to import the data
into the 8.1.7 database.
3. From 9.0.1 to 8.1.7 => Use the EXPORT 8.1.7 to export the data from the
9.0.1 database and IMPORT 8.1.7 to import the data
into the 8.1.7 database.
4. From 8.1.7 to 9.2.0 => Use the EXPORT 8.1.7 to export the data from the
8.1.7 database and IMPORT 9.2.0 to import the data
into the 9.2.0 database.
5. From 9.0.1 to 9.2.0 => Use the EXPORT 9.0.1 to export the data from the
9.0.1 database and IMPORT 9.2.0 to import the data
into the 9.2.0 database.
6. From 8.1.7 to 7.3.4 => Only if never run before, first run CATEXP7.SQL
on the 8.1.7 database in order to create the
Oracle7 data dictionary views; then use the
EXPORT 7.3.4 to export the data from the 8.1.7
database and IMPORT 7.3.4 to import the data into
the 7.3.4 database.
7. From 9.0.1 to 7.3.4 => Not supported.
8. From 8.0.5 to 9.0.1 => Not supported with export FULL=Y and import FULL=Y
9. From 8.1.5 to 9.2.0 => Not supported with export FULL=Y and import FULL=Y

 

[lydiattc]

Thanks for all your replies. When I execute the following command from DOS prompt (Oracle9i on Windows2000):
exp usrid/passwd full=y file=myExp.dmp log=myLog.log

The message shows the Oracle version is 9.2.0.1.0. I assume this is the version for the export utility. If I go to OEM and look up the Help menu, it tells me the same version. Where else I can look up the version number for the server?

If all places tell me that I'm using same version utility as the server, what else could be wrong?

Thanks,
Lydia

 

[dbtoo2001]

Lydia:
Do you have more than 1 oracle home on the machine? If so, it could be path. Please post the path variable. (Better yet, do a SET command, if you are permitted.)

-david

 

[lydiattc]

The full path to the export utility is H:\oracle\ora92\bin\.

There's only one oracle home on the machine, and I specified the full path to the utility when I ran it.

Any other ideas?

Thanks,
Lydia

 

[dbtoo2001]

are there any other messages besides the ORA-00942?

is the usrid a DBA user?

 

[avjoshi]

was the dump file myExp.dmp created using same version.

 

[dbtoo2001]

was catexp.sql executed against this database when it was built?

 

[karluk]

Could you please cut and paste the exact output from your export? As the other replies indicate, you are most likely having a compatibility issue between your database and the version of the export utility you are using. If you give all the output, we will be able to either verify this, or disprove it and move on to other possible explanations. There should be two lines in the output among other things. One should say something like

Export: Release 9.2.0.1.0 - Production on Tue Nov 25 15:43:37 2003

That tells you the version of the export utility. There should also be a line similar to

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

This line tells you the release level of the database.

 

[lydiattc]

Thank you all! We're having some network problems since late yesterday. I haven't been able to log onto our server. I'll keep trying today and give you the log file for the export so that you can help me to detemine what went wrong.

Thanks again and happy Thanksgiving!

Lydia

 

[lydiattc]

Okay, I'm back and our server came back online. Here's the log file from export on the server:
=================================================
Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character
set

About to export the entire database ...
.. exporting tablespace definitions
.. exporting profiles
.. exporting user definitions
.. exporting roles
.. exporting resource costs
.. exporting rollback segment definitions
.. exporting database links
.. exporting sequence numbers
.. exporting directory aliases
.. exporting context namespaces
.. exporting foreign function library names
.. exporting PUBLIC type synonyms
.. exporting private type synonyms
.. exporting object type definitions
EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully
=================================================

When I did "exp help=y" on the server, it spitted out this:
================================
Export: Release 9.2.0.3.0 - Prodcution on Wed Dec 3 11:40:32 2003
...
...
=================================

Apearently, the server and the export utility have the same version numbers. Please let me know if you have any ideas why it's terminated unsuccessfully.

Thanks,
Lydia

 

[dbtoo2001]

I believe that this line:
EXP-00024: Export views not installed, please notify your DBA

may indicate that $ORACLE_HOME/rdbms/admin/catexp.sql might need to be run against the database. (as previously mentioned above.)

 

[karluk]

dbtoo2001 is right that you seem to be missing the 9.2.0.3.0 version of the export views. You should be able to fix this specific problem by running catexp.sql, as he suggests.

However, I suspect that your problem is wider in scope than this. The 9.2.0.3.0 patchset has been applied to your server. After installing the patchset, you should have run the catpatch.sql script against all databases on the server. Most likely this has not been done, since running this script would have updated the export views.

Can you verify whether or not catpatch.sql has been run? Perhaps you can talk to whoever did the patchset install.

 

[lydiattc]

Thank you very much! I think all of you are right. However, I don't know who installed the patch. This is our client's server and the Oracle and the patchset were installed by their tech support. I'll pull all the strings to find out.

Thanks again!

Best wishes,
Lydia