Creating Sequences in Oracle Enterprise Manager Console

Sequences can be created using SQL *Plus or using the Enterprise Manager Console. To create a sequence in the Console, open the database tree and select the Schema you want to add the sequence to, right click on Sequences and click Create.

The Create Sequence window opens. Type a Name for the sequence in the Name TextBox. A Minimum and Maximum value may be entered. The Interval value is the difference between the values produced by the Sequence. An interval of 1 will produce: 1, 2, 3, etc. An interval of 5 will produce: 5, 10, 15, etc. The Initial value is also entered. It defaults to 1, but any value may be entered. Options to Cycle the value allows the values to start over again once the maximum value has been reached. Order values ensures the database provides the values in numeric order. If unchecked, the database does not necessarily provide the values in order. Cache allows the database to store the sequence values and improves performance.

After clicking Create, the database will create the sequence in the Schema.

Creating SEQUences in Oracle SQL *Plus PL/SQL

To create the sequence in SQL *Plus, login and type the syntax shown. This code creates a sequence named MySeq that starts with the number 2 and increments by 1 and has no maximum value.

By using the Dual table, a select statement can be written to extract the Next value from the sequence.

To use the sequence, I have created a table that uses an Integer value for the ID and varchar2 for an additional string field.

To insert data and use the sequence, the syntax in the 2nd part of the INSERT statement uses the <sequence name>.NextVal to get the sequence value. I have included a SELECT query on DUAL to get the CurrVal and NextVal of the Sequence after the insert.

To delete the sequence, a DDL Drop statement will remove it.

This example uses a Sequence with a minimum value and a maximum value with cycle to show how Oracle reuses the numbers of a sequence once they have all been utilized.

To populate the table with enough data to run through the sequence values at least 1 time, some anonymous code is used to construct a WHILE loop that will insert the NextVal of the sequence 9 times producing 9 records in the table. A SELECT statement from the table confirms that the data was inserted and that the sequence begins to reuse numbers as of the last record inserted (ID = 1 as the last record).

Problem - Oracle Is No Longer Able to Create PL/SQL Sequences

Notes: I noticed that the database was unable to create sequences and just seemed to "hang" on the CREATE SEQUENCE statement until I forced a close out of the Console or SQL *Plus. It was not until I turned off the ArchiveLog that the create statements were functional.

To determine if the database is in ArchiveLog mode or NoArchiveLog mode, check the Enterprise Manager Console or in SQL *Plus, check the v$database with a select statement.

If the database is in ArchiveLog mode, issue a Shutdown Immediate command and start the database in Mount mode and then use an alter database command to put it in noarchivelog mode and another alter database command to start the database. Alternately, unchecking the ArchiveLog box in the Console will cause the database to shutdown and restart.