Solved

SQL*Loader and FK

Posted on 2004-04-29
6
3,835 Views
Last Modified: 2012-08-14
In our application, we need to do complete refreshes on a weekly basis of all tables. We are using SAS to create datasets that are identical to our Oracle tables.

Since our data volume is decent, we are trying to use SQL*Loader to load our tables.

SQL*Loader works great for tables that do not have any foreign keys, but for the couple of tables that do have them we are getting the following error:

"Referential Integrity Constraint/Trigger Information:
NULL, UNIQUE, and PRIMARY KEY constraints are unaffected.

SQL*Loader-965: Error -1 disabling constraint FK1_TBLDETAIL on table "ESECURITY"."TBLDETAIL" "

Since we are developers and not DBAs we don't really want to have to remove the FK and re add it at the end.

Are there any alternatives? Is there a SQL*Loader option to ignore FKs? Thoughts would be appreciated!!
0
Comment
Question by:jpmcclain
6 Comments
 
LVL 12

Accepted Solution

by:
geotiger earned 250 total points
ID: 10953252

I do not believe that sql*loader has such option. You have to do it in the database. You do not need to remove FKs. You can just disable them before you data load using alter table statement.

Here is the excerption from Oracle doc:

DISABLE disables the integrity constraint. If you do not specify this clause when creating a constraint,
Oracle automatically enables the constraint.
n DISABLE VALIDATE disables the constraint and drops the index on the constraint, but
keeps the constraint valid. This feature is most useful in data warehousing situations,
where the need arises to load into a range-partitioned table a quantity of data with a
distinct range of values in the unique key. In such situations, the disable validate state
enables you to save space by not having an index. You can then load data from a non-partitioned
table into a partitioned table using the exchange_partition_clause of the
ALTER TABLE statement or using SQL*Loader. All other modifications to the table
(inserts, updates, and deletes) by other SQL statements are disallowed.
If the unique key coincides with the partitioning key of the partitioned table, disabling
the constraint saves overhead and has no detrimental effects. If the unique key does not
coincide with the partitioning key, Oracle performs automatic table scans during the
exchange to validate the constraint, which might offset the benefit of loading without
an index.
n DISABLE NOVALIDATE signifies that Oracle makes no effort to maintain the constraint
(because it is disabled) and cannot guarantee that the constraint is true (because it is
not being validated). For information on when to use this setting, see Oracle8i Design-ing
and Tuning for Performance.
You cannot drop a table whose primary key is being referenced by a foreign key even if
the foreign key constraint is in DISABLE NOVALIDATE state. Further, the optimizer can
use constraints in DISABLE NOVALIDATE state.
n If you specify neither VALIDATE nor NOVALIDATE, the default is NOVALIDATE.
n If you disable a unique or primary key constraint that is using a unique index, Oracle
drops the unique index.
0
 
LVL 10

Assisted Solution

by:SDutta
SDutta earned 250 total points
ID: 10953364
This is a problem with foreign keys, every time you reload the master table, the foreign keys have to be dropped and recreated.

You could howeverimplement the constraints using triggers on the child tables where each insert/update/delete is validated against the master for RI. This is a more time-consuming way of implementing RI but avoids the problem of dropping and recreating the constraints. This issue also happens when you create dependencies on materialized views you cannot do a complete refresh without redoing the FK constraints.
0
 

Author Comment

by:jpmcclain
ID: 10953624
Thanks! Your suggestions helped us get it working!!!
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
ID: 10953643
@jpmcclain:

your comment--->Since we are developers and not DBAs we don't really want to have to remove the FK and re add it at the end. Are there any alternatives? Is there a SQL*Loader option to ignore FKs? Thoughts would be appreciated!!


You don't have to remove the FKs.  Read the message from SQL*LOADER,
you can disable them temporarily for the sqlldr process and reenable them after.

it's really easy to do this in an automatic fashion.
SAve the following script to run it under the schema owner (!!!)
before  you run sqlldr

-------------------------------------------------cut here -----------------------------------------------

set termout off
set feedback off
set trimspool on
set pagesize 0
set heading off

spool disablefks.sql

select 'alter table '|| table_name || ' disable constraint '|| constraint_name ||';' from user_constraints where constraint_type='R';
spool off
@disablefks.sql

---------------------------------------------------------------cut here-----------------------------------------------------------

save above between "cut here" to a file called "test.sql" file

then log in as the schema owner

SQL>@test.sql





After finishing the sqlloader process, run this script to enable all FKs constraints

-------------------------------------------------cut here -----------------------------------------------

set termout off
set feedback off
set trimspool on
set pagesize 0
set heading off

spool disablefks.sql

select 'alter table '|| table_name || ' enable constraint '|| constraint_name ||';' from user_constraints where constraint_type='R';
spool off
@disablefks.sql

---------------------------------------------------------------cut here-----------------------------------------------------------

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 10953649
uh-huh,

jpmcclain, aren't you fast !!!
0
 

Expert Comment

by:SizovLeonid
ID: 37724840
we have exactly the same problem.
but run sql loader using control file, which indicate optional for disabling / enabling FKs :
  REENABLE DISABLED_CONSTRAINTS

Import works unstable - then everything is fine, then fall with an error:
SQL * Loader-965: Error -1 disabling constraint.

Are there any solutions to solve the problem and save our options?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now