• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 965
  • Last Modified:

Exp/imp utility

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
dedean01
Asked:
dedean01
  • 3
  • 2
1 Solution
 
JankovskyCommented:
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
 
JankovskyCommented:
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
 
dedean01Author Commented:
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
 
JankovskyCommented:
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
 
dedean01Author Commented:
Thank you I took the direct=y out and the data transfer was a successful

thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now