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
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.