4

I'm trying to do a data pump export for metadata on a tablespace (COURSE). Am I on the right track? My data pump directory was created as:

CREATE DIRECTORY dpump_dir AS '/mydata/data';

Please keep in mind that I'm a beginner. This may or may not be the best way to go about this, but I'd like to try to get this working. Thanks very much.

declare
      dp_handle       number;
  begin
      dp_handle := dbms_datapump.open(
      operation   => 'EXPORT',
      job_mode    => 'TABLESPACE');

    dbms_datapump.add_file(
      handle    =>  dp_handle,
      filename  => 'courses.dmp',
      directory => 'dpump_dir');

    dbms_datapump.add_file(
      handle    => dp_handle,
      filename  => 'courses.log',
      directory => 'dpump_dir',
      filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

    dbms_datapump.metadata_filter(      
      handle => dp_handle,
      name   => 'TABLESPACE_EXPR',
      value  => '''COURSE''');

    dbms_datapump.start_job(dp_handle);

    dbms_datapump.detach(dp_handle);
  end;
  /    

ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2926
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3162
ORA-06512: at line 8
René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
relyt
  • 679
  • 6
  • 14
  • 26

3 Answers3

2

There are a number of reasons why this could fail:

  1. The file you want to write (courses.dmp) does already exist. Oracle won't override the file. (Yes, the error message is pretty lame)
  2. The OS-directory /mydata/data does not exist.
  3. /mydata/data does exist, but Oracle does not have the necessary rights to access the directory
  4. None of the above, but the Oracle user that runs the script was not granted read, write on dpump_dir

If these points don't help, you might first open (add_file) the *.log file, then the *.dmp file. Hopefully, Oracle will write something meaningful into the *.log file.

René Nyffenegger
  • 39,402
  • 33
  • 158
  • 293
  • 1
    The suggestion above about adding the log file before the dmp file has been KEY to me resolving issues with the dump process. – DonBecker Jun 03 '18 at 00:14
  • adding the log file before the dump file has also resolved my issue when i ran into this – mmiara Dec 14 '22 at 15:04
1

It appears your error is occurring on the first ADDFILE call.

Try making your directory references upper case: directory => 'DPUMP_DIR'

DCookie
  • 42,630
  • 11
  • 83
  • 92
0

Try this:

dbms_datapump.metadata_filter(      
      handle => dp_handle,
      name   => 'TABLESPACE_EXPR',
      value  => '=''COURSE''');

"I still get the same error messages though"

Hmmm.... Remote debugging of other people's code is one of the least fun aspects of SO. Anyway, let's give it another guess.

Do you have an OS directory called /mydata/data which Oracle has read and write privileges on? The CREATE DIRECTORY statement only creates a pointer, it doesn't create the underlying OS directory.

APC
  • 144,005
  • 19
  • 170
  • 281