Sequence é um objeto do Oracle que gera números inteiros únicos de forma automática, funcionando bem mesmo com várias sessões acessando ao mesmo tempo. É o jeito clássico de gerar chave substituta (surrogate key), numerar documentos ou qualquer contador que precise ser único.
Sintaxe Básica
CREATE SEQUENCE nome_da_sequencia
[ START WITH valor_inicial ]
[ INCREMENT BY incremento ]
[ MINVALUE valor_minimo | NOMINVALUE ]
[ MAXVALUE valor_maximo | NOMAXVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE tamanho_cache | NOCACHE ]
[ ORDER | NOORDER ];
Todas as cláusulas são opcionais e podem vir em qualquer ordem. Um simples CREATE SEQUENCE minha_sequencia; já funciona, usando os valores padrão do Oracle.
Exemplo Simples
CREATE SEQUENCE minha_sequencia
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
Esse exemplo é bom para aprender porque deixa tudo explícito. Só que em produção, na prática, o ideal é manter o CACHE ligado (falo mais sobre isso na parte de performance).
Parâmetros Principais
- START WITH: valor inicial da sequência. Se não informar, o Oracle usa o MINVALUE (ou MAXVALUE se for decrescente).
- INCREMENT BY: valor de incremento, padrão 1. Pode ser negativo para criar uma sequência decrescente, mas nunca pode ser 0.
- MINVALUE: valor mínimo permitido.
- MAXVALUE: valor máximo permitido (o padrão é um número gigante, praticamente ilimitado).
- CYCLE / NOCYCLE: define se a sequência reinicia ao chegar no limite ou se simplesmente para de gerar valores e lança erro. Padrão é NOCYCLE. Cuidado: com CYCLE ligado a sequência pode repetir valores, então não use isso numa chave primária sem pensar bem no caso de uso.
- CACHE / NOCACHE: quantos valores o Oracle pré-aloca em memória. Padrão é CACHE 20.
- ORDER / NOORDER: só importa mesmo em ambiente RAC, garante que os valores saiam em ordem estrita entre instâncias.
Uso da Sequência
Para pegar o próximo valor:
SELECT minha_sequencia.NEXTVAL FROM DUAL;
Cada NEXTVAL consome um número da sequência, e esse número nunca volta, nem se a transação der rollback.
Para ver o valor atual sem incrementar, usa o CURRVAL:
SELECT minha_sequencia.CURRVAL FROM DUAL;
Só um detalhe importante aqui: CURRVAL mostra o último valor gerado pela sua própria sessão, não o último valor gerado por qualquer sessão do banco. Por isso ele só funciona depois que você já rodou pelo menos um NEXTVAL na mesma sessão. Se tentar usar CURRVAL antes disso, o Oracle devolve o erro:
ORA-08002: sequence MINHA_SEQUENCIA.CURRVAL is not yet defined in this session
Um uso bem comum é encadear inserts em tabelas relacionadas:
INSERT INTO pedidos (id_pedido, id_cliente)
VALUES (seq_pedidos.NEXTVAL, 100);
INSERT INTO itens_pedido (id_item, id_pedido, produto)
VALUES (seq_itens.NEXTVAL, seq_pedidos.CURRVAL, 'Teclado');
Em PL/SQL, a partir do 12c, dá pra atribuir direto numa variável sem precisar do SELECT FROM DUAL:
DECLARE
v_id pedidos.id_pedido%TYPE;
BEGIN
v_id := seq_pedidos.NEXTVAL;
INSERT INTO pedidos (id_pedido, id_cliente) VALUES (v_id, 100);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
Vale lembrar que NEXTVAL e CURRVAL não podem ser usados em qualquer lugar. Não funcionam em WHERE, GROUP BY, ORDER BY, DISTINCT, em operadores como UNION e MINUS, dentro de subquery de view, nem em constraint CHECK.
Sequence Ligada a Coluna
Antes do Oracle 12c não existia coluna com auto incremento nativo. O jeito de fazer era criar a sequence e alimentar ela via trigger:
-- Padrão pré-12c
CREATE SEQUENCE seq_clientes START WITH 1 INCREMENT BY 1;
CREATE TABLE clientes (
id NUMBER,
nome VARCHAR2(100),
CONSTRAINT pk_clientes PRIMARY KEY (id)
);
CREATE OR REPLACE TRIGGER trg_clientes_bi
BEFORE INSERT ON clientes
FOR EACH ROW
BEGIN
IF :NEW.id IS NULL THEN
:NEW.id := seq_clientes.NEXTVAL;
END IF;
END;
/
Do 12c pra frente isso ficou bem mais simples. Existem duas alternativas melhores.
Sequence como DEFAULT da coluna:
CREATE TABLE clientes (
id NUMBER DEFAULT ON NULL seq_clientes.NEXTVAL,
nome VARCHAR2(100),
CONSTRAINT pk_clientes PRIMARY KEY (id)
);
Ou coluna IDENTITY, que hoje é o mais recomendado para chave substituta:
CREATE TABLE clientes (
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
nome VARCHAR2(100),
CONSTRAINT pk_clientes PRIMARY KEY (id)
);
Tem três variantes de IDENTITY que valem conhecer. GENERATED ALWAYS AS IDENTITY não deixa você informar valor nenhum no INSERT (dá erro ORA-32795 se tentar). GENERATED BY DEFAULT AS IDENTITY usa a sequence só quando você não informa a coluna. E GENERATED BY DEFAULT ON NULL AS IDENTITY usa a sequence tanto quando a coluna é omitida quanto quando vem NULL explícito, essa última costuma ser a mais fácil de usar com ORM e em cargas de dados legadas.
Por baixo dos panos o Oracle cria uma sequence interna (aparece como ISEQ$$_... em USER_SEQUENCES), que some junto quando você dropa a tabela. Dá pra ajustar os parâmetros dela direto na criação da tabela:
CREATE TABLE clientes (
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1 CACHE 100),
nome VARCHAR2(100)
);
Na prática, para chave substituta em banco 12c ou mais novo, eu recomendo usar IDENTITY. Guarde a sequence separada só quando o mesmo contador precisa ser compartilhado entre tabelas diferentes, quando você precisa saber o valor antes de fazer o INSERT, ou quando a numeração tem algum significado pro negócio.
Performance: CACHE, NOCACHE e os Gaps
O CACHE faz o Oracle reservar um bloco de valores na memória de uma vez. Enquanto tem valor em cache, o NEXTVAL é resolvido direto na memória. Sem cache, cada chamada precisa atualizar o dicionário de dados numa transação recursiva, o que é bem mais lento.
NOCACHE só compensa em sequence usada raramente. Em cenário de alta concorrência isso vira gargalo na certa. Já o CACHE, mesmo o padrão de 20 já ajuda bastante, mas para sequence bem usada (insert em massa, aplicação OLTP, RAC) é comum ver CACHE de 1000 ou mais.
Falando em RAC, cada instância mantém seu próprio cache separado. O ideal ali é CACHE grande com NOORDER. Se colocar ORDER junto com NOCACHE, a geração de valores fica serializada entre instâncias e derruba a escalabilidade do banco.
Uma coisa que sempre gera dúvida é por que aparecem buracos na numeração. A resposta é simples: sequence garante que o número é único, não que ele é contínuo. Isso acontece por rollback (o número já foi consumido e não volta), por perda de cache (se a instância reinicia ou a shared pool é limpa, os valores que estavam em cache e não foram usados se perdem), por concorrência entre sessões, ou por algum INSERT que falhou depois de já ter puxado o número da sequence.
Se o requisito for numeração sem buraco nenhum, tipo nota fiscal, sequence não é a ferramenta certa. Aí entra outra abordagem, como uma tabela de controle com SELECT FOR UPDATE, aceitando que isso serializa o acesso e derruba a concorrência.
Privilégios Necessários
Para criar sequence no seu próprio schema você precisa do privilégio CREATE SEQUENCE. Para criar em qualquer schema, CREATE ANY SEQUENCE. Para alterar ou remover sequence de outro schema, ALTER ANY SEQUENCE e DROP ANY SEQUENCE. E para usar sequence de qualquer schema, SELECT ANY SEQUENCE.
Se for privilégio de objeto, para outro usuário poder usar a sua sequence:
GRANT SELECT ON meu_schema.minha_sequencia TO usuario_app;
Pra conferir o que a sua sessão já tem de privilégio:
SELECT privilege
FROM session_privs
WHERE privilege LIKE '%SEQUENCE%';
E pra ver os privilégios de objeto concedidos sobre suas sequences:
SELECT grantee, table_name, privilege
FROM user_tab_privs_made
WHERE table_name = 'MINHA_SEQUENCIA';
Sequence não ocupa espaço em tablespace de dados, é um objeto de dicionário, então não precisa se preocupar com quota.
Alterando uma Sequência
ALTER SEQUENCE minha_sequencia
INCREMENT BY 10
MAXVALUE 999999999
CACHE 1000;
Um detalhe que pega bastante gente: START WITH não pode ser alterado via ALTER SEQUENCE em versões anteriores ao 12.2 (dá erro ORA-02283). A partir do 12.2 existe a cláusula RESTART:
ALTER SEQUENCE minha_sequencia RESTART START WITH 1;
Em versão mais antiga, o jeito de contornar é mexer temporariamente no incremento:
ALTER SEQUENCE minha_sequencia INCREMENT BY -500 NOCACHE;
SELECT minha_sequencia.NEXTVAL FROM DUAL;
ALTER SEQUENCE minha_sequencia INCREMENT BY 1 CACHE 20;
Qualquer ALTER SEQUENCE descarta os valores que já estavam em cache, então isso naturalmente cria um gap. E claro, a alteração só vale para os valores futuros, nada do que já foi gerado muda.
Removendo uma Sequência
DROP SEQUENCE minha_sequencia;
O DROP não mexe nos dados que já estão gravados nas tabelas, mas invalida trigger, view, procedure ou default que dependa da sequence. E sequence interna de coluna IDENTITY não dá pra dropar sozinha, ela some junto quando a tabela é removida.
Consultando as Sequências Existentes
SELECT sequence_name,
min_value,
max_value,
increment_by,
cycle_flag,
order_flag,
cache_size,
last_number
FROM user_sequences
ORDER BY sequence_name;
Um cuidado aqui: a coluna LAST_NUMBER, quando tem CACHE ligado, mostra o valor gravado no dicionário (o topo da faixa reservada), não necessariamente o próximo valor que o NEXTVAL vai devolver. Já vi gente se confundir com isso. Para ver sequence de outros schemas, use ALL_SEQUENCES ou DBA_SEQUENCES.
Resumindo
Sequence gera número único com boa performance em ambiente concorrido, mas não promete número contínuo. NEXTVAL incrementa, CURRVAL só lê o último valor gerado pela sua sessão e exige um NEXTVAL antes. Mantenha CACHE ligado nas sequences mais usadas. A partir do 12c, para chave substituta, vale mais a pena usar IDENTITY e deixar a sequence separada só pros casos que realmente precisam dela. E gap na numeração é normal, faz parte de como sequence funciona.