Desvendando o erro ORA-00955 durante a criação de uma primary key ou unique key

Olá,

É possível que muitos desenvolvedores já tenham passado pelo erro "ORA-00955: nome já está sendo usado por um objeto existente" durante a criação da primary key ou de uma unique key em uma tabela. Mas, porque isso acontece? Bom, sabemos que toda constraint (primary key ou unique key) ao ser criada, geralmente vem acompanhada também de um índice único criada com o mesmo nome da constraint. Mas, se por acaso algum índice já existente, seja ele único ou não, for útil à constraint, então o mesmo será utilizado por ela. Neste ponto, podemos chegar a conclusão de que o índice em si não é utilizado para garantir unicidade como muitos imaginam. Podemos verificar essa afirmação vendo o exemplo abaixo:

SQL> create table tabela1 (cod number);

Tabela criada.

-- criando um índice normal (não único)
SQL> create index idx_tabela1_cod on tabela1(cod);

Índice criado.

-- criando a chave primária
SQL> alter table tabela1 add constraint pk_tabela1 primary key (cod);

Tabela alterada.

SQL> select table_name,constraint_name,index_name from user_constraints;

TABLE_NAME             CONSTRAINT_NAME          INDEX_NAME
---------------------- ------------------------ ---------------------
TABELA1                PK_TABELA1               IDX_TABELA1_COD

Podemos ver pelo resultado acima que o Oracle não criou um índice chamado PK_TABELA1, em vez disso ele utilizou o índice IDX_TABELA1_COD existente. Tudo bem, mas o que aconteceria se dropássemos a chave primária? O índice seria dropado também?

A regra é, se o índice foi criado implicitamente durante a criação da constraint, então por padrão o Oracle irá dropar também o índice, a não ser que utilizemos a palavra chave (keep index) para manter o índice ao dropar a constraint.

Mas, e quanto em relação ao índice que já existia antes da criação da constraint? Neste caso, até a versão do Oracle 9i, o índice seria dropado também, pois por padrão, no Oracle 9i a cláusula (drop index) está implicitamente atrelada ao comando que dropa a constraint. Vejamos na demonstração abaixo:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> create table t1 (id number);

Tabela criada.

SQL> create unique index pk_t1 on t1 (id);

Índice criado.

SQL> alter table t1 add constraint pk_t1 primary key (id);

Tabela alterada.

SQL> alter table t1 drop constraint pk_t1;

Tabela alterada.

SQL> select constraint_name from user_constraints where table_name='T1';

não há linhas selecionadas

SQL> select index_name from user_indexes where table_name='T1';

não há linhas selecionadas

Percebemos que, após droparmos a chave primária da tabela T1 no Oracle 9i, o índice que ela utilizava também foi dropado. O comando que o Oracle interpretou foi "alter table t1 drop constraint pk_t1 DROP INDEX" Por um lado, isso não é bom, pois não foi a PK quem criou o índice. O índice foi criado para ser utilizado para outros fins (performance, etc...) e o Oracle simplesmente dropa o índice sem nos avisar? Deve ser por esse motivo que o padrão mudou à partir do Oracle 10g:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table t1 (id number);

Tabela criada.

SQL> create unique index pk_t1 on t1 (id);

Índice criado.

SQL> alter table t1 add constraint pk_t1 primary key (id);

Tabela alterada.

SQL> alter table t1 drop constraint pk_t1;

Tabela alterada.

SQL> select constraint_name from user_constraints where table_name='T1';

não há linhas selecionadas

SQL> select index_name from user_indexes where table_name='T1';

INDEX_NAME
--------------------
PK_T1

Podemos ver acima que, após droparmos a chave primária da tabela T1 no Oracle 10g R2, o índice que ela utilizava foi mantido. O comando que o Oracle 10g interpretou foi "alter table t1 drop constraint pk_t1 KEEP INDEX". Se quiséssemos dropar também o índice, teríamos que utilizar a cláusula (drop index). Vale a pena salientar que esse comportamento só é válido quando a constraint reutiliza um índice existente. Portanto, um índice que foi criado implicitamente durante a criação de uma constraint, também será dropado junto com a constraint.

Após essa longa introdução, irei demonstrar abaixo que uma simples exportação e importação de tabelas, seja via os utilitários exp/imp ou expdp/impdp, poderá ser a causa do erro ORA-00955 durante a criação de uma primary key ou unique key.

Vamos partir do comando abaixo:

SQL> create table t1 (id number constraint pk_t1 primary key);

Tabela criada.

Bom, já sabemos que se droparmos a chave primária da tabela T1, o índice que foi implicitamente criado também será dropado. Mas, o que acontece se exportamos essa tabela e a importarmos novamente no banco de dados? Para realização dos testes irei utilizar o Oracle 11g R2.

C:\>exp scott/tiger file=t1.dmp tables=t1

Export: Release 11.2.0.1.0 - Production on Dom Nov 6 10:01:39 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Exportação executada no conjunto de caracteres de WE8MSWIN1252 e no conjunto de
caracteres de AL16UTF16 NCHAR

Sobre exportar tabelas especificadas ... via Caminho Convencional ...
. . exportando tabela                          T1          0 linhas exportadas
Exportação encerrada com sucesso, sem advertências.

Após a exportação da tabela T1 pelo utilitário (exp), irei verificar abaixo como o Oracle irá executar os comandos DDL de criação da tabela durante a importação:

C:\>imp scott/tiger file=t1.dmp indexfile=t1.txt

Import: Release 11.2.0.1.0 - Production on Dom Nov 6 10:03:14 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Arquivo de exportação criado por EXPORT:V11.02.00 via caminho convencional
importação realizada nos conjuntos de caracteres WE8MSWIN1252 e NCHAR AL16UTF16
. . ignorando tabela "T1"

Importação encerrada com sucesso, sem advertências.

C:\>type t1.txt

REM  CREATE TABLE "SCOTT"."T1" ("ID" NUMBER) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 0 rows
CONNECT SCOTT;
CREATE UNIQUE INDEX "SCOTT"."PK_T1" ON "T1" ("ID" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ;
REM  ALTER TABLE "SCOTT"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("ID")
REM  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536
REM  NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM  DEFAULT) TABLESPACE "USERS" LOGGING ENABLE ;

Acima, podemos perceber que o Oracle irá primeiro criar o índice para depois criar a constraint primary key. Esse comportamento também é visto se utilizarmos o utilitário de exportação e importação Oracle DataPump:

C:\>expdp scott/tiger tables=t1 directory=data_pump_dir dumpfile=t1.dmp

Export: Release 11.2.0.1.0 - Production on Dom Nov 6 10:08:57 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=t1
directory=data_pump_dir dumpfile=t1.dmp
Estimativa em andamento com o método BLOCKS...
Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o método de BLOCKS: 0 KB
Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE
Processando o tipo de objeto TABLE_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exportou "SCOTT"."T1"                                  0 KB       0 linhas
Tabela-mestre "SCOTT"."SYS_EXPORT_TABLE_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SCOTT.SYS_EXPORT_TABLE_01 é:
  E:\ORACLE\ADMIN\BD02\DPDUMP\T1.DMP
O job "SCOTT"."SYS_EXPORT_TABLE_01" foi concluído com sucesso em 10:09:27

Após a exportação da tabela T1 pelo utilitário (expdp), irei verificar abaixo como o Oracle irá executar os comandos DDL de criação da tabela durante a importação:

C:\>impdp scott/tiger tables=t1 directory=data_pump_dir dumpfile=t1.dmp sqlfile=t1.txt

Import: Release 11.2.0.1.0 - Production on Dom Nov 6 10:12:35 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Tabela-mestre "SCOTT"."SYS_SQL_FILE_TABLE_01" carregada/descarregada com sucesso
Iniciando "SCOTT"."SYS_SQL_FILE_TABLE_01":  scott/******** tables=t1
directory=data_pump_dir dumpfile=t1.dmp SQLFILE=t1.txt
Processando o tipo de objeto TABLE_EXPORT/TABLE/TABLE
Processando o tipo de objeto TABLE_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
O job "SCOTT"."SYS_SQL_FILE_TABLE_01" foi concluído com sucesso em 22:12:43

C:\>type E:\oracle\admin\BD02\dpdump\t1.txt

-- CONNECT SCOTT
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."T1"
   (    "ID" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
  DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "SCOTT"."PK_T1" ON "SCOTT"."T1" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
  DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" PARALLEL 1 ;

  ALTER INDEX "SCOTT"."PK_T1" NOPARALLEL;

-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "SCOTT"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE
  DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;

Da mesma forma como aconteceu no exemplo com os utilitários (exp/imp), podemos perceber acima que o Oracle também irá primeiro criar o índice para depois criar a constraint primary key. Após a importação da tabela T1, irei simular abaixo o erro ORA-00955:

C:\>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Dom Nov 6 10:23:45 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter table t1 drop primary key;

Tabela alterada.

SQL> alter table t1 add (id2 number);

Tabela alterada.

SQL> alter table t1 add constraint pk_t1 primary key (id,id2);
alter table t1 add constraint pk_t1 primary key (id,id2)
* ERRO na linha 1: ORA-00955: nome já está sendo usado por um objeto existente

Perceberam o motivo do erro? O Oracle tentou criar um índice com o nome PK_T1. A questão é que o índice que foi utilizado pela chave primária anterior foi mantido no banco de dados:

SQL> select table_name,index_name from user_indexes;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
T1                             PK_T1

A explicação é que, após dropar a chave primária, criar uma nova coluna na tabela e criar novamente a chave primária, o índice PK_T1 existente não foi reaproveitado (por motivos óbvios). Neste caso, o Oracle tentou criar um índice com o mesmo nome da constraint, o que gerou o erro ORA-00955. Para resolver o problema teremos que dropar o índice PK_T1 explicitamente antes de criar a nova primary key.

SQL> drop index pk_t1;

Índice eliminado.

SQL> alter table t1 add constraint pk_t1 primary key (id,id2);

Tabela alterada.