Oracle新表使用序列(sequence)作为插入值，初始值不是第一个，oraclesequence

2022-12-04 11:45:25

使用oracle11g插入数据时遇到这样一个问题：

1 --创建测试表--
2 CREATE TABLE tbl_test(
3 test_id NUMBER PRIMARY KEY,
4 test_name VARCHAR2(20)
5 );
6
7 --为tbl_test创建序列--
8 CREATE SEQUENCE seq_test
9 　　　　INCREMENT BY 1 -- 每次加几个
10 　　　　START WITH 1 -- 从1开始计数
11 ;
12
13 --插入测试数据--
14 INSERT INTO tbl_test VALUES(seq_test.nextval,'测试');
15 COMMIT;
16
17 --查询表中的数据
18 SELECT * FROM tbl_test;

显示结果：

问题原因：

·当我们使用序列作为插入数据时，如果使用了“延迟段”技术，则跳过序列的第一个值

·Oracle从 11.2.0.1版本开始，提供了一个“延迟段创建”特性：
即

当我们创建了新的表(table)和序列(sequence)，
在插入(insert)语句时，序列会跳过第一个值(1)。
所以结果是插入的序列值从 2(序列的第二个值) 开始，而不是 1开始。

想要解决这个问题有两种方法：
更改数据库的“延迟段创建”特性为false（需要有相应的权限）
ALTER SYSTEM SET deferred_segment_creation=FALSE;
或者
在创建表时让seqment立即执行，如：

CREATE TABLE tbl_test(
test_id NUMBER PRIMARY KEY,
test_name VARCHAR2(20)
)
SEGMENT CREATION IMMEDIATE;

以上两种方法都可以解决之前的问题。

oracle中怎获得某个表的正在或曾经使用的sequence?
好象它们两者没有依属联系,而确实在工作中又容易遇见此类问题.
从序列的角度来思考的话，仅跟用户相关。
以下步骤可以参考:
1 从系统视图中取得所有序列的相关信息。
注意相关权限。
select sequence_name,min_value,max_value,increment_by,last_number from all_sequences
如果能够能够排除用户A的表数据，采用了用户B的sequence来生成数据的情况，那可以加上
where sequence_owner=' '; -----适合的用户
或者从USER_SEQUENCES中提取。
2 从关心的表中提取敏感字段的最大值
select max(id) from test;
3 将两者对比。或者将上述两个查询连接到一起来查询。如果当前该表没有被插入，并且，近期没有被删除，那么，该max（id）+1=last_number ；
如果非要弄清楚的话，那就将表数据保护起来，拒绝删除数据，当发现数据插入后，观察插入的频度和事务提交后序列列值变化的大小，由此最终能判断出是哪个序列号。基于非要准确的搞清楚的前提下，使用排出法，将上述最接近的序列按照从轻度怀疑到高度怀疑的顺序，再备份的情况下，依次删除、恢复.....这样肯定能搞准。

还有一个最好的办法以后遇到这种情况就简单了。在该表上建立基于插入后的触发器，在触发器中包含上面查询思想，将max（id）+1=last_number 的序列名返回。。。。则绝对不会错
.
知识有限，在此也期盼能学习到更高级的方法。
希望能帮到你。.

oracle表中怎建序列
在oracle中sequence就是所谓的序列号，每次取的时候它会自动增加，一般用在需要按序列号排序的地方。
1、Create Sequence
你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限，
CREATE SEQUENCE emp_sequence
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10;

一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL
CURRVAL=返回 sequence的当前值
NEXTVAL=增加sequence的值，然后返回 sequence 值
比如：
emp_sequence.CURRVAL
emp_sequence.NEXTVAL

可以使用sequence的地方：

不包含子查询、snapshot、VIEW的 SELECT 语句
INSERT语句的子查询中
NSERT语句的VALUES中
UPDATE 的 SET中

可以看如下例子：
INSERT INTO emp VALUES
(empseq.nextval, 'LEWIS', 'CLERK',7902, SYSDATE, 1200, NULL, 20);

SELECT empseq.currval FROM DUAL;

但是要注意的是：

第一次NEXTVAL返回的是初始值；随后的NEXTVAL会自动增加你定义的INCREMENT BY值，然后返回增加后的值。CURRVAL 总是返回当前SEQUENCE的值，但是在第一次NEXTVAL初始化之后才能使用CURRVAL，否则会出错。一次NEXTVAL会增加一次SEQUENCE的值，所以如果你在同一个语句里面使用多个NEXTVAL，其值就是不一样的。明白？
如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组到cache。使用cache或许会跳号，比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。

2、Alter Sequence
你或者是该sequence的owner，或者有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值，必须 drop sequence 再 re-create .
Alter sequence 的例子
ALTER SEQUENCE emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE -- 到10000后从头开始
NOCACHE ;

码农公寓

相关文章