Home » RDBMS Server » Server Utilities » importing data only in existing tables. (Oracle 11g, Window 2008)
icon5.gif  importing data only in existing tables. [message #612601] Tue, 22 April 2014 10:57 Go to next message
jxh461
Messages: 182
Registered: March 2005
Senior Member
I want to do an import of data only using datapump.

Some of the tables may already have data in them, and I would like them truncated before they are loaded.
Is there an option in datapump to truncate the tables before loading ?

 impdb sys@orcl directory=dumpdir schemas=ov1 content=data_only dumpfile=ov1.dmp logfile=ov1.log 


Will this do the trick ?

Please advise
Re: importing data only in existing tables. [message #612603 is a reply to message #612601] Tue, 22 April 2014 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

E:\>impdp help=y

Import: Release 11.2.0.1.0 - Production on Mar. Avr. 22 18:00:23 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
...
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
...

Re: importing data only in existing tables. [message #612628 is a reply to message #612603] Tue, 22 April 2014 16:05 Go to previous messageGo to next message
jxh461
Messages: 182
Registered: March 2005
Senior Member
Can someone advise me how to resolve this error. I am getting it on multiple tables.

ORA-39120: Table "OV1"."EVENT" can't be truncated, data will be skipped. Failing error is:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [59]
TABLE_DATA:"OV1"."EVENT"
ORA-39120: Table  can't be truncated, data will be skipped. Failing error is:

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8171

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
AD4D25DC     18990  package body SYS.KUPW$WORKER
AD4D25DC      8192  package body SYS.KUPW$WORKER
AD4D25DC     18552  package body SYS.KUPW$WORKER
AD4D25DC      4105  package body SYS.KUPW$WORKER
AD4D25DC      8875  package body SYS.KUPW$WORKER
AD4D25DC      1649  package body SYS.KUPW$WORKER
A6098C30         2  anonymous block

Job "SYSTEM"."SYS_IMPORT_SCHEMA_45" stopped due to fatal error at 16:55:30
Re: importing data only in existing tables. [message #612636 is a reply to message #612628] Wed, 23 April 2014 00:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Disable all foreign keys before import and reenable them after.

Re: importing data only in existing tables. [message #612637 is a reply to message #612628] Wed, 23 April 2014 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How does your IMPDP command line look now?

The error message says the following:
Oracle

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Cause: An attempt was made to truncate a table with unique or primary keys referenced by foreign keys enabled in another table. Other operations not allowed are dropping/truncating a partition of a partitioned table or an ALTER TABLE EXCHANGE PARTITION.

Action: Before performing the above operations the table, disable the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
Re: importing data only in existing tables. [message #612709 is a reply to message #612637] Wed, 23 April 2014 11:17 Go to previous messageGo to next message
youngryand
Messages: 10
Registered: March 2014
Location: Madison, WI
Junior Member
Per Michel's suggestion, below is code I wrote and use to drop and re-create foreign keys referencing a given table. I realize the code might have just a bit more SQL than is necessary; this is because it was created by modifying other SQL I use for different FK purposes. However, I believe it works perfectly.

Make sure you save the recreate results before you perform the drops.

The code (predicates) can be modified to do all tables in a given schema, or all tables that match an expression, etc...

SELECT sql
  FROM
(
  SELECT 1 rnk, TRIM('ALTER TABLE ' || c_src.owner || '.' || c_src.table_name || ' DROP CONSTRAINT ' || c_list.constraint_name || ';') sql
    FROM dba_constraints  c_list,
         dba_cons_columns c_src,
         dba_cons_columns c_dest
   WHERE c_list.owner             = c_src.owner
     AND c_list.r_owner           = c_dest.owner
     AND c_list.constraint_name   = c_src.constraint_name
     AND c_list.r_constraint_name = c_dest.constraint_name
     AND c_list.constraint_type   = 'R'
     AND c_dest.owner             = UPPER('&&owner')
     AND c_dest.table_name        = UPPER('&&table')
     AND NVL(c_src.position, 1)   = NVL(c_dest.position, 1)
     AND NVL(c_src.position, 1)   = 1
UNION ALL
  SELECT 2 rnk, TRIM(dbms_lob.substr(dbms_metadata.get_ddl('REF_CONSTRAINT', c_list.constraint_name, c_src.owner), 32000, 1) || ';') sql
    FROM dba_constraints  c_list,
         dba_cons_columns c_src,
         dba_cons_columns c_dest
   WHERE c_list.owner             = c_src.owner
     AND c_list.r_owner           = c_dest.owner
     AND c_list.constraint_name   = c_src.constraint_name
     AND c_list.r_constraint_name = c_dest.constraint_name
     AND c_list.constraint_type   = 'R'
     AND c_dest.owner             = UPPER('&&owner')
     AND c_dest.table_name        = UPPER('&&table')
     AND NVL(c_src.position, 1)   = NVL(c_dest.position, 1)
     AND NVL(c_src.position, 1)   = 1
) x
ORDER BY rnk, sql;
Re: importing data only in existing tables. [message #612711 is a reply to message #612709] Wed, 23 April 2014 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59425
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't need to drop the constraints, just disable it then it is easy to re-enable all disabled constraints.
If you drop the constraints, you have to memorize somewhere which constraints were deleted (hoping this somewhere will not disappear during your work).

[Edit: missing word]

[Updated on: Tue, 29 April 2014 11:07]

Report message to a moderator

Re: importing data only in existing tables. [message #613088 is a reply to message #612711] Tue, 29 April 2014 11:04 Go to previous messageGo to next message
youngryand
Messages: 10
Registered: March 2014
Location: Madison, WI
Junior Member
Michel Cadot wrote on Wed, 23 April 2014 11:51

You don't need to drop the constraints, just disable it then it is to re-enable all disabled constraints.
If you drop the constraints, you have to memorize somewhere which constraints were deleted (hoping this somewhere will not disappear during your work).



Yes, very true. Michel is right that FKs do not necessarily need to be dropped, UNLESS the parent table has to be dropped as part of the truncate and reload process. Often that is the case for me. It depends on your process -- what you are doing, and why you are doing it. (An FK (enabled or disabled), cannot reference a non-existent table).

The script I attached will create the DDL to recreate the FKs for you if you in fact do need to drop the parent table and FKs.

Ryan
Re: importing data only in existing tables. [message #613113 is a reply to message #613088] Wed, 30 April 2014 02:53 Go to previous message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're dropping tables then you're not doing a truncate and reload process. And I can't think of any reason why you would need to drop tables in those circumstances
Previous Topic: IMP Datapump error
Next Topic: how to handle nulls for date field in control file
Goto Forum:
  


Current Time: Fri Oct 24 06:42:48 CDT 2014

Total time taken to generate the page: 0.14854 seconds