Oracle Export Missing Tables

I am trying to export all the objects from two users.  Currently the tables with no rows are not being exported, what am I missing?

exp userid = 'sys/password@database as sysdba' file=Dumpfile.dmp log=Logfile.log owner=USER1, USER2

Thanks
Brian
LVL 1
bmcdowell540Asked:
Who is Participating?
 
manzoor_dbaConnect With a Mentor Commented:
Hi,

Pls find the below snap, the tables with no rows are also been exported.

C:\Documents and Settings>exp userid='sys/admin@orcl_man as sysdba' file=test.dmp owner=(manzoor,test) log=test.log

Export: Release 10.2.0.1.0 - Production on Thu Apr 21 16:12:59 2011

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user MANZOOR
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MANZOOR
. exporting object type definitions for user TEST
About to export MANZOOR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MANZOOR's tables via Conventional Path ...
. . exporting table                     DEPARTMENT    1280000 rows exported
. . exporting table                            EMP        355 rows exported
. . exporting table                       EMP_PART
. . exporting partition                         PART_1          3 rows exported
. . exporting partition                         PART_2          7 rows exported
. . exporting table              HKA_FAILED_REPORT          0 rows exported
. . exporting table       HKA_FRAGMENTATION_REPORT     237972 rows exported
. . exporting table          HKA_JOBSBROKEN_REPORT        612 rows exported
. . exporting table           HKA_MAXEXTENT_REPORT         14 rows exported
. . exporting table             HKA_SESSION_REPORT       1507 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table                   TABLE_NN_LOG     300001 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Thanks..
0
 
ajexpertCommented:
Can you try

'sys/password@database as sysdba' file=Dumpfile.dmp log=Logfile.log owner=USER1, USER2 full=Y
0
 
ajexpertCommented:

exp userid = 'sys/password@database as sysdba' file=Dumpfile.dmp log=Logfile.log owner=USER1, USER2 FULL=Y

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
bmcdowell540Author Commented:
I get EXP-00026: conflicting modes specified
0
 
ajexpertCommented:
This could be because you specified 2 OWNERS in parameter.  Try with 1 at at time
0
 
bmcdowell540Author Commented:
I removed the second user, so the script looks like this and I get EXP-00026: conflicting modes specified

exp userid = 'sys/password@database as sysdba' file=Dumpfile.dmp log=Logfile.log owner=USER1 FULL=Y
0
 
OP_ZaharinCommented:
hi,
- you cannot specify user=user1 and full=y at the same time. that is why you are getting the conflicting error.
- just use this:
exp userid = 'sys/password@database as sysdba' file=Dumpfile.dmp log=Logfile.log owner=USER1
OR
exp userid = 'sys/password@database as sysdba' file=Dumpfile.dmp log=Logfile.log tables=(table1,table2)

- for the 2nd exp option, you might be prompt to input the owner of the tables, just input USER1 or USER2
0
 
gopiseraCommented:

Your statement will works good.  But it will export both the rows and the structure of the tables.

exp userid = 'sys/password@database as sysdba' file=Dumpfile.dmp log=Logfile.log owner=USER1, USER2

IF you require only structure but with out rows  then add  rows=no

exp userid = 'sys/password@database as sysdba' file=Dumpfile.dmp log=Logfile.log owner=USER1, USER2 rows=no


Thanks,

0
 
schwertnerCommented:
Export utility by schema level export always exports all tables (without or with rows).
So you have to check from which database you export and to check if there are tables without rows and all other facts. The most possible reason is that either there are no empty tables or you direct the import to the not correct database.
0
 
OP_ZaharinCommented:
if u use my method, you can specify the tablename that is missing or needed instead to have run ot all over again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.