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
  • Hmm, how many tables/indexes are there, and how big are their initial extents? Have the tables got lots of partitions? – Philᵀᴹ Oct 29 '14 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'. – Andrey Regentov Oct 30 '14 at 4:23
  • May it be table statistics or index statistics? – Andrey Regentov Nov 8 '14 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 Jul 5 '16 at 11:38
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, privacy policy and cookie policy

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