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.
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>