0

I am currently trying to import a database using DBMS_DATAPUMP in PL/SQL using the following script.

DECLARE
    h1 NUMBER; 
BEGIN 
    h1 := DBMS_DATAPUMP.OPEN('IMPORT', 'FULL', NULL, DBMS_SCHEDULER.generate_job_name, 'LATEST'); 
    DBMS_DATAPUMP.ADD_FILE(handle => h1, filename => 'EXAMPLE6.DMP', directory => 'DUMP'); 
    DBMS_DATAPUMP.START_JOB(h1); 
    dbms_datapump.detach(h1);
END;
/

Everytime I execute this code, I get the following error message.

ERROR in line 1:
ORA-39001: invalid argument value
ORA-06512: in "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: in "SYS.DBMS_DATAPUMP", line 4929
ORA-06512: in "SYS.DBMS_DATAPUMP", line 5180
ORA-06512: in line 5

I already googled the error, but the answer mostly consisted of checking if the directory was already created and if the user had read and write access to the directory.

I also tried the impdp tool just as an experiment, to see if I could execute imports that way.

impdp pdb2 directory="DUMP" dumpfile="EXAMPLE6.DMP"

Based on the user I am executing impdp as, I get different error messages.

As a user with all privileges granted:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39155: error expanding dump file name "C:\Users\...\EXAMPLE6.DMP"
ORA-48128: opening of a symbolic link is disallowed

As the sysdba user:

ORA-39002: invalid operation
ORA-39070: unable to open the log file
ORA-39087: directory name DUMP is invalid

As I already said, the directory does exist on my drive, I created the directory called DUMP in Oracle and granted read and write access to my user.

All help would be appreciated and I would be happy to clarify if I wrote something confusing!

Edit: Output of select directory_name, directory_path from dba_directories;

DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
DUMP
C:\Users\Nemanja\Desktop\oraclePLS

I forgot to mention, that the Oracle service has complete access to the specified Windows directory.

Nemo
  • 1
  • 1
  • 3
  • it says that the directory does not exist. can you put the output of `select directory_name, directory_path from dba_directories` – Roberto Hernandez Aug 04 '20 at 16:23
  • that path exists in the database server ? keep in mind that datapump works in the server, you cannot use local folders – Roberto Hernandez Aug 04 '20 at 16:25
  • I am currently hosting the database on my local machine – Nemo Aug 04 '20 at 16:27
  • can you run this query: `SELECT DBMS_UTILITY.DIRECTORY_HAS_SYMLINK(dirobj => 'DUMP') as "HAS_SYMLINK" FROM dual;` as sysdba to see the return, either 1 or 0 – Roberto Hernandez Aug 04 '20 at 16:36
  • The select statement returns 0. – Nemo Aug 04 '20 at 16:59
  • that is ok. can you try in a different directory ? the dump file was created using datapump as well, right ? – Roberto Hernandez Aug 04 '20 at 17:13
  • Which version of Oracle are you using? There's a bug that might be relevant but only refers to this happening for `expdp`, not `impdp` - might be worth checking out though, as [MoS doc ID 2654167.1](https://support.oracle.com/epmos/faces/DocContentDisplay?id=2654167.1), and contacting Oracle Support if you think it might be that. (Assuming you have a support contract, of course...) – Alex Poole Aug 04 '20 at 17:32
  • I created a new directory and created a dump file with DATAPUMP. Export works fine, only import problems. I sadly do not have a support contract. – Nemo Aug 04 '20 at 17:33
  • Have you tried different OS directory paths, particularly somewhere that isn't under your home directory, e.g. c:\temp? No idea if that will make any difference, or what triggers the bug - if it is that. (Just curious, did you export to the same directory, or somewhere else and then move the file?) – Alex Poole Aug 04 '20 at 17:43
  • I created a new directory directly under C:\, created a dump and tried to import it. It actually made a difference. My original dumps, which are on the desktop, are extremely small. It seems they didn't finish for some reason. In the new directory the created dumps are much larger and seem to be actually created. I was actually able to import using a dump from this folder. Thank you all very much! – Nemo Aug 04 '20 at 18:12
  • I have seen this before with Oracle and trying to save / load things from folders under Desktop. I believe this is due to the Desktop folder being treated as special by Windows. If you right click on your desktop folder and select properties you will see an extra tab called location, where you can specify a different directory to actually save the files in (I have seen this pointed at a network location kind of like a windows symlink). But even if it is just pointed at the default location Oracle seems to have an issue with it. – Shaun Peterson Aug 05 '20 at 23:12

0 Answers0