Overview

This document describes various methods to initialize a SEQUENCE, other than using DROP SEQUENCE / CREATE SEQUENCE, when you want to change the START WITH value during SEQUENCE usage.

Methods

Method 1: Initialization by Applying SEQUENCE CYCLE

This method resets the numbering to start from 1 again according to the CYCLE option when the SEQUENCE value reaches a certain number.

Caution

The NEXT_VAL will return to the initial value only after reaching MAXVALUE.

-- Use CYCLE option to restart numbering from 1 upon reaching MAXVALUE
CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 9999 CYCLE;

Method 2: Initialization by Deleting/Recreating SEQUENCE Using SYNONYM

This method initializes the SEQUENCE by recreating the SEQUENCE connected through a SYNONYM object.

Caution

Periodic scheduling of SEQUENCE creation/deletion is required.

-- Create SEQUENCE by date
CREATE SEQUENCE TEST_SEQ_20260629 INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 9999;

-- Replace SYNONYM with new SEQUENCE
CREATE OR REPLACE SYNONYM TEST_SEQ FOR TEST_SEQ_20260629;

-- Create new SEQUENCE and connect SYNONYM
CREATE OR REPLACE SYNONYM TEST_SEQ FOR TEST_SEQ_20260630;

-- Drop old SEQUENCE (scheduling recommended)
DROP SEQUENCE TEST_SEQ_20260629;
DROP SEQUENCE TEST_SEQ_20260630;

Method 3: Initialization by Adjusting SEQUENCE INCREMENT BY

This method initializes the SEQUENCE by checking the current numbering value and changing the INCREMENT BY accordingly.

Caution

The procedure for SEQUENCE initialization is complex and requires a procedure or scheduled task.

-- Create SEQUENCE
CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 9999;
SELECT TEST_SEQ.NEXTVAL FROM DUAL;
SELECT TEST_SEQ.NEXTVAL FROM DUAL;

-- Check current SEQUENCE value
SELECT NEXT_VAL FROM  SYS._DD_SEQ WHERE
OBJ_ID = (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='TEST_SEQ');

-- Adjust INCREMENT BY
ALTER SEQUENCE TEST_SEQ INCREMENT BY - (NEXT_VAL - 1);
ex) If NEXT_VAL=20, ALTER SEQUENCE TEST_SEQ INCREMENT BY -20;

-- Adjust NEXT_VAL using INCREMENT BY value
SELECT TEST_SEQ.NEXTVAL FROM DUAL;

-- Set SEQUENCE numbering to start again from 1
ALTER SEQUENCE TEST_SEQ INCREMENT BY 1;

Method 4: Initialization by Updating Internal _DD_SEQ via 293704 Patch

This method initializes the SEQUENCE numbering value by updating the values in the internal _DD_SEQ table.

Caution

This involves controlling the internal SEQUENCE table and using a system parameter. Numbering will start from the changed NEXT_VAL after applying the SEQUENCE CACHE size.

Note

Available by default in Tibero 6.7.1, 7.2.1 and later versions

-- Create SEQUENCE
CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 9999;

-- Enable parameter to apply _DD_SEQ UPDATE value
ALTER SYSTEM SET _SEQ_GET_NEXTVAL_FROM_DD_TABLE = 'Y';

-- Check current NEXT_VAL
SELECT NEXT_VAL FROM  SYS._DD_SEQ WHERE
OBJ_ID = (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='TEST_SEQ');

-- Set desired initial numbering value
UPDATE SYS._DD_SEQ SET NEXT_VAL=100 WHERE
OBJ_ID = (SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='TEST_SEQ');

-- Restore parameter
ALTER SYSTEM SET _SEQ_GET_NEXTVAL_FROM_DD_TABLE = 'N';

Method 5: Initialization Using RESTART START WITH %d via 316049 Patch

This method initializes the SEQUENCE by applying the official feature ALTER SEQUENCE %s RESTART START WITH %d;

Note

Available by default in Tibero 6.7.4, 7.2.4 and later versions

-- Create SEQUENCE
CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 999;

-- Perform SEQUENCE initialization DDL
ALTER SEQUENCE TEST_SEQ RESTART START WITH 1 ;