Ok calling an Oracle expert, DBA or general good guy

The problem is simple but trawling Mr Google has not come up with an easy to use solution.

I am testing data input.

I want to compare master.dmp with testers.dmp these have been created using exp in Oracle 11g the schema will be the same it is only the data input that will change.

The master.dmp was created when I put the data in myself using some sql and typing. Now I have to use the app and a tester(me) to see if the results are the same.

I can use ODBC and import data in Excel and uses Excel Compare Ok but it would be quicker to just compare the two .dmp files and highlight the changes .

Has any on done this? Has anyone a link to a tool or software that will compare two dmp files?

or is there a nice easy way to compare the data.


Comparing dmp files like that is probably quite difficult to do, as the format is quite complex, with snippets of embedded SQL and all sorts. Having said that, although it is a binary format, it is a stream format (i.e. no internal pointers), so it's quite amenable to processing with sed.

I think loading the contents of he dmp files into two schemas and comparing the two is by far the easiest way you're going to do this, barring the existence of some tool that I'm not aware of.

  • I was hoping to avoid that although Redgate do a good tool for comparing. Just hoping out there some where is a smart tool for comparing raw .DMP
    – icecurtain
    Dec 20 '11 at 12:31

windows has a built in compare utility called comp

Compares the contents of two files or sets of files.

COMP [data1] [data2] [/D] [/A] [/L] [/N=number] [/C] [/OFF[LINE]]

  data1      Specifies location and name(s) of first file(s) to compare.
  data2      Specifies location and name(s) of second files to compare.
  /D         Displays differences in decimal format.
  /A         Displays differences in ASCII characters.
  /L         Displays line numbers for differences.
  /N=number  Compares only the first specified number of lines in each file.
  /C         Disregards case of ASCII letters when comparing files.
  /OFF[LINE] Do not skip files with offline attribute set.

To compare sets of files, use wildcards in data1 and data2 parameters.

Not sure if this is what you're looking for though.

  • After 10 unequal comparisons, comp stops comparing the files and displays the following message: 10 Mismatches - ending compare This did not do any more than the first 10 chars.
    – icecurtain
    Dec 20 '11 at 12:28
  • Mm... this will not solve your problem then. my Google-Fu only returned the following odbc-dump.qarchive.org
    – xQbert
    Dec 20 '11 at 12:38
  • @xQbert - the dump files are a binary format with a lot of extraneous data and hardwired schema names, comp/diff and similar tools won't really help here. Dec 20 '11 at 13:25
  • If you want to compare output for your inputs, you should consider using utPLSQL, a framework that can automate testing for you. As for comparing output, you could create a test schema from master schema. Do testing in test schema. Then use sql (specifically minus) commands to compare output for specific tables between your master and test schemas. Or you can use dbms_compare to automate that. There are so many ways to do, but using dmp (not sure if you used exp or expdp but they both generate data in proprietary format), is probably not easy. perhaps consider csv exports instead?
    – Raj
    Nov 1 '13 at 20:07

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.