Home » RDBMS Server » Server Utilities » insert crores of records (oracle 10g, hp-ux)
insert crores of records [message #342331] Fri, 22 August 2008 01:18 Go to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Hi,

Actually I want to insert 1 crore records (may contain duplicate data)in table of approx. 100 crore records. I don't want to disable constraint & then then import duplicate rows & then delete duplicate records from entire table. So which is the best method to do this?
Re: insert crores of records [message #342335 is a reply to message #342331] Fri, 22 August 2008 01:22 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Read this
Re: insert crores of records [message #342336 is a reply to message #342331] Fri, 22 August 2008 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't want to disable the constraint then you can't insert duplicates.
What do you want to do with these duplicates? Ignore? Identify? If there are duplicates which one do you want to load?...

Regards
Michel
Re: insert crores of records [message #342354 is a reply to message #342336] Fri, 22 August 2008 02:01 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Hi,
I want to ignore those duplicate records & enter remaining records in file. I can do this using import ignore=y in oracle 10g .But this is not possible with impdp command. Since there is no ignore=y.It will give error for duplicate rows ,but insert remaining rows. Function of ignore=y & table_exist_action is diffrent. I have tested this. Dump is taken using expdp command.
If I use export command it takes lot of time, so I used expdp command.
Re: insert crores of records [message #342370 is a reply to message #342354] Fri, 22 August 2008 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can do this using import ignore=y in oracle 10g

This will hide only DDL errors not duplicate ones.

Quote:
It will give error for duplicate rows ,but insert remaining rows

Same behaviour in both case.

Regards
Michel
Re: insert crores of records [message #342379 is a reply to message #342370] Fri, 22 August 2008 03:35 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Thanks.

No, it is not same. I have tested this. It is not inserting remaining records (impdb). It is coming out after giving errors(constraint violated)

[Updated on: Fri, 22 August 2008 03:38]

Report message to a moderator

Re: insert crores of records [message #342386 is a reply to message #342379] Fri, 22 August 2008 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you have your solution, use exp/imp.

Regards
Michel
Re: insert crores of records [message #342391 is a reply to message #342386] Fri, 22 August 2008 04:10 Go to previous messageGo to next message
seema.taunk
Messages: 96
Registered: October 2006
Member
Then it must be bug in 10.2.0.3.
I can do it with export ,but it is taking lot of time.

Thanks
Re: insert crores of records [message #342608 is a reply to message #342391] Sat, 23 August 2008 02:04 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Function of ignore=y & table_exist_action is diffrent.
There is no difference but TABLE_EXIST_ACTION provide more option.

I can do this using import ignore=y in oracle 10g .But this is not possible with impdp command.

Yes right, when i tested i found same.
consider:

SQL> select * from test;

        NO
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
         1

        NO
----------
         2
         3
         4
         5
         6
         7
         8
         9
        10

20 rows selected.

SQL> host exp scott/tiger file=f:\test1.dmp tables=TEST

Export: Release 10.1.0.5.0 - Production on Sat Aug 23 11:15:38 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           TEST         20 rows exported
Export terminated successfully without warnings.

----------xxxxxxxxx-------------
SQL> host expdp scott/tiger directory=data1 dumpfile=test.dmp tables=TEST

Export: Release 10.1.0.5.0 - Production on Saturday, 23 August, 2008 11:16

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=data1 dumpfile
=test.dmp tables=TEST
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TEST"                              5.046 KB      20 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  F:\TEST.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 11:16

----------xxxxxxxxx-------------
SQL> truncate table TEST;

Table truncated.
----------xxxxxxxxx-------------
SQL> alter table TEST add ( constraint uk unique(NO));

Table altered.
----------xxxxxxxxx-------------
SQL> host imp scott/tiger file=f:\test1.dmp full=y IGNORE=Y

Import: Release 10.1.0.5.0 - Production on Sat Aug 23 11:18:21 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table                         "TEST"
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UK) violated
Column 1 1
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UK) violated
Column 1 2
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UK) violated
Column 1 3
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UK) violated
Column 1 4
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UK) violated
Column 1 5
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UK) violated
Column 1 6
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UK) violated
Column 1 7
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UK) violated
Column 1 8
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UK) violated
Column 1 9
IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SCOTT.UK) violated
Column 1 10         10 rows imported
Import terminated successfully with warnings.
----------xxxxxxxxx-------------
SQL> select count(*) from test;

  COUNT(*)
----------
        10
----------xxxxxxxxx-------------
SQL> truncate table test;

Table truncated.
----------xxxxxxxxx-------------
SQL> host impdp scott/tiger directory=data1 dumpfile=test.dmp full=y -
> table_exists_action=[B]APPEND[/B]

Import: Release 10.1.0.5.0 - Production on Saturday, 23 August, 2008 11:21

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=data1 dumpfile=
test.dmp full=y table_exists_action=APPEND
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
ORA-31693: Table data object "SCOTT"."TEST" failed to load/unload and is being s
kipped due to error:
ORA-00001: unique constraint (SCOTT.UK) violated
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 11:21
----------xxxxxxxxx-------------

SQL> select count(*) from test;

 [B] COUNT(*)
----------
         0[/B]
----------xxxxxxxxx-------------
SQL> host impdp scott/tiger directory=data1 dumpfile=test.dmp full=y -
> table_exists_action=[B]REPLACE[/B]

Import: Release 10.1.0.5.0 - Production on Saturday, 23 August, 2008 11:22

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01":  scott/******** directory=data1 dumpfile=
test.dmp full=y table_exists_action=REPLACE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
. . imported "SCOTT"."TEST"                              5.046 KB      20 rows
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 11:22
----------xxxxxxxxx-------------

For fast response you can use REPLACE option and load all rows in a table after that custom SQL statement REMOVE duplicate rows. (it is fast compare then Original EXP/IMP).

NOTE: Just test.

[Updated on: Sat, 23 August 2008 02:33]

Report message to a moderator

Re: insert crores of records [message #342782 is a reply to message #342331] Mon, 25 August 2008 01:29 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

REPLACE option drop the existing table from the database and create a new one as it is in the dump file and then load data from the source file.

Re: insert crores of records [message #342785 is a reply to message #342608] Mon, 25 August 2008 01:33 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for posting the test.

Regards
Michel
Previous Topic: Database Import
Next Topic: SQL Loader
Goto Forum:
  


Current Time: Fri Dec 09 11:47:41 CST 2016

Total time taken to generate the page: 0.12706 seconds