| insert crores of records [message #342331] |
Fri, 22 August 2008 01:18  |
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 #342354 is a reply to message #342336] |
Fri, 22 August 2008 02:01  |
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 #342608 is a reply to message #342391] |
Sat, 23 August 2008 02:04 |
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
