OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » Data import

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

Data import [message #219620]

Thu, 15 February 2007 03:34

moinuddin_sh
Messages: 3
Registered: February 2007

Junior Member

Hi everyone,

I am new to Oracle database. I tried frollowing command to import data from an existing backup dmp file but it fails with error

imp sys/manager@TAL file=d:\moin\daily.dmp fromuser=fais touser=fais ignore=y log=d:\moin\daily.log

The error is given below

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (TAL.BE_SEED_PK) violated

How can I restore the backup normally?

Regards

Report message to a moderator

Re: Data import [message #219635 is a reply to message #219620]

Thu, 15 February 2007 04:16

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

Senior Member
Account Moderator

>> ORA-00001: unique constraint (TAL.BE_SEED_PK) violated
You need to cleanup the existing rows first.
Some rows are getting duplicated and so the constraint is violated.

Report message to a moderator

Re: Data import [message #219685 is a reply to message #219635]

Thu, 15 February 2007 09:27

moinuddin_sh
Messages: 3
Registered: February 2007

Junior Member

Thanks for your reply, when I added in my statement constraints=n then a different error received, as given below:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and UTF8 NCHAR character set
. importing SYSTEM's objects into SYSTEM
. importing PERFSTAT's objects into PERFSTAT
. importing TAL's objects into TAL
. importing FAS's objects into FAS
IMP-00015: following statement failed because the object already exists:
"CREATE SYNONYM "ACTIVE_INTERFACE_LIST" FOR "TAL"."ACTIVE_INTERFACE_LIST""
IMP-00015: following statement failed because the object already exists:
"CREATE SYNONYM "ACTIVE_INTERFACE_LIST_TABLE" FOR "TAL"."ACTIVE_INTERFACE_"
"LIST_TABLE""
IMP-00015: following statement failed because the object already exists:
"CREATE SYNONYM "ADVGENTYPE" FOR "TAL"."ADVGENTYPE""
IMP-00015: following statement failed because the object already exists:
"CREATE SYNONYM "ADVGENTYPETABLE" FOR "TAL"."ADVGENTYPETABLE""
IMP-00015: following statement failed because the object already exists:
"CREATE SYNONYM "BLOCKGRIDTYPE" FOR "TAL"."BLOCKGRIDTYPE""
IMP-00015: following statement failed because the object already exists:
"CREATE SYNONYM "BUSINESS_EVENT" FOR "TAL"."BUSINESS_EVENT""
IMP-00015: following statement failed because the object already exists:
"CREATE SYNONYM "CHARTYPE" FOR "TAL"."CHARTYPE""
IMP-00015: following statement failed because the object already exists:
"CREATE SYNONYM "CODE_DESCRIPTION" FOR "TAL"."CODE_DESCRIPTION""
IMP-00015: following statement failed because the object already exists:

IMP-00061: Warning: Object type "FAS"."HX_PHONE" already exists with a different identifier
"CREATE TYPE "HX_PHONE" TIMESTAMP '2004-09-29:09:22:49' OID '6B4C03D0E690400"
"E8C928F82B6CF9C1A' wrapped"

Report message to a moderator

Re: Data import [message #219686 is a reply to message #219685]

Thu, 15 February 2007 09:31

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

Senior Member

hi,

IMP-00015: following statement failed because the object already exists:

first drop all object create during previous import operation and after do fresh import with constraint=n option.

what is your actually need ?

regards
Taj

Report message to a moderator

Re: Data import [message #219750 is a reply to message #219686]

Thu, 15 February 2007 17:17

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

Senior Member
Account Moderator

Or, perhaps, use import's parameter IGNORE=Y (which might help in a case when an object already exists in this particular schema).

Also, you could look at it from the bright side: if nothing changed about those synonyms, you shouldn't worry about them - synonyms exist and that's all - no need to import (create) them again.

Report message to a moderator

Re: Data import [message #219842 is a reply to message #219750]

Fri, 16 February 2007 05:52

moinuddin_sh
Messages: 3
Registered: February 2007

Junior Member

Hi Taj,

I take daily backup through Veritas of my Oracle database running on a Windows 2003 server. I also have a standby (backup) server that I keep as plan B. I feel to restore through Veritas is time taking procedure, so I took a backup through "exp" command from my live server and I want to restore/update this data on my existing backup server. On this backup server I already have the same database but it is not with the recent data. Meaning both the servers have identical database but live server have latest and standby have the old data.

For data export from the live server I used following statement:

exp sys/manager@TAL file=d:\moin\daily.dmp full=y compress=y log=d:\moin\daily.log

and for import following command

imp sys/manager@TAL file=d:\moin\daily.dmp fromuser=fais touser=fais constraints=n ignore=y log=d:\moin\daily.log

Hope it is clear to u.

best regards

Moin

Report message to a moderator

Re: Data import [message #219846 is a reply to message #219842]

Fri, 16 February 2007 06:03

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

Senior Member
Account Moderator

>> I want to restore/update this data
Import will not "update" the data.
By default it will append.
If there is any constraints enable in database or through your import command, they may violate (your case).
As we said already, drop all the objects in target before importing.
>>exp sys/manager@TAL file=d:\moin\daily.dmp full=y compress=y log=d:\moin\daily.log
compress=y means you are asking for trouble.
Always use compress=n
>>imp sys/manager@TAL file=d:\moin\daily.dmp fromuser=fais touser=fais constraints=n ignore=y log=d:\moin\daily.log
with constraints=n, you are disableing constraints in dump file only.
Still, the target database has constraints and rows which are violating.
drop all the objects and import again.

>>I feel to restore through Veritas is time taking procedure
If "veritas" backup is hot/cold/rman backup, it is much better than exp/imp.

Report message to a moderator

Re: Data import [message #223525 is a reply to message #219846]

Fri, 09 March 2007 06:16

rojo72
Messages: 7
Registered: March 2007

Junior Member

What is the process for dropping all objects in the existing database so the IMP will go through successful? I am having trouble finding the commands for dropping the schema prior to using the IMP command.

Thanks,

Rojo

Report message to a moderator

Re: Data import [message #223526 is a reply to message #223525]

Fri, 09 March 2007 06:19

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

Senior Member
Account Moderator

You cannot do with IMP commands (atleast upto 9x. Not sure in 10g).
Use sqlplus to drop the objects prior to import.

Report message to a moderator

Re: Data import [message #223529 is a reply to message #219620]

Fri, 09 March 2007 06:30

rojo72
Messages: 7
Registered: March 2007

Junior Member

Is there a command to drop all objects using sql*plus?

Thanks,

Report message to a moderator

Re: Data import [message #223533 is a reply to message #223529]

Fri, 09 March 2007 06:38

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

Senior Member
Account Moderator

You can drop the whole schema
or
generate an sql do drop all objects in your schema
or
use dynamic sql.
Search the forum. There are many examples.

Report message to a moderator

Previous Topic:	IMP-00017:
Next Topic:	sql loader and foreign keys

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 16:41:47 CDT 2024