1

I'm trying to export a schema from an Oracle 10.2.0.5.0 database. For some reason, when I launch the expdp command, it finishes almost immediately and it only creates the log file, no dump file at all. It must be some silly mistake but I can't figure it out.

This is the command I'm executing on the server (logged in SSH as the Oracle user):

expdp my_user/my_pass@db schemas=my_user directory=my_dir dumpfile=exp.dmp logfile=exp.log

With SYS I get the same result:

expdp \"sys/sys_pass@db as sysdba\" schemas=my_user ...

There is only one database in the server, so I've also tried without the @db part. Same result.

I've checked that my_dir is properly defined and points to an existing filesystem location on the server, with available space (~3.4 GB free) and where the Oracle user can read and write. I've also granted read and write privileges to my_user on my_dir. And I've quadruple-checked that my_user is the correct schema with the tables I want to export.

This is the only output I get, both on the console (except the ;;;) and the logfile:

;;; 
Export: Release 10.2.0.5.0 - 64bit Production on Monday, 08 April, 2019 13:27:31

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;; 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

And then nothing. No dump file, no errors, no nothing. It just ends.

I've found this page with a few useful (?) queries. v$datapump_job and dba_resumable are empty. There are a few exports in dba_datapump_jobs, all of them with status NOT RUNNING; I don't know for sure because there isn't any "date" field, but I think they are old jobs, they don't change when I launch the expdp command.

What else can I check?

Edit: if I run echo $? immediately after the expdp command, it outputs 1, which according to this page (for Oracle 12c, not 10.2...), means "exit with fatal errors". Yet, not important enough to write them in the logfile?

  • Just want to make sure that you know that the datapump directory is on the RDBMS server and not local? – Philᵀᴹ Apr 8 at 12:50
  • Thanks Phil. Yes, I knew, that's where I'm checking the output log file. – AJPerez Apr 8 at 12:53
  • 1
    Does my_user have the EXP_FULL_DATABASE role? If not, try the export as that user without any SCHEMAS parameter. – CaM Apr 8 at 13:14
  • I just tried without the schemas parameter... and it failed again, but at least now it's showing an error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP. It seems like the Oracle partition doesn't have any available space. Hopefully that was causing all the issues... – AJPerez Apr 8 at 13:21
1

It was an issue with the TEMP tablespace, which was stored in a disk partition with no more available space.

Following CaM's suggestion (thanks!), I checked that my_user doesn't have the EXP_FULL_DATABASE role, so I launched again the expdp command but without the schemas parameter. For some reason, doing it this way Oracle finally took pity of me, gave up its shenanigans and decided to show the error:

ORA-39125: Worker unexpected fatal error in KUPW$WORKER.DISPATCH_WORK_ITEMS while calling
           DBMS_LOB.CREATETEMPORARY []
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

After "moving" the temp datafile to another partition and having a nice fight with Unix to release the disk space, the expdp finally works again.

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.