oracle之Sequence

       今日实现功能插入数据时，Oracle数据库报  自增序列唯一约束冲突的错误！不解，于是查找资料，做了一番总结与记录。

这个错误最常见的情况就是数据迁移之后，导致数据最大值超过序列值。

       可以用以下两种方法解决：

1、先删除序列，然后重新创建，’这个方法比较方便。

2、通过Increment By来实现修改初始值，在这之前我们先查询一下当前序列当前值的值。

若序列名为：sequence_demo ；

SELECT sequence_demo.CURRVAL FROM dual        -- 查看当前值

然后我们修改初始值

Alter Sequence sequence_demo Increment By 1000

然后校验一下序列的nextval，一定要超过目前的数据最大值。

SELECT sequence_demo.nextval FROM dual              --查看下一序列值

问题解决完成，接下来在了解下其中的要点。

用select * from DBA_SEQUENCES 和 select * from USER_SEQUENCES 查看字段。其中

DBA_SEQUENCES记录了数据库中所有的sequence信息。（DBA_SEQUENCES describes all sequences in the database.）

USER_SEQUENCES记录了数据库中当前用户的sequence信息，但视图中不包含SEQUENCE_OWNER列。（USER_SEQUENCES describes all sequences owned by the current user. This view does not display the   SEQUENCE_OWNER column）

字段信息

Column	Datatype	NULL	Description
SEQUENCE_OWNER	VARCHAR2(30)	NOT NULL	Name of the owner of the sequence
SEQUENCE_NAME	VARCHAR2(30)	NOT NULL	Sequence name
MIN_VALUE	NUMBER		Minimum value of the sequence
MIN_VALUE	NUMBER		Maximum value of the sequence
INCREMENT_BY	NUMBER	NOT NULL	Value by which sequence is incremented
CYCLE_FLAG	VARCHAR2(1)		Does sequence wrap around on reaching limit
ORDER_FLAG	VARCHAR2(1)		Are sequence numbers generated in order
CACHE_SIZE	NUMBER	NOT NULL	Number of sequence numbers to cache
LAST_NUMBER	NUMBER	NOT NULL	Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used.

Alter Sequence 根据需要修改序列的当前值、最大值、最小值、缓存长度等。

还有一张Oracle Database SQL Reference  图

ALTER SEQUENCE [ schema. ]sequence
  { INCREMENT BY integer
  | { MAXVALUE integer | NOMAXVALUE }
  | { MINVALUE integer | NOMINVALUE }
  | { CYCLE | NOCYCLE }
  | { CACHE integer | NOCACHE }
  | { ORDER | NOORDER }
  }
    [ INCREMENT BY integer
    | { MAXVALUE integer | NOMAXVALUE }
    | { MINVALUE integer | NOMINVALUE }
    | { CYCLE | NOCYCLE }
    | { CACHE integer | NOCACHE }
    | { ORDER | NOORDER }
    ]... ;

参考： https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_2011.htm
            https://blog.csdn.net/sky6even/article/details/79486582

本博客为个人记录总结，若有侵权望告知，及时删除。