4

I have received a dump of a Oracle 11.2.0.2.0 db, which I'd like to export to some readable format like plain text, CSV, excel...

Is that possible in some sort of a simple way? I've found a few converters, but they're not free.

If nothing else, I did just install the latest Oracle Express Edition and Oracle SQL Developer, but I can't seem to find a way to import my .DMP file. I've tried using the imp and impdp tools, but they both gave me an error:

imp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt

IMP-00038: Could not convert to environment character set's handle

impdp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39088: file name cannot contain a path specification

UPDATE:

It seems I needed to copy the dump to "C:\oraclexe\app\oracle\admin\XE\dpdump" for the IMPDP to work. But now I get the following error message (or maybe just a warning, because as far I see, the tables were created and the data was imported?):

ORA-39083: Object type REFRESH_GROUP failed to create with error:

ORA-23421: job number 1389 is not a job in the job queue

Failing sql is:

BEGIN dbms_refresh.make('"DB"."TABLE"',list=>null,next_date=>null,interval=>null,implicit_destroy=>TRUE,lax=>FALSE,job=>1389,rollback_seg=>NULL,push_deferred_rpc=>TRUE,refresh_after_errors=>FALSE,purge_option => 1,parallelism => 0,heap_size => 0); dbms_refresh.add(name=>'"DB"."TABLE"',list=>'"DB"."TABLE"',siteid=>0,export_db=>'XE'); END;

Job "SYS"."SYS_IMPORT_FULL_01" completed with 32 error(s) at 06:59:30

UPDATE 2:

Yeah, I've just checked via Toad for Oracle, and all the data was successfully imported. I also managed to export to excel file using the same application.

Improve this question
6
  • What line did you run in order to generate the errors?
    – Ben
    Aug 29, 2012 at 12:40
  • 1
    Do you have NLS_LANG set? How did you receive the file - it's a binary format so it it possible it's been corrupted by an ASCI FTP or similar? Does running imp with show=y give you anything?
    – Alex Poole
    Aug 29, 2012 at 13:54
  • @Ben I've updated the question. @AlexPoole I don't know. I've received it through email. show=y gives me the exact same error as before.
    – Howie
    Aug 30, 2012 at 4:53
  • You have to create a directory first. In sqlplus: create directory IMPDIR as '/path/to/the/file';. Then repeat your previous impdp command-line, but add the parameter DIRECTORY=IMPDIR
    – Philᵀᴹ
    Aug 30, 2012 at 14:46
  • 1
    ORA-39088: file name cannot contain a path specification indicates that your file name specification contains a path specification like file=C:\dump.dmp or something similiar. In your post the impdp command did not have such a path specification. This is strange. Do you show us the real command or did you remove the path in your post?
    – miracle173
    Aug 31, 2012 at 1:45

1 Answer 1

Reset to default
1

First of all you need to create a directory on the database.

Create or Replace Directory IMP_DIR as 'Path\Path\';

Ensure the user that will import the file has the privilige to Read & Write on the directory (In case you are not using SYS)

GRANT Read, Write ON DIRECTORY IMP_DIR TO user;

Then, include the directory parameter within the import statement

impdp 'sys AS SYSDBA' file=dump.dmp full=yes log=implog.txt directory=IMP_DIR
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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