oracle返回序列值不会增加,sql – 如何获取一个oracle序列的当前值,而不增加它？...

SELECT last_number

FROM all_sequences

WHERE sequence_owner = ''

AND sequence_name = '';

您可以从user_sequences，all_sequences和dba_sequences获取各种序列元数据。

这些视图在会话中工作。

编辑：

如果序列在您的默认模式中，则：

SELECT last_number

FROM user_sequences

WHERE sequence_name = '';

如果你想要所有的元数据，那么：

SELECT *

FROM user_sequences

WHERE sequence_name = '';

希望它帮助…

EDIT2：

如果你的缓存大小不是1，一个长时间的做法更可靠的方式是：

SELECT increment_by I

FROM user_sequences

WHERE sequence_name = 'SEQ';

I

-------

1

SELECT seq.nextval S

FROM dual;

S

-------

1234

-- Set the sequence to decrement by

-- the same as its original increment

ALTER SEQUENCE seq

INCREMENT BY -1;

Sequence altered.

SELECT seq.nextval S

FROM dual;

S

-------

1233

-- Reset the sequence to its original increment

ALTER SEQUENCE seq

INCREMENT BY 1;

Sequence altered.

只要小心，如果其他人在这段时间使用序列 – 他们(或你)可能会得到

ORA-08004: sequence SEQ.NEXTVAL goes below the sequences MINVALUE and cannot be instantiated

此外，您可能希望在重置之前将缓存设置为NOCACHE，然后恢复到其原始值，以确保您没有缓存大量的值。