3

When we do an Oracle Data Pump import of our database, it runs without significant errors. However, on one table Oracle reports that 0 records have been imported.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "SCHEMA1"."TABLE_NAME"                   34.26 GB       0 out of 402776 rows

The import does create the table. It just does not import any rows. There are other miscellaneous errors during the import -- none of which apply to this table.

If we drop the table and do a table-level import, it again creates the table but imports 0 rows.

We are using a basic data pump command line.

userid="sys/********@INSTANCE AS SYSDBA" SCHEMAS=(SCHEMA1, SCHEMA2, ...) DIRECTORY=DPUMP_DIR LOGFILE=LOGFILE.IMP.LOG DUMPFILE=DUMPFILE_%U_DATE.dmpdp

We are trying to figure out why the rows are not being loaded and to get them loaded.

Improve this question
3
  • 1
    what are the contents of your impdp parfile or impdp command line? Perhaps there is a QUERY clause that excludes data load? – Raj Sep 9 '14 at 14:44
  • We are using a very basic impdp command line. I added a sample to the question. – Metro Sep 9 '14 at 16:00
  • perhaps the table data was excluded when dump file was created then. You can run append keep_master=y to your command line, re-run. Then once it is over, look for a table with same name as your impdp job name (you will see it in the beginning of logfile), there you can find if this table actually contains data or not in the dumpfile. That master table is basically TOC for your dumpfiles. You can drop it off later. – Raj Sep 9 '14 at 17:04
1

I suspect this may have something to do with you starting impdp as sysdba. Please see the note in http://docs.oracle.com/database/121/SUTIL/dp_export.htm#SUTIL824:

"Do not start Export as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users."

Try your command as system.

Improve this answer
2
  • Thank you for your suggestion. We will try this the next time we do an import. We did resolve this specific issue by using the next day's export. The table imported fine from that. – Metro Sep 10 '14 at 14:26
  • Interesting. Can you compare the dump files? Perhaps just on a size basis? I wonder if you had a corrupted dump file. – captjackvane Sep 11 '14 at 12:40
0

We ended up using an export from the next day and doing a table-level import. The table was created and all rows were imported. It is concerning to us that this workaround succeeded. There were no indication in the export log of the problem export that there were any issues with the export. We did not try the suggestion to use a different user id than "SYS". We will try this the next time we have an issue with import.

Improve this answer
0

There is metalink note about this. https://support.oracle.com/knowledge/Oracle%20Database%20Products/1987476_1.html

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.