Home » RDBMS Server » Server Utilities » problem with import
problem with import [message #217101] Wed, 31 January 2007 12:17 Go to next message
pavan27
Messages: 50
Registered: December 2006
Location: bangalore
Member
Hi to every one,

i have a problem while importing.
i took export of some tables which contains lot of data than 2 days back. i took entire tables data and trying to import in on the tables i am getting errors.errors are due to constraints. when i disable to constrains entire export dump is importing.

is there any way to to import with out disable constraints. could any one send me solution as soon as possible.

thanks and regards,
Re: problem with import [message #217103 is a reply to message #217101] Wed, 31 January 2007 12:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> i am getting errors.errors are due to constraints
Those are the constraints defined in your target database.
You need to disable them prior to import.

You can try import with constraints=n and ignore=y which will not force the constraints from dump. But, the database constraints will still revolt (if you already have some data that may be violating).
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96652/ch02.htm#1005366

[Updated on: Wed, 31 January 2007 12:32]

Report message to a moderator

Re: problem with import [message #217158 is a reply to message #217103] Wed, 31 January 2007 20:56 Go to previous messageGo to next message
pavan27
Messages: 50
Registered: December 2006
Location: bangalore
Member
Hi mahesh,

thanks for your reply. i tried it with constraints=n but it is not working. one more doubt is in my schema i have 50 tables then how to disable constraints to all tables at single statement.

thanks and regard,
Re: problem with import [message #217164 is a reply to message #217158] Wed, 31 January 2007 22:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Not possible in a single statement.
Write a stored procedure that will loop through the constraints and make use of dynamic sql (execute immediate).
Re: problem with import [message #217170 is a reply to message #217158] Wed, 31 January 2007 23:03 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
try below scripts
SQL> set heading off
SQL> set echo off
SQL> set feedback off
SQL> spool d:\disable.sql
...
...
SQL>spool off
SQL>@d:\disable.sql

SQL> select 'alter table '||table_name||' disable constraints '|| constraint_nam
e ||';'
  2  from user_constraints;

SQL>set heading on
SQL>set echo on
SQL>set feedback on

SQL>set heading off
SQL>set feedback off
SQL>set echo off
SQL> spool d:\enable.sql
SQL> select 'alter table '||table_name||' enable constraints '|| constraint_name
 ||';'
  2  from user_constraints
  3  order by constraint_type;
SQL>spool off
SQL>@enable.sql
SQL>set heading on
SQL>set feedback on
SQL>set echo on


regards
Taj

[Updated on: Wed, 31 January 2007 23:05]

Report message to a moderator

Re: problem with import [message #217266 is a reply to message #217101] Thu, 01 February 2007 06:26 Go to previous messageGo to next message
needathaj
Messages: 1
Registered: February 2007
Location: India
Junior Member
I am also facing the same import problem.

But one thing i noticed in this.

I am truncating table with one user, but importing data using some other user. This could be reason for getting the error below like this:

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (GNV_BATCH.ITEMISATIONOVERRIDE_PK) violated
Column 1 1
Column 2 5
Column 3 0
Column 4 01-JAN-2004:00:00:00
Column 5
Column 6 3
Column 7 N
Column 8
Column 9
Column 10
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (GNV_BATCH.ITEMISATIONOVERRIDE_PK) violated
Column 1 3
Column 2 5
Column 3 0
Column 4 01-JAN-2004:00:00:00
Column 5
Column 6 3
Column 7 N
Column 8
Column 9
Column 10 0 rows imported

finally i logined with that same import user, just truncated table again, then came out from that sqlplus session. Then tried to import data it is successfully imported.

Other option, you can just execute "imp" then for each step if you answer properly , you can import without any problem.


Re: problem with import [message #217313 is a reply to message #217266] Thu, 01 February 2007 11:38 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
I am truncating table with one user, but importing data using some other user. This could be reason for getting the error below like this:

Has nothing to do with the said error. May be you are importing into wrong schema.
Quote:
Other option, you can just execute "imp" then for each step if you answer properly , you can import without any problem.

Still, you need to understand what you are doing and behaviour is just the same as in non-interative mode.
Previous Topic: How to know the tablespaces from EXP dump
Next Topic: DBV-00100 using dbverf80
Goto Forum:
  


Current Time: Sat Dec 10 12:40:10 CST 2016

Total time taken to generate the page: 0.07847 seconds