Re: Refreshing Snapshots with Foregn Key defined on them

A copy of this was sent to andreyNSPAM_at_bookexchange.net (NetComrade) (if that email address didn't require changing) On Mon, 29 Nov 1999 18:38:59 GMT, you wrote:

>I have snapshots with F/Kes defined on them.
>so when they 'try' to refresh, they produce the following:
>
>*** SESSION ID:(5.299) 1999.11.29.12.28.22.000
>*** 1999.11.29.12.28.22.000
>ORA-12012: error on auto execute of job 25
>ORA-12008: error in snapshot refresh path
>ORA-02266: unique/primary keys in table referenced by enabled foreign
>keys
>ORA-06512: at "SYS.DBMS_SNAPSHOT", line 271
>ORA-06512: at "SYS.DBMS_IREFRESH", line 416
>ORA-06512: at "SYS.DBMS_REFRESH", line 171
>ORA-06512: at line 1
>
>So I fugre the only way to do this 'automatically' is to write a
>PL/SQL procedure which would drop constraints, and then do
>DBMS_SNAPSHOT.REFRESH and then recreate constraints? And then put this
>into a DBMS_JOB?
>

what version of Oracle?

In v7.x -- putting foreign keys on snapshots is not allowed (for the above reason).

In 8.x and up, you would create the foreign key constraint to be 'deferrable'. the replication process will make it so that all deferrable consraints operate deferred (checked at commit, not at statement execution time). this allows the fkeys to go out of sync during the refresh (as they will) and have the constraint valdated at commit (when they will be OK again).

>Any other suggestions?
>
>Also, will the data be anavailable while the snapshot is refreshing?
>

for read -- no.

for write (if using updatable snapshots), the snapshot table is locked from updates during the refresh.

>Thanx
>
>---------------
>Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 373-5417
>AOL: NetComrade ICQ: 11340726 remove NSPAM to email

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Nov 29 1999 - 13:09:35 CST