?
Solved

Oracle Export Missing Tables

Posted on 2011-04-19
10
Medium Priority
?
1,090 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:bmcdowell540
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 35428555
Can you try

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

Expert Comment

by:ajexpert
ID: 35428558

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

Open in new window

0
 
LVL 1

Author Comment

by:bmcdowell540
ID: 35428691
I get EXP-00026: conflicting modes specified
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 14

Expert Comment

by:ajexpert
ID: 35428750
This could be because you specified 2 OWNERS in parameter.  Try with 1 at at time
0
 
LVL 1

Author Comment

by:bmcdowell540
ID: 35428809
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35429217
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
 
LVL 3

Expert Comment

by:gopisera
ID: 35430243

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
 
LVL 5

Accepted Solution

by:
manzoor_dba earned 2000 total points
ID: 35441415
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
 
LVL 48

Expert Comment

by:schwertner
ID: 35442639
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35444328
if u use my method, you can specify the tablename that is missing or needed instead to have run ot all over again
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month13 days, 8 hours left to enroll

749 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