-- Sequence is used in Oracle databases to create unique index values. Using a trigger 
-- sequence can be indexed forward in data loading process or by just 
-- sequence_name.NEXTVAL the next value can be derived. After the sequence_name.NEXTVAL
-- call, the sequence_name.CURRVAL can also be accessed. Without calling 
-- sequence_name.NEXTVAL, the value of sequence_name.CURRVAL will be NULL.

-- Sequence can be created as shown:

CREATE SEQUENCE cms_id_seq  START WITH 1 INCREMENT BY 1 CACHE 20;

-- If the cache_size needs to be reset to 1 use:

ALTER SEQUENCE sequence_name NOCACHE;

SELECT  sequence_name, min_value, increment_by, cache_size, last_number 
FROM user_sequences;



-- To bump the sequences by a certain value (factor of 1000), following PL/SQL code can 
-- be used.  This code can be used to set a higher watermark on all user sequences.
-- This is useful when data gets reloaded between databases with trigger being 
-- disabled or data loaded by force by excluding sequenced insert.

DECLARE
  v_sql      VARCHAR2(200);
  v_factor   SIMPLE_INTEGER := 1000;
  v_idx      SIMPLE_INTEGER := 0;
BEGIN
 FOR seq_rec IN (SELECT sequence_name FROM user_sequences) LOOP
    v_sql := 'ALTER SEQUENCE '||seq_rec.sequence_name||' INCREMENT BY '|| 
             v_factor; 
    EXECUTE IMMEDIATE v_sql;
    v_sql := 'SELECT '||seq_rec.sequence_name||'.NEXTVAL FROM DUAL';
    EXECUTE IMMEDIATE v_sql  INTO  v_idx;
    v_sql := 'ALTER SEQUENCE '||seq_rec.sequence_name||' INCREMENT BY 1';
    EXECUTE IMMEDIATE v_sql;
 END LOOP;
END;
/




-- To bump by a computed factor based usage of a sequence

DECLARE
  v_sql      VARCHAR2(200);
  v_idx      SIMPLE_INTEGER := 0;
BEGIN
 FOR seq_rec IN (SELECT
                   sequence_name, 
                   DECODE(ROUND(last_number*0.005,0),
                     0,1, ROUND(last_number*0.005,0)
                    ) last_num
                 FROM user_sequences
               ) LOOP
    v_sql := 'ALTER SEQUENCE '||seq_rec.sequence_name||' INCREMENT BY '|| 
             last_num; 
    EXECUTE IMMEDIATE v_sql;
    v_sql := 'SELECT '||seq_rec.sequence_name||'.NEXTVAL FROM DUAL';
    EXECUTE IMMEDIATE v_sql  INTO  v_idx;
    v_sql := 'ALTER SEQUENCE '||seq_rec.sequence_name||' INCREMENT BY 1';
    EXECUTE IMMEDIATE v_sql;
 END LOOP;
END;
/