A sequence creates an ever increasing number that is guaranteed to be unique (if they are not reset), even across different sessions. This property makes them an ideal candiate for a surrogate primary key.
A session can acquire a number from a sequence without blocking another session or waiting for another session that is concurrently acquiring a number from the same sequence.
create sequence tq84_sequence
start with 1
increment by 1
nocache
nocycle;
select tq84_sequence.nextval from dual;
select * from user_sequences where sequence_name = 'TQ84_SEQUENCE';
drop sequence tq84_sequence;
create table tq84_autoincrement (
id number primary key,
txt varchar2(20)
);
create sequence tq84_autoincrement_seq;
create or replace trigger tq84_autoincrement_ins
before insert on tq84_autoincrement
for each row
when (new.id is null)
begin
select tq84_autoincrement_seq.nextval into :new.id from dual;
end;
/
insert into tq84_autoincrement (txt) values ('one');
insert into tq84_autoincrement (txt) values ('two');
insert into tq84_autoincrement (id, txt) values (100, 'hundred');
select * from tq84_autoincrement;
drop table tq84_autoincrement purge;
drop sequence tq84_autoincrement_seq;
create table tq84_failed_insert(
i number primary key,
j varchar2(20)
);
create sequence tq84_failed_insert_seq start with 1 increment by 1;
insert into tq84_failed_insert values (5, lpad('-', 5, '-'));
insert into tq84_failed_insert values (9, lpad('-', 9, '-'));
begin
for i in 1 .. 10 loop
begin
insert into tq84_failed_insert values(
tq84_failed_insert_seq.nextval,
lpad('-', i, '-')
);
exception when dup_val_on_index then
null;
end;
end loop;
end;
/
select * from tq84_failed_insert order by i;
drop table tq84_failed_insert purge;
drop sequence tq84_failed_insert_seq;
In Oracle 12c, it's possible to create session level sequences:
create sequence session_seq start with 1 increment by 1
/* Note the following keyword: */ SESSION;
select session_seq.nextval from dual;
select session_seq.nextval from dual;
select session_seq.nextval from dual;
select session_seq.nextval from dual;
connect &username/&password
select session_seq.nextval from dual;
select session_seq.nextval from dual;
select session_seq.nextval from dual;
select session_seq.nextval from dual;
drop sequence session_seq;
--
-- Note: a session level sequence can be changed to a global sequence and vice versa
-- alter sequence session_seq global;
-- alter sequence session_seq session;