?
Solved

Exp/imp utility

Posted on 2005-03-30
5
Medium Priority
?
962 Views
Last Modified: 2008-02-26
I did an export of a user schema and then did an import to another schema, all of the rows transfered over but some of the data in the rows where not consistant .  I used consistant=y while doing the export.  I was wondering if the consistant=y only work when do a full export of the database by the user system.

my exp parfile looked like this
userid=payrole/partool1
log=export_parole.log
file=export_parole.dmp
buffer=400000
rows=y
grants=y
direct=y
consistent=y
statistics=none


my imp parfile looks like this
userid=dev_parole/devpartool1
file=export_parole.dmp
log=imp_from_parole.log
buffer=400000
fromuser=parole
touser=dev_parole
ignore=y
gransts=y
indexes=y
commit=y
0
Comment
Question by:dedean01
[X]
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
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:Jankovsky
ID: 13661379
Of course,
consistent=Y means, all the data will be exported in the state representing the same point of time.

I think it solves your problem.

0
 
LVL 6

Expert Comment

by:Jankovsky
ID: 13661398
Pay attention:
CONSISTENT=y is unsupported for exports that are performed when you are connected as user SYS or you are using AS SYSDBA, or both.

0
 

Author Comment

by:dedean01
ID: 13661775
I used consistent =y, but the data was missing key information when I imported the data into the other schema.  I thought that I read some where that the parameter consistent=y will only work with a full export of the database by the user "system", is there any validity to that.  
0
 
LVL 6

Accepted Solution

by:
Jankovsky earned 200 total points
ID: 13662124
There is a possible reason you have enabled constraints and import data in the order of names (it does).
see:

"In the normal import order, referential constraints are imported only after all tables
are imported. This sequence prevents errors that could occur if a referential
integrity constraint exists for data that has not yet been imported.
These errors can still occur when data is loaded into existing tables. For example, if
table emp has a referential integrity constraint on the mgr column that verifies that
the manager number exists in emp, a legitimate employee row might fail the
referential integrity constraint if the manager's row has not yet been imported.
When such an error occurs, Import generates an error message, bypasses the failed
row, and continues importing other rows in the table. You can disable constraints
manually to avoid this.

To prevent errors like these, you should disable referential integrity constraints
when importing data into existing tables.

If it's not the cause, try set direct=n during export. It's not documented, but it seems it could be a problem.
0
 

Author Comment

by:dedean01
ID: 13666541
Thank you I took the direct=y out and the data transfer was a successful

thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

762 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