1

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
4
  • Hmm, how many tables/indexes are there, and how big are their initial extents? Have the tables got lots of partitions?
    – Philᵀᴹ
    Commented Oct 29, 2014 at 13:45
  • @Phil see updates in the question. I'm quite a noob in this, so can't understand what you mean by 'initial extents'. Commented Oct 30, 2014 at 4:23
  • May it be table statistics or index statistics? Commented Nov 8, 2014 at 5:25
  • May be even the export datapump is a metada_only, the SYS.AUD$ is beging exporting. Lets try export as system account.
    – user99365
    Commented Jul 5, 2016 at 11:38

1 Answer 1

5

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.

Your Answer

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

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