importing data only in existing tables. [message #612601] |
Tue, 22 April 2014 10:57 |
jxh461
Messages: 185 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 #612628 is a reply to message #612603] |
Tue, 22 April 2014 16:05 |
jxh461
Messages: 185 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 #612709 is a reply to message #612637] |
Wed, 23 April 2014 11:17 |
|
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 |
|
Michel Cadot
Messages: 68728 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
|
|