際際滷

際際滷Share a Scribd company logo

Part 7 alter sequence modified

Download as PPTX, PDF
Girija Muscut
Girija Muscut

The ALTER SEQUENCE statement can be used to change attributes of an existing sequence like the increment, minimum and maximum values, cached numbers, and behavior. However, Oracle performs some validations on the changes, such as not allowing the starting sequence number to be altered or the maximum value to be set lower than the current sequence number. The alter only affects future sequence numbers, not those already generated. Changing the increment may cause some sequence numbers to be skipped if done before first use of the sequence. To retain the original starting value while changing increment, the sequence should be dropped and recreated.

Convert to study materialsBETA

Transform any presentation into ready-made study materialselect from outputs like summaries, definitions, and practice questions.

1 of 5
Download to read offline
1DR. GIRIJA NARASIMHAN Part 7- Alter Sequence
2 DR. GIRIJA NARASIMHAN Use the ALTER SEQUENCE statement to change the increment, minimum and maximum values, cached numbers, and behavior of an existing sequence. This statement affects only future sequence numbers.
SQL> alter sequence seq1 start with 1001; alter sequence seq1 start with 1001 * ERROR at line 1: ORA-02283: cannot alter starting sequence number SQL> alter sequence seq1 increment by 2; Sequence altered. SQL> select seq1.nextval from dual; NEXTVAL ---------- 1001 SQL> select seq1.nextval from dual; NEXTVAL ---------- 1003 If you change the INCREMENT BY value before the first invocation of NEXTVAL, some sequence numbers will be skipped. Therefore, if you want to retain the original START WITH value, you must drop the sequence and re-create it with the original START WITH value and the new INCREMENT BY value. create sequence seq1 START WITH 1000 INCREMENT BY 1 NOCACHE maxvalue 1005 minvalue 1000 cycle; DR. GIRIJA NARASIMHAN
Oracle Database performs some validations. For example, a new MAXVALUE cannot be imposed that is less than the current sequence number. SQL> select seq1.currval from dual; CURRVAL ---------- 1003 SQL> alter sequence seq1 maxvalue 1002; alter sequence seq1 maxvalue 1002 * ERROR at line 1: ORA-04009: MAXVALUE cannot be made to be less than the current value DR. GIRIJA NARASIMHAN
http://www.slideshare.net/nbgirija

More Related Content

Part 7 alter sequence modified

  • 1. 1DR. GIRIJA NARASIMHAN Part 7- Alter Sequence
  • 2. 2 DR. GIRIJA NARASIMHAN Use the ALTER SEQUENCE statement to change the increment, minimum and maximum values, cached numbers, and behavior of an existing sequence. This statement affects only future sequence numbers.
  • 3. SQL> alter sequence seq1 start with 1001; alter sequence seq1 start with 1001 * ERROR at line 1: ORA-02283: cannot alter starting sequence number SQL> alter sequence seq1 increment by 2; Sequence altered. SQL> select seq1.nextval from dual; NEXTVAL ---------- 1001 SQL> select seq1.nextval from dual; NEXTVAL ---------- 1003 If you change the INCREMENT BY value before the first invocation of NEXTVAL, some sequence numbers will be skipped. Therefore, if you want to retain the original START WITH value, you must drop the sequence and re-create it with the original START WITH value and the new INCREMENT BY value. create sequence seq1 START WITH 1000 INCREMENT BY 1 NOCACHE maxvalue 1005 minvalue 1000 cycle; DR. GIRIJA NARASIMHAN
  • 4. Oracle Database performs some validations. For example, a new MAXVALUE cannot be imposed that is less than the current sequence number. SQL> select seq1.currval from dual; CURRVAL ---------- 1003 SQL> alter sequence seq1 maxvalue 1002; alter sequence seq1 maxvalue 1002 * ERROR at line 1: ORA-04009: MAXVALUE cannot be made to be less than the current value DR. GIRIJA NARASIMHAN