Managing Sequences in Oracle

Sequence is a database object from which multiple users may generate unique integers.

The topic learns how to:

create, edit, and delete sequences using dbForge Studio for Oracle
create, edit, and delete sequences using the CREATE SEQUENCE, ALTER SEQUENCE, and DROP SEQUENCE statements respectively

Creating a Sequence

In Database Explorer, right-click the Sequences folder, and then click New Sequence on the shortcut menu.
In the template that appears, specify sequence parameters:
- Sequence name
- Name of the schema owner
- Initial value that indicates the first number in the sequence
- Increment that is the interval between sequence numbers. By default, an increment value is set to 1.
- Minimum value of the sequence that must be less than or equal to the initial value and less than the maximum value.
- Maximum value of the sequence that must be equal to or greater than the initial value.
- Cache that stores the specified number of sequence values in cache for performance.
- Cycle values, if specified, it allows generating values after reaching the maximum and minimum value
- Order values, if specified, it allows generating sequence numbers in order of request
By default, dbForge Studio creates an ascending sequence that starts with 1, increments by 1, has 20 values cached (Oracle default), and is not limited.
To save the changes, click Apply Changes. The sequence will be added to the list of sequences in Database Explorer.

To inspect the generated SQL query, click Script Changes. If you want to copy this scrpit to a clipboard, click Script Changes > To Clipboard.

-or-

On the Start Page, switch to the Database Design tab and click New Database Object.
In the New Object dialog box, select the Sequence type and then follow the steps from the procedure listed above.

-or-

On the Standard toolbar, click New Database Object and select the Sequence object type from the drop-down list and then follow the steps from the procedure listed above.

Selecting a sequence database object in Oracle

Creating a Sequence Using the CREATE SEQUENCE Statement

To create an Oracle sequence using the CREATE SEQUENCE statement, do the following:

On the Start Page > SQL Development tab, click SQL Editor.

-or-

On the Standard toolbar, click New SQL.

In the SQL editor, type the query. The syntax is as follows:

 CREATE SEQUENCE schema_name.sequence_name
 [INCREMENT BY interval]
 [START WITH first_number]
 [MAXVALUE max_value | NOMAXVALUE]
 [MINVALUE min_value | NOMINVALUE]
 [CYCLE | NOCYCLE]
 [CACHE cache_size | NOCACHE]
 [ORDER | NOORDER];

Add a sequence name you want to create and a schema name to which the sequence will belong.
Specify the parameters in the statement. All the parameters have the same meaning as described in Creating a Sequence.
To run the query, click .

Editing a Sequence

In Database Explorer, expand the Sequences folder.
Right-click a required sequence and then click Edit Sequence on the shortcut menu.
To update the sequence with the changes, click Apply Changes.

By default, dbForge Studio creates an ascending sequence that starts with 1, increments by 1, has 20 values cached (Oracle default), and is not limited.

Editing a Sequence Using the ALTER SEQUENCE Statement

Using the ALTER SEQUENCE statement, you can change the increment, minimum and maximum values, cache values, and behavior of a sequence object.

To change the attributes of the Oracle sequence using the ALTER SEQUENCE statement, do the following:

On the Start Page > SQL Development tab, click SQL Editor.

-or-

On the Standard toolbar, click New SQL.

In the SQL editor, type the query. The syntax is as follows:

 ALTER SEQUENCE schema_name.sequence_name
 [INCREMENT BY interval]
 [MAXVALUE max_value | NOMAXVALUE]
 [MINVALUE min_value | NOMINVALUE]
 [CYCLE | NOCYCLE]
 [CACHE cache_size | NOCACHE]
 [ORDER | NOORDER];

Update the sequence attributes. All the parameters have the same meaning as described in Creating a Sequence.
To run the query, click .

If you want to start the sequence with a different number, you need to drop the sequence and then to re-create it by running the following query:

DROP SEQUENCE schema_name.sequence_name;

CREATE SEQUENCE schema_name.sequence_name
    START WITH new_value;

Deleting a Sequence

In Database Explorer, expand the Sequences folder.
Right-click a required sequence and then click Delete on the shortcut menu.
In the pop-up window, do the following:
- click Yes to confirm the deletion of the sequence.
- click No to cancel the deletion of the sequence.

Dropping a Sequence Using the DROP SEQUENCE Statement

To remove a sequence from the database, do the following:

On the Start Page > SQL Development tab, click SQL Editor.

-or-

On the Standard toolbar, click New SQL.
In the SQL editor, type the query. The syntax is as follows:
```
     DROP SEQUENCE schema_name.sequence_name;
```
Specify the sequence name and the schema name containing the sequence to be removed.
To run the query, click .

To remove a sequence in another schema, you should have the DROP ANY SEQUENCE privilege.

Want to Find out More?

Download a free 30-day trial