3.6 Managing Sequences

A sequence generates a serial list of unique numbers for numeric columns of a database table. Use Object Browser to create, view, or drop a sequence.

Database sequences are generally used to populate table primary keys.

• Creating a Sequence
  Create a sequence using Object Browser.
• Browsing a Sequence
  Select a sequence from the Object Tree and access tabs in the Object Detail View.
• Dropping a Sequence
  Select a sequence from the Object Tree pane and click Drop on the Object Details tab.

3.6.1 Creating a Sequence

Create a sequence using Object Browser.

To create a sequence:

1. In Object Browser, click the Create Database Objects menu and select Sequence.

   Tip:

   To create new objects from the Object Tree, right-click the object and select the Create option.

   The Create Sequence Wizard appears.

2. On Create Sequence:
   1. Sequence Name - Enter the name of the sequence. The name must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore.
   2. Start With - Specify the first sequence number to be generated. This value must be a valid integer.

      Use this field to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence.

   3. Increment By - Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0.

      The absolute of this value must be less than the difference of Maximum Value and Minimum Value. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends.

   4. Number to Cache - Enter a cache size. This value must be a valid positive integer greater than 1.

      The value is used to preallocate a set of sequence numbers and keep them in memory so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used, the database reads another set of numbers into the cache.

   5. Minimum Value - Specify the minimum value of the sequence. This value must be a valid integer and must be less than or equal to Start With and must be less than Maximum Value. If no minimum value is specified, Oracle uses minimum value of 1 for an ascending sequence or -(1027 -1) for a descending sequence.
   6. Maximum Value - Specify the maximum value the sequence can generate. This value must be a valid integer and equal to or greater than Start With and than Minimum Value. If no maximum value is specified, Oracle uses maximum value of 1027 -1 for an ascending sequence or -1 for a descending sequence..
   7. Cycle - Select this option to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

      Deselect this option to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

3. Click Create Sequence.

3.6.2 Browsing a Sequence

Select a sequence from the Object Tree and access tabs in the Object Detail View.

To browse a sequence:

1. In Object Browser, Object Tree, expand Sequences and select a sequence.
2. The Object Detail View appears and displays four tabs:
   • Object Details - Displays details about the selected type. Available actions include:
     • Alter
     • Drop
     • Refresh
   • Grants - Displays a list of grants associated with the sequence. Available actions include:
     • Grant
     • Revoke
     • Refresh
   • Dependencies - Displays a list of objects that use (or depend) upon this sequence. Available actions include: Refresh.
   • DDL - Displays the DDL necessary to re-create this sequence. Available actions include:
     • Download.
     • Refresh

3.6.3 Dropping a Sequence

Select a sequence from the Object Tree pane and click Drop on the Object Details tab.

To drop a sequence:

1. In Object Browser, Object Tree, expand Sequences and select a sequence.

   The Object Detail View appears.

2. On the Object Details tab, click Drop.
   1. Drop - Review the details.
   2. SQL - Displays the SQL generated to drop the sequence. To copy the displayed SQL, click the Copy icon.
   3. To confirm your selection, click Drop again.