Q
Problem solve Get help with specific problems with your technologies, process and projects.

Error messages on attempting full export

I am getting these error messages when I try to do a full export on a newly create Oracle 9i database. I already ran the catex.sql script, and I still get the errors.

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

Any suggestions?


The script to run is ORACLE_HOME/rdbms/admin/catexp.sql. This script must be run as SYS! Many people incorrectly run this script as SYSTEM. If this script has been run as SYSTEM, then you'll have to drop the objects this script creates, including any public synonyms, then run the script on SYS as well.

If that still does not fix your problem, then you'll have to start a trace in your export session to find out which view or table is missing, causing the ORA-942 error. A LOGON trigger can be used to start a trace. Such a trigger might look like the following:

CREATE TRIGGER after_logon_trigger
AFTER LOGON ON DATABASE
DECLARE
   usernm VARCHAR2(30);
   sid    NUMBER;
   serial NUMBER;
BEGIN
   SELECT user INTO usernm FROM dual;
   IF (usernm='MY_EXP_USER') THEN
      SELECT sid,serial# INTO sid,serial
      FROM v$session WHERE username=usernm;

      SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial,TRUE);
   END IF;
END;
/

Create that trigger as SYS, then do your export. When the error occurs, a trace file will be generated in your USER_DUMP_DEST directory. Search for the keyword "err=942" and you will find the SQL statement that is causing your problem. Fix the missing table or view.

And don't forget to drop your trigger when you are done. Otherwise, everytime this user signs on, a trace file will be generated.


Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close