Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Exp Command not exporting 0 rows tables in oracle 11g

Posted on 2013-06-17
2
Medium Priority
?
1,594 Views
Last Modified: 2013-08-29
Hi,

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

Regards,
GSK
0
Comment
Question by:gotetioracle
2 Comments
 
LVL 49

Expert Comment

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

https://forums.oracle.com/thread/1091481

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

Accepted Solution

by:
MikeOM_DBA earned 2000 total points
ID: 39260963
Perhaps in 11.2.0.3 its fixed:

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

SQL>  show parameter deferred

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

SQL>

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 11.2.0.3.0 - 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 11.2.0.3.0 - 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...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . . E t c . . .
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . 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:
  /opt/oracle/scripts/mom/scott.dmp
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:29:06

Open in new window

:p
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

824 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