How to reset the Sequence used by the NEXTVAL Function

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

I am interested in knowing the procedure to reset the sequence value that is called in the NEXTVAL function.

For example: @nextval(memberid,100000000)@01

Is there any way, to reset this “memberid” value, in same data set, after few data generation publish jobs in Datamaker/TDM portal?

Environment

TDM Portal
Test data Manager
Datamaker

Resolution

The NEXTVAL function finds the next value from the specified Oracle sequence.
A sequence is a schema object that can generate a unique sequential value.
If the sequence does not exist, the sequence will be created in the Oracle database.
The value is incremented in the Oracle database each time the sequence is used.

Resetting a specific sequence in Oracle is not all that straight forward, as it is in SQL Server.
For Oracle, you basically need to increment the sequence value by the negative current value.
The process is discussed by Tom Kyte in his Oracle blog (https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1119633817597)

Therefore, to have a sequence start over, or re-initialize a sequence, you will need to do this inside the database.
You may want to review the options with your DBA to see which options would work best with your database deployment.

Note: Before making changes to the database, it is always advised to make a backup of the database.

To reset a specific sequence in Oracle:

1. Get the next value for the sequence:

select [sequencename].nextval from GTREP.gtrep_system

This should return a numeric value, such as 160.

2. Alter the sequence by incrementing the value by the negative "current value":

alter sequence [sequencename] increment by –[value returned above] minvalue 0;

For example, if the current value returned was 160, then:

alter sequence [sequencename] increment by 160 minvalue 0 ;

3. Get the next value again, which should return the value of 0

select [sequencename].nextval from GTREP.gtrep_system

4. Set the sequence to increment by 1 again:

alter sequence [sequencename] increment by 1 minvalue 0 ;

5. Get the next value, should return 1;

select [sequencename].nextval from GTREP.gtrep_system

To reset a sequence in SQL Server, you have a couple of options.
If you need to reset the value on a regular basis, you can issue a reset by running an ALTER SEQUENCE statement.
If you are not comfortable with SQL statements, or only need to reset occasionally for project requirements, you can use the Microsoft SQL Server Management Studio GUI.

To reset a specific sequence using a SQL Statement:
1. Issue the following ALTER SEQUENCE statement:

ALTER SEQUENCE [schema].[sequencename] RESTART WITH [new value];

To reset a specific sequence through the SSMS GUI:

1. Launch SSMS GUI and connect to your database

2. In the SSMS GUI, navigate to Database Folder – [Database Name] – Programmability Folder – Sequences Folder.

3. Select the sequence of interest, and double click to open the Sequence Properties dialog window

4. Select the “Restart sequence” checkbox.

You can enter the value you want to reset the sequence to, or keep the default value.

5. Click OK to reset the sequence.

6. You will see the Current value field updated to the reset value.

Additional Information

For more information regarding Oracle sequences:
"Create Sequence" https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm#SQLRF01314
"How to reset sequences?" (Tom Kytes Blog Page)
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1119633817597

CA Test Data Manager Community Discussions:
How the sequence in nextval works?
How to reset a sequence