The attributes of Oracle batch modification sequence (such as cache)_size, increment_(by et al.)

Oracle self-growing columns can be set as sequences, such as self-growing IDs.

Create a sequence

--Create Sequence dept_deptid_seq
create sequence dept_deptid_seq
    increment by 10
    start with 1
    maxvalue 9990
    nocycle
    nocache

--Create default sequence no_no_seq
create sequence no_no_seq

Sequence (start with n) as the value of the first sequence, the sequence value (increment by n) for each of the increase, the maximum sequence as the maxvalue (n), the sequence of the minimum value for (minvalue n)
cycle said if the sequence of maximum value is greater than the start from the minimum cycle (the default is not cycle), database cache n said pre-allocated n values stored in memory (the default) 20
The query sequence
Note: After a sequence has been created, the value of the current sequence cannot be directly queryed.nextVal must be used to query the value of the next sequence before using currVal to query the value of the current sequence.

select sequence_name ,last_number, min_value,max_value,increment_by,cache_size from user_sequences

Modify the sequence

alter sequence sequence_name increment by 1 cache 3000

Batch modification sequence

--bulk change sequence
declare
  v_sql varchar2(2000);
  CURSOR seqs IS select sequence_name from user_sequences where user_sequences.CACHE_SIZE<=20  and user_sequences.LAST_NUMBER>10000;
begin
  FOR seq IN seqs LOOP
    v_sql:='alter sequence '||seq.sequence_name
           ||' increment by 1 cache 3000';
    dbms_output.put_line(v_sql);
    execute immediate v_sql;
  end loop;
end;

The cursor places the sequence to be modified. Loop out the sequence name and modify it.


Read More: