Expdp with metadata_only gives way too large dump. Why

dumpexportoracle

There is an oracle 11g schema with amount of data about 700 Gb. There are tables/indexes only in that schema, no procedures/packages.

Doing expdp on that schema

expdp schemas=TheSchema directory=dir dumpfile=dump logfile=log content=metadata_only

, I expected quite a litte dumpfile because of metadata_only. Doing tools/export-user-objects in pl/sql developer gives an sql file with text size a half of megabyte.

But instead there appeared a 20 Gb dumpfile!

Is there something large in dumpfile created by this expdp, like index contents?

What could be done to minimize the dumpfile done with expdp, if I want just to create tables like in TheSchema in another schema?

UP: There is quite a few number of tables/indexes in the text-sql-dump of schema:

> grep "create table" TheSchema.sql | wc -l
523

> grep "index" TheSchema.sql | wc -l
501

> grep "partition" TheSchema.sql | wc -l
96

Best Answer

What version are you running? I'm not able to reproduce your results on 11.2.0.4. Here's my schema:

16:36:09 system@DB> select owner, sum(bytes)/1024/1024/1024 GB from dba_segments where owner = 'USER' group by owner;

OWNER                                  GB
------------------------------ ----------
USER                           1156.41901

Here's the export statement: expdp system/x schemas=user directory=DATA_PUMP_DIR content=metadata_only logfile=test_meta.log

Here's the sizing on the dump file:

oracle@db01:(db1) ~]$ ls -ltrh /u01/exports/db/expdat.dmp
-rw-r----- 1 oracle oinstall 25M Nov 20 16:40 /u01/exports/db/expdat.dmp

Make sure you're using SYSTEM to do exports, as sysdba privs are not recommended for running data pump (see Invoking Data Pump Export. I wonder if that might be the problem.

Related Question