how to import data into existing oracle table

In Nov 4, I exported a full database from an old database (oracle 10gr2), and import the data file into a new database (oracle 12c). After a week, we found that some application still insert the data into the old database. So I give myself a task to find the missing data (about 159 records in a specific table )from the old database and put it into the new database.

First, I tried the COPY command:

COPY FROM someuser/oldpaswd@//192.168.1.46:1521/OLDORCL to someuser/newpaswd@//192.168.1.26:1521/NEWORCL
 CREATE plus_circular_profile_new USING (select * from plus_circular_profile where sys_date >= to_date('05-NOV-17','DD-MON-YY' ));

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY-0012: Datatype cannot be copied

SQL> desc plus_circular_profile;
 
 ID_OF_ROW              NOT NULL RAW(16)         <------ this is the culpit
 ID_OF_CIRCULAR    NOT NULL NUMBER(18)
 ID_OF_CONSUMER NOT NULL NUMBER(18)
 FIRST_NAME            VARCHAR2(50)
 .........

Open in new window


Due to I have the raw type in the table, this is certainly not possible to do.

Then I turn around to use imp/exp utilites.
exp tables=plus_circular_profile query="""where sys_date > to_date('04-NOV-17','DD-MON-YY' )"""
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
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          PLUS_CIRCULAR_PROFILE        159 rows exported

Open in new window


But I have trouble to import the data into the new table:

imp someuser/newpaswd fromuser=someuser touser=someuser file=EXPDAT.DMP rows=Y ignore=Y log=import111617.log

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (ARTHUR.PLUS_CIRCULAR_PROFILE_PK) violated
Column 1 EA6AEC75335D4B1F9DCECF852D2165ED
...........

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (ARTHUR.PLUS_CIRCULAR_PROFILE_UK) violated
Column 1 DDA0613DE4354DC5B03C4FCC47FF3726

Open in new window


For the primary key violation:
ID_OF_ROW                       NOT NULL RAW(16)         <------ this is the primary key

For the Unique Key violation:
ID_OF_CIRCULAR              NOT NULL NUMBER(18)  <------- this is the unique key

I know the RAW type record could be possibly duplicated from one physical server to another after the database migration.
For the duplicated ID_OF_CIRCULAR it is because the production of the past week had generated the same id number because it always find the currently biggest number in the table and increment by 1 when needed. so there must have some record in the new database have exactly same ID_OF_CIRCULAR.

My question is: How can I get these 159 records into the new database?

Thank you for any help.
Arthur WangAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
if you have toad ...
open the table in the schema browser, data tab
apply the filter to limit to the records you need
right click, export data as insert statements
> then run these insert statements on your dest

or
use expdp on the source db host with the query option
and then use impdp full on the dest

expdp :
https://docs.oracle.com/database/122/SUTIL/oracle-data-pump-export-utility.htm#SUTIL200

credir.sql
define dirname=&1
define dirdir=&2
create or replace directory &dirname. as '&dirdir.';
exit

Open in new window


sample export on source host
set oracle_sid=SOURCESID
sqlplus user/pass @credir.sql exp c:\temp
expdp user/pass directory=exp dumpfile=source.dmp logfile=expsource.log content=data_only table=yourschema.yourtable query="WHERE ID>205000"

Open in new window


the dumpfile will be in c:\temp
copy it to the same location on the dest host

import:
https://docs.oracle.com/database/122/SUTIL/datapump-import-utility.htm#SUTIL300

sample import on dest host:
set oracle_sid=DESTSID
sqlplus user/pass @credir exp c:\temp
impdp user/pass directory=exp dumpfile=source.dmp logfile=impsource.log content=data_only table=yourschema.yourtable table_exists_action=append

Open in new window


hope that helps
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John TsioumprisSoftware & Systems EngineerCommented:
Well i am an Access guy so i would create a link to the Old Database and a link to the new Database...perform a query with a left join from the old table to the new table....the null fields should be the missing ones...
0
Arthur WangAuthor Commented:
Geert, thanks for the solution, however, i tried your code and got the following error:

expdp someone/somepaswd directory=exp dumpfile=source.dmp logfile=expsource.log content=data_only
tables=ARTHUR.plus_circular_profile query=""" WHERE sys_date > to_date('05-NOV-17','DD-MON-YY')"""

ORA-39006: internal error
ORA-39213: Metadata processing is not available

By the way, I did create the directory by using the file credir.sql before running the expdp command

Update:  I tried on oracle 10gr2, it was successful, and the above error showing on my testing server which is oracle 12c
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Arthur WangAuthor Commented:
After tried to export successfully on oracle 10gr2, I conduct the import on the new server which is oracle 12c:

impdp someuser/******  directory=imp dumpfile=source.dmp logfile=expsource.log content=data_only 
tables=ARTHUR.plus_circular_profile query=""" WHERE sys_date > to_date('04-NOV-17','DD-MON-YY')"""

Import: Release 12.1.0.2.0 - Production on Fri Nov 17 03:50:11 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39006: internal error
ORA-39213: Metadata processing is not available

Open in new window

0
Geert GOracle dbaCommented:
login as sys and load the stylesheets:
execute sys.dbms_metadata_util.load_stylesheets;

make sure you don't have any invalid objects in your source db
on the db host:
cd /d %oracle_home%
bin\sqlplus "/ as sysbda" @rdbms\admin\utlrp

Open in new window

0
Geert GOracle dbaCommented:
you can import the full dump
it should only contain missing records...

the syntax for impdp doesn't have a query parameter

but now i see something.
your primary key, is max()+1 on the source.

best to create an insert trigger which overwrites the id column upon insert with a sequence
max()+1 is not such a good idea for big tables
0
Arthur WangAuthor Commented:
Update: realize I should import the full source file. After modified the impdp statment, got almost the same error message:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "ARTHUR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ARTHUR"."SYS_IMPORT_TABLE_01":  arthur/******** directory=imp dumpfile=source.dmp logfile=expsource.log content=data_only tables=ARTHUR.plus_circular_profile table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "ARTHUR"."PLUS_CIRCULAR_PROFILE" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-00001: unique constraint (ARTHUR.PLUS_CIRCULAR_PROFILE_PK) violated
Job "ARTHUR"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Fri Nov 17 04:01:22 2017 elapsed 0 00:00:09

Open in new window

0
Geert GOracle dbaCommented:
you need an insert trigger which generates the id_of_row in the dest table

i would rename the table in the source to prevent any more inserts

alter table ARTHUR.plus_circular_profile rename to ARTHUR.xplus_circular_profile;

Open in new window

0
Arthur WangAuthor Commented:
thanks, seems like that's the only solution at this moment. I checked the database, actually it did imported 63 records  which are non-violated records. Now I have to figure out how to deal with this 159 -63 records, these are either Primary Key violated or Unique key violated. All of them show in the log file. The worst situation for me is to do manual insert one by one.


Column 27 0
Column 28 07-NOV-17 11.48.39.406000000 AM
Column 29 
Column 30 
Column 31 
Column 32 
Column 33 
Column 34 
Column 35 
Column 36 
Column 37 
Column 38 
Column 39 
Column 40 
Column 41 
Column 42 
Column 43          63 rows imported
About to enable constraints...
Import terminated successfully with warnings.

Open in new window


Question: Is there any way to reverse the last successful import?
0
Mark GeerlingsDatabase AdministratorCommented:
"Is there any way to reverse the last successful import?"
Not directly, unless there has been very little database activity since then,  If that is true, you could use a "flashback query" something like this:
select * from [your_table] as of timestamp sysdate - [the number of hours or days since the import];

But, you should be able to Identify the 63 rows that were imported, then delete them.
0
Arthur WangAuthor Commented:
Thanks for all the help,  your guys did help me solve the problem. Since the number of misplaced records is only about 159, I get help from some of our employee in the office to manually re-enter the data from the web page(this is a web application).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.