Home » RDBMS Server » Server Utilities » Data import
Data import [message #219620] Thu, 15 February 2007 03:34 Go to next message
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
Re: Data import [message #219635 is a reply to message #219620] Thu, 15 February 2007 04:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: Data import [message #219685 is a reply to message #219635] Thu, 15 February 2007 09:27 Go to previous messageGo to next message
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"


Re: Data import [message #219686 is a reply to message #219685] Thu, 15 February 2007 09:31 Go to previous messageGo to next message
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
Re: Data import [message #219750 is a reply to message #219686] Thu, 15 February 2007 17:17 Go to previous messageGo to next message
Littlefoot
Messages: 20900
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.
Re: Data import [message #219842 is a reply to message #219750] Fri, 16 February 2007 05:52 Go to previous messageGo to next message
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
Re: Data import [message #219846 is a reply to message #219842] Fri, 16 February 2007 06:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: Data import [message #223525 is a reply to message #219846] Fri, 09 March 2007 06:16 Go to previous messageGo to next message
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
Re: Data import [message #223526 is a reply to message #223525] Fri, 09 March 2007 06:19 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: Data import [message #223529 is a reply to message #219620] Fri, 09 March 2007 06:30 Go to previous messageGo to next message
rojo72
Messages: 7
Registered: March 2007
Junior Member
Is there a command to drop all objects using sql*plus?

Thanks,

Re: Data import [message #223533 is a reply to message #223529] Fri, 09 March 2007 06:38 Go to previous message
Mahesh Rajendran
Messages: 10672
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.
Previous Topic: IMP-00017:
Next Topic: sql loader and foreign keys
Goto Forum:
  


Current Time: Wed Dec 07 22:30:23 CST 2016

Total time taken to generate the page: 0.10582 seconds