Home » RDBMS Server » Server Utilities » imp triggers fails (from 9.0 win2003 to 10gR2 on AIX 5.2)
imp triggers fails [message #310099] Mon, 31 March 2008 05:23 Go to next message
maoro
Messages: 310
Registered: May 2005
Senior Member
hello
during a project to migrate a database from 9.0 on win server 2003 to 10g r2 on AIX 5.2, i used export/import with the followinf steps:

-create a database 10gR2 on the destination server , with the same name as the original database

-create tablespaces on the 10gR2 database like all the ones that existed on the 9.0 database


then i copied the export file from the 9.0 database to a cd and copy the file to the 10gR2 server
then i imported the dmp file, at the end i got a message confirming that import was terminated successfully with warning.

later i did a comparaison at schema level between the databases,everything was ok except abt 1500 trigger in a schema named "sales"
i did another import using fromuser touser options
also i got a message confirming that import was terminated successfully with warning.
but still the triggers within this schema were not exported;
any help is really really appreciated
also a way to pull out the DDL from the 9.0 database of the 1500 trigger
would be a great alternative
thanks gurus
Re: imp triggers fails [message #310110 is a reply to message #310099] Mon, 31 March 2008 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which errors?

Regards
Michel
Re: imp triggers fails [message #310148 is a reply to message #310099] Mon, 31 March 2008 07:21 Go to previous messageGo to next message
maoro
Messages: 310
Registered: May 2005
Senior Member
no errors were logged
except when i compare the 2 databases the 1500 triggers of the sales schema were not imported
please help
Re: imp triggers fails [message #310166 is a reply to message #310148] Mon, 31 March 2008 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what were the warnings?

Regards
Michel
Re: imp triggers fails [message #310179 is a reply to message #310099] Mon, 31 March 2008 08:40 Go to previous messageGo to next message
maoro
Messages: 310
Registered: May 2005
Senior Member
warnings like that table,sequence or other (but not triggers) was not created because it already exist...
Re: imp triggers fails [message #310184 is a reply to message #310179] Mon, 31 March 2008 09:08 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

...was not created because it already exist...
then first import full tables triggers will import with related tables.
Re: imp triggers fails [message #310517 is a reply to message #310099] Tue, 01 April 2008 08:20 Go to previous messageGo to next message
maoro
Messages: 310
Registered: May 2005
Senior Member
thanks for kind help

another question
lets say that there is a table named "A" on database "alfa" that has 150 records
and the same tabe exist on another database "beta" named also "A" but with 200 record

both tables have te same owners on the 2 databases;

if i export the 200 record table and import it into the 150 record table ,would they be synchronized means 200 by 200 records in both tables?
or the import wont import rows cause the table alredy exist ?
thanks
Re: imp triggers fails [message #310524 is a reply to message #310099] Tue, 01 April 2008 08:44 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>if i export the 200 record table and import it into the 150 record table ,would they be synchronized means 200 by 200 records in both tables?
NO, & how many records depends upon constraints (or not)
>or the import wont import rows cause the table alredy exist ?
see response above
You could possibly end up with 350 records in table.
Re: imp triggers fails [message #310532 is a reply to message #310099] Tue, 01 April 2008 09:18 Go to previous messageGo to next message
maoro
Messages: 310
Registered: May 2005
Senior Member
can you elaborate more please..
i mean since the 50 records in difference does not exist in the target table and never violate any constraints...

thanks for kind help
Re: imp triggers fails [message #310536 is a reply to message #310099] Tue, 01 April 2008 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
If TableA has UNIQUE constrainst, it could cause 1 or more rows not to be INSERTed from TableB into TableA.
Without any UNIQUE constraint on TableA, then all 200 new rows get INSERTED into TableA.

So the actual results depends upon CONSTRAINT (or not) on TableA & actual data values in TableB.

[Updated on: Tue, 01 April 2008 09:27] by Moderator

Report message to a moderator

Re: imp triggers fails [message #310730 is a reply to message #310099] Wed, 02 April 2008 04:19 Go to previous messageGo to next message
maoro
Messages: 310
Registered: May 2005
Senior Member
so i guess
that we should not import data over an existing table with no primary or unique key..
that might produce duplicate rows.

but if a primary or unique key exist on the table then specifying ignore=y during import will synchronize the 2 tables
Am i right ?

thanks for assistance
Re: imp triggers fails [message #310735 is a reply to message #310730] Wed, 02 April 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
we should not import data over an existing table with no primary or unique key

Wrong, if yuo have no primary or unique key this means you don't care about duplicates.

Quote:
but if a primary or unique key exist on the table then specifying ignore=y during import will synchronize the 2 tables

No, if part of rows that is not in the primary/unique key is different then there is no synchronization.

Regards
Michel
Re: imp triggers fails [message #312513 is a reply to message #310099] Wed, 09 April 2008 01:54 Go to previous message
maoro
Messages: 310
Registered: May 2005
Senior Member
thas was really clear and helpfull
thank you so much
Previous Topic: Loading clob data in Table by using sql loader
Next Topic: Import and export
Goto Forum:
  


Current Time: Tue Dec 06 12:30:21 CST 2016

Total time taken to generate the page: 0.05237 seconds