Exp Command not exporting 0 rows tables in oracle 11g

Posted on 2013-06-17
Last Modified: 2013-08-29

We are not able export 0 rows table using oracle 11g standard or enterprise edition.Can any body help me in this regards.

Question by:gotetioracle
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 49

Expert Comment

ID: 39252461
you may want to look at this forum:

not sure why you want this export - but maybe that thread will assist
LVL 29

Accepted Solution

MikeOM_DBA earned 500 total points
ID: 39260963
Perhaps in its fixed:

SQL>  select * from v$version;
Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE      Production
TNS for IBM/AIX RISC System/6000: Version - Production
NLSRTL Version - Production

SQL>  show parameter deferred

NAME                        TYPE          VALUE
--------------------------- ------------- ------------------------------
deferred_segment_creation   boolean       TRUE


Open in new window

Here is a snippet of the log from a test export, notice all the zero rows tables:
==> expdp / directory=mom dumpfile=scott.dmp schemas=scott

Export: Release - Production on Wed Jun 19 17:26:57 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_01":  /******** directory=mom dumpfile=scott.dmp schemas=scott
Estimate in progress using BLOCKS method...
. . . E t c . . .
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "SCOTT"."HEALTHCHECK"                       49.29 KB    1797 rows
. . exported "SCOTT"."CARD_FILE"                         6.023 KB       8 rows
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
. . exported "SCOTT"."EMP"                               8.570 KB      14 rows
. . exported "SCOTT"."EMP2"                              8.976 KB      14 rows
. . exported "SCOTT"."GROUPMEMBERSHIP"                   5.640 KB      13 rows
. . exported "SCOTT"."ITEM_RATE"                         5.984 KB       7 rows

.. . .   E t c   . . ..

. . exported "SCOTT"."WDIC"                              7.453 KB      10 rows
. . exported "SCOTT"."WELL"                              5.468 KB       1 rows
. . exported "SCOTT"."WELLTEST_MSR"                      6.156 KB      12 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."CT"                                    0 KB       0 rows
. . exported "SCOTT"."EMP3"                                  0 KB       0 rows
. . exported "SCOTT"."GARE"                                  0 KB       0 rows
. . exported "SCOTT"."GARA_CATEGORIE"                        0 KB       0 rows
. . exported "SCOTT"."INTERNAL_EXAMPLE"                      0 KB       0 rows
. . exported "SCOTT"."SO_WELLTEST_PLUS"                      0 KB       0 rows
. . exported "SCOTT"."WSF_TABLE"                             0 KB       0 rows
Master table "OPS$ORACLE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Dump file set for OPS$ORACLE.SYS_EXPORT_SCHEMA_01 is:
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:29:06

Open in new window


Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question