OraFAQ Forum: Server Utilities » Import ERROR

Home » RDBMS Server » Server Utilities » Import ERROR (Oracle Database 10g Enterprise Edition , 10.2.0.4.0,Linux64_86)

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

Import ERROR [message #654799]

Sat, 13 August 2016 09:49

abdo2203
Messages: 22
Registered: August 2016

Junior Member

I have two systems PROD and TEST of managing HR

i would like to import dump file from PROD to the TEST Envirement but the import encountred errors like imp-00019 row rejected due to oracle error 1
i used this command in oracle user :
imp userid=system/password file=Dump_08_2016.dmp fromuser=grhm touser=grhm ignore=y

i got this errors :

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (GRHM.PK_H_ZONETMP) violated
Column 1 18413U
Column 2 26-JUL-2016:00:00:00
Column 3 004
Column 4 30P94
Column 5 29-JUL-2016:00:00:00
Column 6 J
Column 7 5
Column 8 SHDPHR
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (GRHM.PK_H_ZONETMP) violated
Column 1 23056G
Column 2 30-JUL-2016:00:00:00

Regards

Report message to a moderator

Re: Import ERROR [message #654800 is a reply to message #654799]

Sat, 13 August 2016 09:52

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

As you persist ignoring my request to use [code] tags, I shall ignore your posts from now on. Perhaps someone else will assist.

Report message to a moderator

Re: Import ERROR [message #654801 is a reply to message #654799]

Sat, 13 August 2016 10:03

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So what is your actual problem?
You don't understand the error message?
You don't understand the origin of the message?
You don't know how to fix it?

When you say you import from PROD to TEST, do you mean you replace what is int TEST by PROD content or do you want to APPEND prod data to test ones?

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also take care where you post your questions, there are multiple fora, post your question in the appropriate one.
I move this topic to "Server Utilities" forum to which it belongs.

[Updated on: Sat, 13 August 2016 10:06]

Report message to a moderator

Re: Import ERROR [message #654802 is a reply to message #654800]

Sat, 13 August 2016 10:06

abdo2203
Messages: 22
Registered: August 2016

Junior Member

Hi John

I am not ignoring your request ,I don't know how to use them , sorry for misunderstanding

I will try to repost this topic with tags

regards

Report message to a moderator

Re: Import ERROR [message #654803 is a reply to message #654802]

Sat, 13 August 2016 10:16

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just click on the links I gave you.

Report message to a moderator

Re: Import ERROR [message #654804 is a reply to message #654803]

Sat, 13 August 2016 10:22

abdo2203
Messages: 22
Registered: August 2016

Junior Member

I have two systems PROD and TEST of managing HR

i would like to import dump file from PROD to the TEST Envirement but the import encountred errors like imp-00019 row rejected due to oracle error 1
i used this command in oracle user :

imp userid=system/password file=Dump_08_2016.dmp fromuser=grhm touser=grhm ignore=y

i got this errors :

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (GRHM.PK_H_ZONETMP) violated
Column 1 18413U
Column 2 26-JUL-2016:00:00:00
Column 3 004
Column 4 30P94
Column 5 29-JUL-2016:00:00:00
Column 6 J
Column 7 5
Column 8 SHDPHR
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (GRHM.PK_H_ZONETMP) violated
Column 1 23056G
Column 2 30-JUL-2016:00:00:00

Regards

[Edit MC: please apply code tags to code, result or command line, not on all the post.]

[Updated on: Sat, 13 August 2016 10:29] by Moderator

Report message to a moderator

Re: Import ERROR [message #654805 is a reply to message #654804]

Sat, 13 August 2016 10:25

abdo2203
Messages: 22
Registered: August 2016

Junior Member

Hi Michel,

So what is your actual problem?
my problem is the import not performing on the test system
You don't understand the error message?
yes
You don't understand the origin of the message?
yes
You don't know how to fix it?
yes

When you say you import from PROD to TEST, do you mean you replace what is int TEST by PROD content or do you want to APPEND prod data to test ones?
Actually the TEST System has existant information which will be replaced by the new import

regards

[Updated on: Sat, 13 August 2016 10:29] by Moderator

Report message to a moderator

Re: Import ERROR [message #654806 is a reply to message #654804]

Sat, 13 August 2016 10:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>IGNORE ignore create errors (N)
IGNORE=YES does not ignore all errors.

It appears that table GRHM already exist with a PK & you are trying to load duplicate row.
A number of alternatives exist that might provide a workaround to this depending upon your requirements; such as TRUNCATE TABLE before doing the import.

Report message to a moderator

Re: Import ERROR [message #654807 is a reply to message #654805]

Sat, 13 August 2016 10:30

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Actually the TEST System has existant information which will be replaced by the new import

So drop the schema before importing it.

Report message to a moderator

Re: Import ERROR [message #654808 is a reply to message #654807]

Sat, 13 August 2016 10:38

abdo2203
Messages: 22
Registered: August 2016

Junior Member

Please how to drop or truncate the schema in order to load the new dump

[Updated on: Sat, 13 August 2016 10:40] by Moderator

Report message to a moderator

Re: Import ERROR [message #654809 is a reply to message #654808]

Sat, 13 August 2016 10:40

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please apply code tags to code, result or command line, not on all the post.

DROP USER grhm CASCADE;

Report message to a moderator

Re: Import ERROR [message #654810 is a reply to message #654809]

Sat, 13 August 2016 10:44

abdo2203
Messages: 22
Registered: August 2016

Junior Member

is dropping the schema will execute import command correctly without any further command line ?

Report message to a moderator

Re: Import ERROR [message #654811 is a reply to message #654810]

Sat, 13 August 2016 10:50

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes (for the current command) but don't trust me, try it!
Also, as you use the old export/import and not Data Pump, you have to recreate the account before importing.

[Updated on: Sat, 13 August 2016 10:51]

Report message to a moderator

Re: Import ERROR [message #654814 is a reply to message #654811]

Sun, 14 August 2016 11:15

abdo2203
Messages: 22
Registered: August 2016

Junior Member

Hi Michel
is that command usefull to do the import properly

select 'drop table '||table_name||' cascade constraints;' from user_tables;

regards

Report message to a moderator

Re: Import ERROR [message #654815 is a reply to message #654814]

Sun, 14 August 2016 11:23

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes and no, it depends on the dependencies between the tables.
Search for "delme michel" on AskTom, you will find a script to delete all your own objects I posted many years ago.

Report message to a moderator

Re: Import ERROR [message #654816 is a reply to message #654814]

Sun, 14 August 2016 11:58

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

You may have to generate more scripts to drop other objects: sequences, procedures, functions, packages, views.
It may be easier to generate the DDL to re-create the user and then drop him:

set ling 10000
select dbms_metadata.get_ddl('USER','GHRM') from dual;

Report message to a moderator

Re: Import ERROR [message #654817 is a reply to message #654816]

Sun, 14 August 2016 12:31

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Also use GET_DEPENDENT_DDL to retrieve the privileges.
But the best way is to use Data Pump instead of exp/imp.

Report message to a moderator

Re: Import ERROR [message #654827 is a reply to message #654817]

Mon, 15 August 2016 11:51

abdo2203
Messages: 22
Registered: August 2016

Junior Member

thank you very much it works

Report message to a moderator

Re: Import ERROR [message #654828 is a reply to message #654827]

Mon, 15 August 2016 12:11

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What did you do in the end?

Report message to a moderator

Re: Import ERROR [message #654829 is a reply to message #654828]

Mon, 15 August 2016 12:26

abdo2203
Messages: 22
Registered: August 2016

Junior Member

i perform the following :

DROP USER grhm CASCADE;

create user grhm identified by grhm  ;


grant all privileges to grhm  identified by grhm;


GRANT UNLIMITED TABLESPACE TO grhm

after executing this scrip the import was successfully done;

Many thanks

Report message to a moderator

Re: Import ERROR [message #654830 is a reply to message #654829]

Mon, 15 August 2016 12:31

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do not grant all privileges to a account, only grant the privileges it needs, granting all privileges makes it doing anything in your database like removing or modifying all accounts, objects and data.
Granting it all privileges is stronger than granting it DBA role.

[Updated on: Mon, 15 August 2016 12:32]

Report message to a moderator

Re: Import ERROR [message #654831 is a reply to message #654830]

Mon, 15 August 2016 12:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>grant all privileges to grhm identified by grhm;
now GRHM can do as below

DROP DATABASE;

Report message to a moderator

Re: Import ERROR [message #654832 is a reply to message #654830]

Mon, 15 August 2016 12:40

abdo2203
Messages: 22
Registered: August 2016

Junior Member

i have revoked the privileges after teh import operation , and i granted only connect, create session, imp_full_database

Report message to a moderator

Re: Import ERROR [message #654833 is a reply to message #654832]

Mon, 15 August 2016 12:46

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

IMP_FULL_DATABASE is like DBA, there is few differences, it can do all the things I mentioned in my previous post.
Actually, those who have IMP_FULL_DATABASE role can grant themselves DBA role.

[Updated on: Mon, 15 August 2016 12:46]

Report message to a moderator

Re: Import ERROR [message #654834 is a reply to message #654833]

Mon, 15 August 2016 12:59

abdo2203
Messages: 22
Registered: August 2016

Junior Member

So i will revoke the IMP_FULL_DATABASE role

Report message to a moderator

Re: Import ERROR [message #654835 is a reply to message #654834]

Mon, 15 August 2016 13:32

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you use a DBA account and not the target one to import this later you don't even need to grant it IMP_FULL_DATABASE in the first place.

And do not grant UNLIMITED TABLESPACE, this is also a root of troubles. Give quota to the account on its target tablespaces only.
So in short, to import, you only need CREATE USER statement.

Of course, to work with it afterwards you may need to grant it some privileges, at least, CREATE SESSION.

Report message to a moderator

Re: Import ERROR [message #654877 is a reply to message #654832]

Tue, 16 August 2016 06:34

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

abdo2203 wrote on Mon, 15 August 2016 12:40

i have revoked the privileges after teh import operation , and i granted only connect, create session, imp_full_database

Do not grant CONNECT. That is not a privilege, but a role. A named collection of privileges. And it may very well contain some very powerful privileges that your user shouldn't have. If you want yor user to be be able to connect, then just grant CREATE SESSION. And consider whether the user needs to be able to even do that. It is quite common for a schema to exist for the sole purpose of holding objects that are used by other users, and there is no need for the schema owner to even be able to log on.

Report message to a moderator

Previous Topic:	Backup Some table
Next Topic:	impdp error - bad dump file specification

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 07 01:28:01 CST 2025