0

I am trying to make dump of Oracle database using the expdp tool. I added the exclude=statistics option to the command line to make the resulting dmp file smaller, but the file is still very big even with this setting. Is there some other setting that can be used to make the dmp file smaller? The database is almost empty and the dmp file is around 230MB. Thank you.

Martin Staufcik
  • 8,295
  • 4
  • 44
  • 63
  • Did you trying enabling compression, and perhaps exporting only the schemas you care about? Eg expdp scott/tiger directory=dir schemas=scott dumpfile=scott_schema_compreess_all.dmp compression=all – Stephen ODonnell Oct 07 '15 at 09:53
  • I tried now the compression=all option, and got message: feature not enabled: Dump File Data Compression. – Martin Staufcik Oct 07 '15 at 10:50
  • Ah, sounds like a licensing issue. Are you running Standard Edition? A quick google suggests you need the advanced compression license to compress the dump files. Back in the old exp/imp days you could gzip compress on the fly, but that doesn't seem to work anymore :-( How big is the file if you gzip it? The dump may be so large because it is a full DB dump - ie includes system tablespaces etc. What size is the dump if you list just the schemas you are interested in (see my example command above for how to do this)? – Stephen ODonnell Oct 07 '15 at 11:31
  • We are using express edition 11.2.0.3.0 for development. The dmp file when zipped is almost as big as uncompressed. The schemas option is in place to select only one schema. Interestingly, when customer sends their updates, their dump file of the schema is only a few MB, our dump is hundreds MBs. – Martin Staufcik Oct 07 '15 at 11:58
  • The command line is now: expdp system/xxxxx@naturadev schemas=NATURAMAN dumpfile=naturaman.dmp exclude=statistics – Martin Staufcik Oct 07 '15 at 12:00
  • 1
    I don't have Oracle running here at the moment to test it out. What is the size of all objects in your schema? select sum(bytes)/1024/1024 from dba_segments where owner = 'NATURAMAN' – Stephen ODonnell Oct 07 '15 at 13:47
  • It seems like you can get your answer just by asking your client to send his expdp command over to you. – Yaron Idan Oct 07 '15 at 14:21
  • @StephenODonnell the select returns 290, so it looks there is lots of data. Thanks a lot for pointing in the right direction, I'll investigate the DB further. – Martin Staufcik Oct 08 '15 at 08:23
  • No problem - good to solve the mystery. If you want to find the biggest objects in the schema - select segment_name, sum(bytes)/1024/1024 from dba_segments where owner = 'NATURAMAN' group by segment_name – Stephen ODonnell Oct 08 '15 at 08:28

1 Answers1

0

Split into multiple dump files

expdp usr1/usr1 tables=tbl_test directory=dp_dir dumpfile=test_dump_%u.dmp filesize=20m

Cheers Brian

user1979139
  • 764
  • 5
  • 5