Inserting And Updating Sequences FAQS

1. Can I directly update a sequence value?

• No, you cannot directly update the value of a sequence in Oracle. Sequences are designed to automatically generate unique values. To change a sequence’s starting point or increment, you would need to drop and recreate the sequence.

2. What happens if I use CURRVAL before calling NEXTVAL in the session?

• If you try to use CURRVAL before calling NEXTVAL in the session, Oracle will throw an ORA-08002 error, as CURRVAL only works after NEXTVAL has been called.

3. Can I reuse sequence numbers?

• No, sequence numbers are not reusable. Once a sequence number is used, it is permanently allocated and cannot be reused, even if a transaction using that number is rolled back.

4. What happens when the sequence reaches its MAXVALUE?

• When a sequence reaches its MAXVALUE, it will either stop generating new values (if NOCYCLE is set) or it will restart from the MINVALUE (if CYCLE is set).

5. Can I reset a sequence?

• To reset a sequence, you need to drop and recreate the sequence. There is no direct command to reset a sequence’s value in Oracle.

6. What is the difference between NEXTVAL and CURRVAL?

• NEXTVAL generates the next unique sequence number and increments the sequence.
• CURRVAL returns the last generated sequence number in the current session, but it doesn't increment the sequence. CURRVAL can only be used after NEXTVAL is called in the same session.

7. What happens if a sequence is cached and the database crashes?

• If the database crashes, Oracle may lose some of the sequence values that were cached in memory. As a result, the sequence may have gaps in the numbers, but the sequence values will still be unique.

8. Can I insert multiple rows using a sequence in a single INSERT statement?

• Yes, you can use NEXTVAL in a multi-row INSERT statement to generate unique values for each row. Example:

·        INSERT INTO employees (employee_id, employee_name)

·        VALUES (employee_seq.NEXTVAL, 'Alice Smith'),

·               (employee_seq.NEXTVAL, 'Bob Johnson');

9. What happens if I don't specify CACHE or NOCACHE for a sequence?

• If you don’t specify the CACHE or NOCACHE option, Oracle uses the default value, which is typically CACHE 20. This means Oracle will cache 20 sequence numbers by default for faster performance.

10. Can I use sequences in an UPDATE statement?

• Yes, you can use NEXTVAL or CURRVAL in an UPDATE statement to modify a column with a sequence-generated value, although this is less common. Example:

·        UPDATE employees

·        SET employee_id = employee_seq.NEXTVAL

·        WHERE employee_name = 'John Doe';

11. How do I avoid gaps in sequence values?

• While gaps can occur due to database crashes or rolled-back transactions, there is no way to completely prevent gaps in sequence values. However, you can use NOCACHE to ensure values are generated sequentially, but this might affect performance.

12. Can I use a sequence for a non-primary key column?

• Yes, sequences are not limited to primary key columns. They can be used for generating unique values in any column that requires uniqueness, such as transaction IDs or version numbers.

13. How does Oracle handle sequence concurrency?

• Oracle sequences are designed to handle high concurrency. Multiple users or sessions can safely use the same sequence simultaneously, as Oracle manages sequence values to ensure uniqueness without collisions.

14. What is the default cache size for a sequence?

• If you don’t specify a CACHE value, Oracle typically defaults to caching 20 sequence numbers. This improves performance by reducing disk I/O.