oracle dg中角色切换后的序列测试

SQL> create sequence seq_test minvalue 1 start with 1 increment by 1 cache 50;Sequence created.SQL> select seq_test.currval from dual;select seq_test.currval from dual       *ERROR at line 1:ORA-08002: sequence SEQ_TEST.CURRVAL is not yet defined in this sessionSQL> select seq_test.nextval from dual;   NEXTVAL----------     1SQL> select seq_test.currval from dual;   CURRVAL----------     1SQL> select seq_test.nextval from dual;   NEXTVAL----------     2SQL> select seq_test.nextval from dual;   NEXTVAL----------     3

在从库测试

SQL> select seq_test.nextval from dual;select seq_test.nextval from dual       *ERROR at line 1:ORA-00604: error occurred at recursive SQL level 1ORA-16000: database open for read-only accessSQL> select seq_test.currval from dual;select seq_test.currval from dual       *ERROR at line 1:ORA-08002: sequence SEQ_TEST.CURRVAL is not yet defined in this session

角色切换后，在新主库上查询

SQL> conn baixyu/kobebaiConnected.SQL> select seq_test.currval from dual;select seq_test.currval from dual       *ERROR at line 1:ORA-08002: sequence SEQ_TEST.CURRVAL is not yet defined in this sessionSQL> select seq_test.nextval from dual;   NEXTVAL----------    51

直接从51开始了，这块在角色切换的时候需要注意

SQL> select seq_test.nextval from dual;   NEXTVAL----------       103SQL> desc user_sequences; Name                      Null?    Type ----------------------------------------- -------- ---------------------------- SEQUENCE_NAME                 NOT NULL VARCHAR2(30) MIN_VALUE                      NUMBER MAX_VALUE                      NUMBER INCREMENT_BY                  NOT NULL NUMBER CYCLE_FLAG                     VARCHAR2(1) ORDER_FLAG                     VARCHAR2(1) CACHE_SIZE                NOT NULL NUMBER LAST_NUMBER                   NOT NULL NUMBERSQL> select last_number from user_sequences;LAST_NUMBER-----------    153

last_number是写入磁盘的sequence的值，基本上是第一次使用sequence后加上cache的值。当cache中的值用完了，在磁盘读的时候，last_number的值修改加上cache的大小。

SQL> select seq_test.nextval from dual;   NEXTVAL----------       153SQL> select last_number from user_sequences;LAST_NUMBER-----------    203

在主从切换后，也是直接在磁盘读取的，上面的这种情况，在主从切换后，序列的值是203.测试情况如下：

SQL> conn baixyu/kobebaiConnected.SQL> select seq_test.currval from dual;select seq_test.currval from dual       *ERROR at line 1:ORA-08002: sequence SEQ_TEST.CURRVAL is not yet defined in this sessionSQL> select seq_test.nextval from dual;   NEXTVAL----------       203

序列默认的情况下是无序的，可以在创建的时候指定属性
ORDER
Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.

NOORDER
Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.