Re: Replicating Sequences

From: N Prabhakar <prabhs_at_po.pacific.net.sg>
Date: 1996/11/10
Message-ID: <564k32$pgb_at_newton.pacific.net.sg>#1/1


"Bruce W. Robinson" <BRUCE_W_ROBINSON_at_CCM.JF.INTEL.COM> wrote:
>I am replicating 300 tables from an instance on one machine to an
>instance on a replicate (hot-spare) machine. One or two columns
>in each of the 300 tables are populated originally from nextval of
>a single sequence. If I have to fail-over to the replicate
>machine, the currval of that sequence will probably be unknown.
>
>I know I can select max(columnname) from each of the replicated
>tables and use the largest result to initialize the sequence on
>the replicate machine, but that would require a full table scan on
>all 300 tables, and many of them are large; I can't afford the
>wait.
>
>It has also ocurred to me to put a replication trigger on the base
>table (sys.seq$) underlying the user_sequences view, but Oracle
>documentation says, in boldface type, "don't mess with the
>dictionary tables".
>
>Any ideas?
>
>66
> >

> ^ ..................... brucer

One way to avoid full table scan to get the last sequence number is to perform the following statement.

SELECT LAST_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = <sequence name>

This will give you the latest value of the sequence. You don't need to scan the entire table and select the max value of your sequence column.

Hope the above info helps

Regards

N.Prabhakar
Socgen Crosby Securities
Singapore Received on Sun Nov 10 1996 - 00:00:00 CET

Original text of this message