Re: Replicating Sequences

N Prabhakar <prabhs_at_po.pacific.net.sg> wrote:

>"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.

The accuracy of this will depend on the cache setting for a sequence. You'll burn a few sequences if your sequences were not created with the nocache option. By default the cache is 20.

If accuracy is important, the max(column) is the correct choice.

Russ Received on Sun Nov 10 1996 - 00:00:00 CET