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
What version are you running? I’m not able to reproduce your results on 188.8.131.52. 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.