Home » RDBMS Server » Server Utilities » Import ERROR (Oracle Database 10g Enterprise Edition , 10.2.0.4.0,Linux64_86)
Import ERROR [message #654799] Sat, 13 August 2016 09:49 Go to next message
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
Re: Import ERROR [message #654800 is a reply to message #654799] Sat, 13 August 2016 09:52 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: Import ERROR [message #654801 is a reply to message #654799] Sat, 13 August 2016 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: Import ERROR [message #654803 is a reply to message #654802] Sat, 13 August 2016 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just click on the links I gave you.

Re: Import ERROR [message #654804 is a reply to message #654803] Sat, 13 August 2016 10:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Import ERROR [message #654807 is a reply to message #654805] Sat, 13 August 2016 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import ERROR [message #654808 is a reply to message #654807] Sat, 13 August 2016 10:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

DROP USER grhm CASCADE;
Re: Import ERROR [message #654810 is a reply to message #654809] Sat, 13 August 2016 10:44 Go to previous messageGo to next message
abdo2203
Messages: 22
Registered: August 2016
Junior Member
is dropping the schema will execute import command correctly without any further command line ?
Re: Import ERROR [message #654811 is a reply to message #654810] Sat, 13 August 2016 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: Import ERROR [message #654815 is a reply to message #654814] Sun, 14 August 2016 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import ERROR [message #654816 is a reply to message #654814] Sun, 14 August 2016 11:58 Go to previous messageGo to next message
John Watson
Messages: 8922
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;
Re: Import ERROR [message #654817 is a reply to message #654816] Sun, 14 August 2016 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import ERROR [message #654827 is a reply to message #654817] Mon, 15 August 2016 11:51 Go to previous messageGo to next message
abdo2203
Messages: 22
Registered: August 2016
Junior Member
thank you very much it works
Re: Import ERROR [message #654828 is a reply to message #654827] Mon, 15 August 2016 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What did you do in the end?

Re: Import ERROR [message #654829 is a reply to message #654828] Mon, 15 August 2016 12:26 Go to previous messageGo to next message
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
Re: Import ERROR [message #654830 is a reply to message #654829] Mon, 15 August 2016 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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;
Re: Import ERROR [message #654832 is a reply to message #654830] Mon, 15 August 2016 12:40 Go to previous messageGo to next message
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
Re: Import ERROR [message #654833 is a reply to message #654832] Mon, 15 August 2016 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
abdo2203
Messages: 22
Registered: August 2016
Junior Member
So i will revoke the IMP_FULL_DATABASE role

Re: Import ERROR [message #654835 is a reply to message #654834] Mon, 15 August 2016 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Import ERROR [message #654877 is a reply to message #654832] Tue, 16 August 2016 06:34 Go to previous message
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.
Previous Topic: Backup Some table
Next Topic: impdp error - bad dump file specification
Goto Forum:
  


Current Time: Thu Mar 28 12:39:37 CDT 2024