UDE-00942 ORA-00942 Expdp errorstack

IMPERVA BLOCK expdp

SYMPTOMS

Datapump export (expdp) fails like:

$ expdp '<SCHEMA_NAME>/<PASSWORD>' PARALLEL=16 schemas=<SCHEMA_NAME> dumpfile=dump%U.dmp logfile=explog.log DIRECTORY=dpump_dir compression=all;

Export: Release 11.1.0.7.0 - 64bit Production on Saturday, 29 March, 2014 11:14:55

Copyright (c) 2003, 2007, Oracle. All rights reserved.

UDE-00942: operation generated ORACLE error 942
ORA-00942: table or view does not exist

CAUSE

The user performing the export does does have sufficient access permissions.

The actual table for which the permission is lacking can be shown by setting event 942 before starting the export job, e.g.

SQL> connect / as sysdba
SQL> alter system set events='942 trace name errorstack level 3';
SQL> exit

To disable the event again, use:

SQL> alter system set events='942 trace name errorstack off';

The trace file showed the export failing with following query:

ORA-00942: table or view does not exist
----- Current SQL Statement for this session (sql_id=3n58uzvnuw2hj) -----
SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET'

SOLUTION

o  Verify the user has all necessary privileges to export the data and grant if needed, e.g.

SQL> grant select on sys.v_$nls_parameters to <SCHEMA_NAME>;

- OR -

o  Export the data using the SYSTEM user.