OraFAQ Forum: Server Utilities » Import dmp file from server to another server

Home » RDBMS Server » Server Utilities » Import dmp file from server to another server

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Import dmp file from server to another server [message #198357]

Mon, 16 October 2006 15:00

khmba
Messages: 19
Registered: October 2006

Junior Member

Hello,
I'm working under Oracle 10g .
every day i export data from server No1.

I have an export file sp.dmp done with the following options :
exp system/manager owner=sp file=c:\sp.dmp

The export is ok,

I want to import this file to another server "server No2'.
sp is already existing in server No2 'tables,data,...'
I try to import the exported file with :
imp system/manager fromuser=sp touser=sp file=c:\sp.dmp ignore=y .
it takes to much times and some data not exported.
how can I import faster and no data lose ?

Thanks for help.

Report message to a moderator

Re: Import dmp file from server to another server [message #198358 is a reply to message #198357]

Mon, 16 October 2006 15:28

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What does it mean, "too much time"? Compared to what? Did it (once upon a time) take much less to import? Perhaps there's more data now than before?

Data loss: what does this mean? When you, for example, SELECT COUNT(*) FROM server_1_table, is it different from SELECT COUNT(*) FROM server_2_table? Or do you, perhaps, refer to invalid views and stored procedures after import?

Because, basically (unless I'm wrong about it), your EXP and IMP commands seem to be OK.

Report message to a moderator

Re: Import dmp file from server to another server [message #198410 is a reply to message #198357]

Tue, 17 October 2006 02:12

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Try to ADD ON ur imp command
"DIRECT=Y"

[Updated on: Tue, 17 October 2006 02:13]

Report message to a moderator

Re: Import dmp file from server to another server [message #198451 is a reply to message #198410]

Tue, 17 October 2006 04:09

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Quote:

Try to ADD ON ur imp command
"DIRECT=Y

I understand that was a glitch.
DIRECT=y applies to sqlldr only. Smile

Report message to a moderator

Re: Import dmp file from server to another server [message #198454 is a reply to message #198451]

Tue, 17 October 2006 04:23

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

oops sorry
Direct = y clause used only in "Exp" not in "imp"

thanx to correct me.

Report message to a moderator

Re: Import dmp file from server to another server [message #198519 is a reply to message #198357]

Tue, 17 October 2006 08:34

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

khmba wrote on Mon, 16 October 2006 16:00

imp system/manager fromuser=sp touser=sp file=c:\sp.dmp ignore=y .
it takes to much times and some data not exported.
how can I import faster and no data lose ?

I don't understand. What do you mean some data not exported? You are importing not exporting.
If all your tables and indexes exist beforehend, then your import will be very slow as it is updating the index for every single row of the import.
You do understand that import is going to add all the data to the tables and not replace them, right?

Report message to a moderator

Re: Import dmp file from server to another server [message #199031 is a reply to message #198357]

Thu, 19 October 2006 14:33

khmba
Messages: 19
Registered: October 2006

Junior Member

Thank for all
Yes In server No2 there is tables, indexes, triggers, procedures …
When I imported sp.dmp file
Imp system/manager fromuser=sp touser=sp file=c: \sp.dmp
It takes to many times.
Data loss: what does this mean? I mean
Not all data imported
For example
There are tables
1- bills (master)
2- bill_dtls (details)
In table bills in server No1 40270 rows in server N02 is same 40270 rows
But in table bill_dtls in server N01 168083 rows but in server No2 109617 rows.
Also I have other master details tables.
When I dropped sp users and re-create user sp and imported sp.dmp file
Everything is ok.
Data in server No1 same data in server No2.
Is there any way to import file without drop user?
Thanks.

Report message to a moderator

Re: Import dmp file from server to another server [message #199092 is a reply to message #199031]

Fri, 20 October 2006 02:18

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If I'm not wrong, IMPORT utility is clever and disables all constraints before import process begins. This means that, although tables are dependant on each other (through referential integrity), it doesn't matter in which order tables are imported. In other words, IMPORT might first import a detail table and the its master - it won't fail.

As you've said that "another" schema already exists (has tables etc.), IGNORE=Y option will suppress error messages regarding CREATE TABLE statements and continue importing data; stored procedures are created as CREATE OR REPLACE, so it doesn't matter, all views are created as CREATE FORCE VIEW - all those objects will automatically recompile when needed.

But, what about referential integrity constraints? Well, I'm not sure about it. If export file doesn't contain too many tables, you might try to manually import tables in required order - master first, detail second (specify them using the TABLES identifier of the IMPORT command, one by one).

However, I might be completely wrong about the whole thing and this might not be the issue at all.

Report message to a moderator

Re: Import dmp file from server to another server [message #199111 is a reply to message #199092]

Fri, 20 October 2006 04:28

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Import will take care of Parent/child tables. We need to do nothing ( as long as imported data is valid )

Report message to a moderator

Re: Import dmp file from server to another server [message #199119 is a reply to message #199111]

Fri, 20 October 2006 05:22

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Huh, right ... that's exactly what I've expected you to say, Mahesh.

Can you explain this behaviour (slightly modified OP's post) (i.e. different number of records in 'bill_dtls' table on both servers):

Server  Table 'bills'  Table 'bill_dtls'
------  -------------  -----------------
No1     40270          168083
No2     40270          109617

Report message to a moderator

Re: Import dmp file from server to another server [message #199127 is a reply to message #199119]

Fri, 20 October 2006 06:08

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Seems the OP got the concept
>> When I dropped sp users and re-create user sp and imported sp.dmp file Everything is ok.

By default import will append (with ignore=y)
If there are constraints already defined in target, some records may be skipped because of constraint violation.

The OP must 'clean' the data before importing.
Dropping the user or just dropping the tables (cascade option) would be helpful.
All we(you/me/others) can do is, build some conspiracy theory around this Razz

and guess what is happening. Without logfiles/session snapshots, a definitive know-how is not in vicinity.
Regards~

Report message to a moderator

Re: Import dmp file from server to another server [message #199139 is a reply to message #199127]

Fri, 20 October 2006 08:57

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"If there are constraints already defined in target, some records may be skipped because of constraint violation."

I see ... although Import utility itself takes care about referential integrity constraints while doing an import into an "empty" schema, it can not resolve already existing (and enabled) constraints. OK, thank you, Mahesh!

Report message to a moderator

Re: Import dmp file from server to another server [message #199145 is a reply to message #199139]

Fri, 20 October 2006 09:51

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Hi littlefoot~
Previosly I stated
>>If there are constraints already defined in target, some records may be skipped because of constraint violation
I think it is incomplete. Sorry. May be i was not clear before.
To comment on your statement
>>although Import utility itself takes care about referential integrity constraints while doing an import into an "empty" schema,
"empty" schema for me is a schema without any objects/tables and data.
In such case, import will be successful ( as OP has found it. When OP is dropping the user and starting over , everything is fine).

>>it can not resolve already existing (and enabled) constraints. OK

That is also true when the schema is having tables/constraints and data.
A new import on same schema with ignore=y (which will ignore error messages due to presence of objects) will try to append the data into existing tables.
If the constraints are enabled there might be some violation errors.

A third case (which OP may be experiencing)
Schema 1: is the source schema which has the tables/objects and live data.
Schema 2: is the target schema which has the tables/object and old data.
Objective: Export data from Schema 1 and import into Schema 2. (without data or with data. But the let the constraints be enabled).
What if the source schema is already having some constraint violations?
It is not unusual for a schema to have its constraints disabled or having a violation like duplicate keys and left unnoticed.
An export from such schema and import into target will fail because in target database the constraints are enabled.

Report message to a moderator

Re: Import dmp file from server to another server [message #199217 is a reply to message #199145]

Sat, 21 October 2006 12:24

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Thank you for the effort and all the time you put into answering this, Mahesh.

Report message to a moderator

Previous Topic:	loading data into two tables from single input file
Next Topic:	Getting to know size of tables from Dump?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 00:01:04 CST 2024