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 session
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
1
SQL> select seq_test.currval from dual;
CURRVAL
----------
1
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
2
SQL> 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 1
ORA-16000: database open for read-only access
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 session
角色切换后,在新主库上查询
SQL> conn baixyu/kobebai
Connected.
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 session
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
51
直接从51开始了,这块在角色切换的时候需要注意
SQL> select seq_test.nextval from dual;
NEXTVAL
----------
103
SQL> 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 NUMBER
SQL> 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
----------
153
SQL> select last_number from user_sequences;
LAST_NUMBER
-----------
203
在主从切换后,也是直接在磁盘读取的,上面的这种情况,在主从切换后,序列的值是203.测试情况如下:
SQL> conn baixyu/kobebai
Connected.
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 session
SQL> 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.