Skip to Main Content
  • Questions
  • Export/Import table in deferred mode.

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Javi.

Asked: February 16, 2011 - 10:37 am UTC

Last updated: May 23, 2013 - 2:28 pm UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi Tom, How can I export all tables of a Schema if the system pararameter "deferred_segment_creation" is setted true? I mean, when I export my database, all the that I have created and wich are empty doesn't include in my Dump file. How can I fix the problem? I have tried to export the schema with a simple export and with export datapump, but it doesn't works. Thanks for all. Javi.


and Tom said...

export is a deprecated, no longer enhanced, no longer used tool in 11.1 and above.
see Support Status and Alerts (Doc ID 454507.1)

Export DataPump is the tool to use and it works fine with deferred segment creation.



Export will not work with many new features - now and into the future.

Import (the old import) will continue to be supported for the purpose of importing into an 11.2 and above database exports from OLDER releases only.

[tkyte@localhost ~]$ <b>expdp userid=/ 'schemas=ops$tkyte'
</b>
Export: Release 11.2.0.2.0 - Production on Wed Feb 16 13:05:15 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "OPS$TKYTE"."SYS_EXPORT_SCHEMA_01":  userid=/******** schemas=ops$tkyte 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT<b>
. . exported "OPS$TKYTE"."T"                                 0 KB       0 rows
</b>Master table "OPS$TKYTE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$TKYTE.SYS_EXPORT_SCHEMA_01 is:
  /home/ora11gr2/app/ora11gr2/admin/ora11gr2/dpdump/expdat.dmp
Job "OPS$TKYTE"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:05:56

[tkyte@localhost ~]$ sqlplus /

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 16 13:06:04 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
<b>
ops$tkyte%ORA11GR2> select * from user_segments;

no rows selected

ops$tkyte%ORA11GR2> drop table t;

Table dropped.
</b>
ops$tkyte%ORA11GR2> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[tkyte@localhost ~]$ <b>impdp userid=/ file=/home/ora11gr2/app/ora11gr2/admin/ora11gr2/dpdump/expdat.dmp 'schemas=ops$tkyte'
</b>
Import: Release 11.2.0.2.0 - Production on Wed Feb 16 13:06:22 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=/home/ora11gr2/app/ora11gr2/admin/ora11gr2/dpdump/expdat.dmp" Location: Command Line, Replaced with: "dumpfile=DATA_PUMP_DIR:expdat.dmp"
Database Directory Object "DATA_PUMP_DIR" has been added to file specification: "/home/ora11gr2/app/ora11gr2/admin/ora11gr2/dpdump/expdat.dmp".
Legacy Mode has set nologfile=true parameter.
Master table "OPS$TKYTE"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "OPS$TKYTE"."SYS_IMPORT_SCHEMA_01":  userid=/******** dumpfile=DATA_PUMP_DIR:expdat.dmp schemas=ops$tkyte nologfile=true 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"OPS$TKYTE" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
<b>. . imported "OPS$TKYTE"."T"                                 0 KB       0 rows
</b>Job "OPS$TKYTE"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 13:06:27

[tkyte@localhost ~]$ plus

SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 16 13:06:34 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
<b>
ops$tkyte%ORA11GR2> desc t;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)
</b>

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Deferred Segment Creation and Oracle Export

Andrew Reid, May 22, 2013 - 10:01 pm UTC

Excellent stuff as always and I agree that datapump is the way to go. However, I noticed that tables with deferred segment creation are exported in 11.2.0.2. See here for a worked example:
http://international-dba.blogspot.co.uk/2013/05/oracle-export-and-deferred-segment.html

Tom Kyte
May 23, 2013 - 2:28 pm UTC

so?

export is not supported.
export is beyond deprecated.
export will have "funny behavior".

things might work, then again, they might not.

i would not rely on it at all.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.