oracle renumber,ORA-00001: unique constraint violated

Received procedure from Oracle support. I copy-paste it below

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

How To Renumber The Records In The ODI SNP_EXP_TXT Work Repository Table (Doc ID 753313.1)

Applies to:

Oracle Data Integrator - Version: 10.1.3.2.0 and later [Release: 10gR3 and later ]

Information in this document applies to any platform.

Goal

The document provides detailed SQL commands and step-by-step operations in order to:

•renumber the values of SNP_EXP_TXT and referencing tables, and

•compact the ID sequence which will be re-initialized with a starting value of 1.

I M P O R T A N T:

1. The SQL commands below should not be applied as stand alone, but only within the flow described in Note 603109.1.

2. Actually, only solution for Oracle and Microsoft SQLServer are finalized and fully tested.

Solution

Step 1. Stop all Oracle Data Integrator processes

Stop all Oracle Data Integrator (ODI) processes such as: Agents, Scenarios, Log purge, Packages, Integration Interfaces...

Step 2. Connect to the Work Repository.

Connect to the Database and Schema hosting the Work Repository tables.

Make sure transactional and not AUTOCOMMIT mode is used.

Step 3. Prepare the re-initialization operation.

Copy the existing SNP_EXP_TXT.I_TXT values into a temporary table.

•on Oracle:

/* Compute table/index statistics and resize the storage space */

analyze table SNP_EXP_TXT compute statistics;

alter table SNP_EXP_TXT enable row movement;

analyze table SNP_SESSION compute statistics;

alter table SNP_SESSION enable row movement;

analyze table SNP_STEP_LOG compute statistics;

alter table SNP_STEP_LOG enable row movement;

analyze table SNP_SESS_TASK_LOG compute statistics;

alter table SNP_SESS_TASK_LOG enable row movement;

analyze table SNP_SESS_TXT_LOG compute statistics;

alter table SNP_SESS_TXT_LOG enable row movement;

analyze table SNP_SESS_STEP compute statistics;

alter table SNP_SESS_STEP enable row movement;

analyze table SNP_SESS_TASK compute statistics;

alter table SNP_SESS_TASK enable row movement;

analyze table SNP_TASK_TXT compute statistics;

alter table SNP_TASK_TXT enable row movement;

analyze table SNP_VAR_SESS compute statistics;

alter table SNP_VAR_SESS enable row movement;

analyze table SNP_VAR_SCEN compute statistics;

alter table SNP_VAR_SCEN enable row movement;

analyze table SNP_STEP_REPORT compute statistics;

alter table SNP_STEP_REPORT enable row movement;

analyze table SNP_SCEN_REPORT compute statistics;

alter table SNP_SCEN_REPORT enable row movement;

/* For Oracle 10g+, and tablespace set in autoextend ONLY */

alter table SNP_EXP_TXT shrink space cascade;

alter table SNP_SESSION shrink space cascade;

alter table SNP_STEP_LOG shrink space cascade;

alter table SNP_SESS_TASK_LOG shrink space cascade;

alter table SNP_SESS_TXT_LOG shrink space cascade;

alter table SNP_SESS_STEP shrink space cascade;

alter table SNP_SESS_TASK shrink space cascade;

alter table SNP_TASK_TXT shrink space cascade;

alter table SNP_VAR_SESS shrink space cascade;

alter table SNP_VAR_SCEN shrink space cascade;

alter table SNP_STEP_REPORT shrink space cascade;

alter table SNP_SCEN_REPORT shrink space cascade;

/* create the working table */

create table XXX_EXP_TXT

(

NEW_ID NUMBER (19),

I_TXT NUMBER (19) NOT NULL,

primary key (I_TXT)

)

insert into XXX_EXP_TXT

(

I_TXT

)

select distinct I_TXT

from SNP_EXP_TXT

analyze table XXX_EXP_TXT compute statistics

•on Microsoft SQLServer:

create table XXX_EXP_TXT

(

ID NUMERIC(19) IDENTITY,

NEW_ID NUMERIC(19) NOT NULL,

I_TXT NUMERIC(19) NOT NULL,

primary key (I_TXT, NEW_ID)

)

insert into XXX_EXP_TXT

(

I_TXT, NEW_ID

)

select distinct I_TXT, 0

from SNP_EXP_TXT

•on IBM DB2/400:

(i). First, launch RGZPFM on XXX_EXP_TXT, in order to reorganize the Table.

(ii). Then:

create table XXX_EXP_TXT

(

NEW_ID NUMERIC (19) WITH DEFAULT,

I_TXT NUMERIC (19) NOT NULL WITH DEFAULT,

primary key (I_TXT)

)

insert into XXX_EXP_TXT

(

I_TXT

)

select distinct I_TXT

from SNP_EXP_TXT

•on IBM DB2 UDB:

create table XXX_EXP_TXT

(

NEW_ID NUMERIC (19) WITH DEFAULT,

I_TXT NUMERIC (19) NOT NULL WITH DEFAULT,

primary key (I_TXT)

)

insert into XXX_EXP_TXT

(

I_TXT

)

select distinct I_TXT

from SNP_EXP_TXT

Step 4. Compact and reorganize the IDs for use in the SNP_EXP_TXT table.

For the records stored in temporary XXX_EXP_TXT table, compute a compact sequence of IDs, starting with 1.

•on Oracle (solution based on ROWNUM standard function):

update XXX_EXP_TXT

set NEW_ID = rownum * 1000 + to_number(substr(to_char(I_TXT), length(to_char(I_TXT)) - 2, 3))

•on Microsoft SQLServer (solution based on IDENTITY column):

update X

set X.NEW_ID = X.ID * 1000 + substring(convert(varchar,I_TXT), len(convert(varchar,I_TXT)) - 2, 3)

from XXX_EXP_TXT X

•on IBM DB2/400 (solution based on RRN - Relative Record Number - function):

update XXX_EXP_TXT

set NEW_ID = rrn(XXX_EXP_TXT) * 1000 + bigint(substring(digits(I_TXT), length(digits(I_TXT)) - 2, 3))

•on IBM DB2 UDB (solution based on Database Sequences):

create sequence XXX_EXP_TXT_SEQ

start with 1

update XXX_EXP_TXT

set NEW_ID = (next value for XXX_EXP_TXT_SEQ) * 1000 + integer(substr(digits(I_TXT), length(digits(I_TXT)) - 2, 3))

Step 5. Update SNP_EXP_TXT and referencing tables.

Update SNP_EXP and related tables with new values of I_TXT:

•on Oracle (part I. See below for part II of the update):

create table YYY_EXP_TXT

as

select S.*

from SNP_EXP_TXT S

where 1=2

insert into YYY_EXP_TXT

(

FIRST_DATE,

FIRST_USER,

IND_CHANGE,

INT_VERSION,

I_TXT,

LAST_DATE,

LAST_USER,

TXT,

TXT_ORD

)

select

S.FIRST_DATE,

S.FIRST_USER,

S.IND_CHANGE,

S.INT_VERSION,

X.NEW_ID,

S.LAST_DATE,

S.LAST_USER,

S.TXT,

S.TXT_ORD

from SNP_EXP_TXT S,

XXX_EXP_TXT X

where X.I_TXT = S.I_TXT

truncate table SNP_EXP_TXT

insert into SNP_EXP_TXT

select * from YYY_EXP_TXT

drop table YYY_EXP_TXT

/* Re-compute table/index statistics and resize the storage space */

analyze table SNP_EXP_TXT compute statistics;

alter table SNP_EXP_TXT enable row movement;

analyze table SNP_SESSION compute statistics;

alter table SNP_SESSION enable row movement;

analyze table SNP_STEP_LOG compute statistics;

alter table SNP_STEP_LOG enable row movement;

analyze table SNP_SESS_TASK_LOG compute statistics;

alter table SNP_SESS_TASK_LOG enable row movement;

analyze table SNP_SESS_TXT_LOG compute statistics;

alter table SNP_SESS_TXT_LOG enable row movement;

analyze table SNP_SESS_STEP compute statistics;

alter table SNP_SESS_STEP enable row movement;

analyze table SNP_SESS_TASK compute statistics;

alter table SNP_SESS_TASK enable row movement;

analyze table SNP_TASK_TXT compute statistics;

alter table SNP_TASK_TXT enable row movement;

analyze table SNP_VAR_SESS compute statistics;

alter table SNP_VAR_SESS enable row movement;

analyze table SNP_VAR_SCEN compute statistics;

alter table SNP_VAR_SCEN enable row movement;

analyze table SNP_STEP_REPORT compute statistics;

alter table SNP_STEP_REPORT enable row movement;

analyze table SNP_SCEN_REPORT compute statistics;

alter table SNP_SCEN_REPORT enable row movement;

/* For Oracle 10g+, and tablespace set in autoextend ONLY */

alter table SNP_EXP_TXT shrink space cascade;

alter table SNP_SESSION shrink space cascade;

alter table SNP_STEP_LOG shrink space cascade;

alter table SNP_SESS_TASK_LOG shrink space cascade;

alter table SNP_SESS_TXT_LOG shrink space cascade;

alter table SNP_SESS_STEP shrink space cascade;

alter table SNP_SESS_TASK shrink space cascade;

alter table SNP_TASK_TXT shrink space cascade;

alter table SNP_VAR_SESS shrink space cascade;

alter table SNP_VAR_SCEN shrink space cascade;

alter table SNP_STEP_REPORT shrink space cascade;

alter table SNP_SCEN_REPORT shrink space cascade;

•on IBM DB2/400 and IBM DB2 UDB (part I. See below for part II of the update):

update SNP_EXP_TXT S

set S.I_TXT = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT = X.I_TXT

)

•on Oracle, IBM DB2/400 and IBM DB2 UDB (part II):

... execution related information:

/* SNP_SESS_TASK_LOG: Error messages and warnings related to Session Tasks */

update SNP_SESS_TASK_LOG S

set S.I_TXT_TASK_MESS = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_TASK_MESS = X.I_TXT

)

where S.I_TXT_TASK_MESS is not null

and exists (select 'X'

from SNP_SESS_TASK_LOG S2

where S.I_TXT_TASK_MESS = S2.I_TXT_TASK_MESS

)

/* SNP_STEP_LOG: Error messages and warnings related to Session Steps */

update SNP_STEP_LOG S

set S.I_TXT_STEP_MESS = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_STEP_MESS = X.I_TXT

)

where S.I_TXT_STEP_MESS is not null

and exists (select 'X'

from SNP_STEP_LOG S2

where S.I_TXT_STEP_MESS = S2.I_TXT_STEP_MESS

)

/* SNP_VAR_SESS (1): Values of Variables used during executions */

update SNP_VAR_SESS S

set S.I_TXT_VAR = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_VAR = X.I_TXT

)

where S.I_TXT_VAR is not null

and exists (select 'X'

from SNP_VAR_SESS S2

where S.I_TXT_VAR = S2.I_TXT_VAR

)

/* SNP_VAR_SESS (2): Default values of Variables used during executions */

update SNP_VAR_SESS S

set S.I_TXT_DEF_T = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_DEF_T = X.I_TXT

)

where S.I_TXT_DEF_T is not null

and exists (select 'X'

from SNP_VAR_SESS S2

where S.I_TXT_DEF_T = S2.I_TXT_DEF_T

)

/* SNP_SESSION (1): Session Parameters and Keywords */

update SNP_SESSION S

set S.I_TXT_SESS_PARAMS = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_SESS_PARAMS = X.I_TXT

)

where S.I_TXT_SESS_PARAMS is not null

and exists (select 'X'

from SNP_SESSION S2

where S.I_TXT_SESS_PARAMS = S2.I_TXT_SESS_PARAMS

)

/* SNP_SESSION (2): Error messages and warnings related to the Session */

update SNP_SESSION S

set S.I_TXT_SESS_MESS = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_SESS_MESS = X.I_TXT

)

where S.I_TXT_SESS_MESS is not null

and exists (select 'X'

from SNP_SESSION S2

where S.I_TXT_SESS_MESS = S2.I_TXT_SESS_MESS

)

/* SNP_STEP_REPORT: Error messages and warnings related to Scenario Report Steps */

update SNP_STEP_REPORT S

set S.I_TXT_STEP_MESS = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_STEP_MESS = X.I_TXT

)

where S.I_TXT_STEP_MESS is not null

and exists (select 'X'

from SNP_STEP_REPORT S2

where S.I_TXT_STEP_MESS = S2.I_TXT_STEP_MESS

)

/* SNP_SCEN_REPORT: Error messages and warnings related to Scenario Reports */

update SNP_SCEN_REPORT S

set S.I_TXT_SESS_MESS = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_SESS_MESS = X.I_TXT

)

where S.I_TXT_SESS_MESS is not null

and exists (select 'X'

from SNP_SCEN_REPORT S2

where S.I_TXT_SESS_MESS = S2.I_TXT_SESS_MESS

)

... Scenario related information:

/* SNP_SCEN : Scenario description */

update SNP_SCEN S

set S.I_TXT_SCEN = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_SCEN = X.I_TXT

)

where S.I_TXT_SCEN is not null

and exists (select 'X'

from SNP_SCEN S2

where S.I_TXT_SCEN = S2.I_TXT_SCEN

)

/* SNP_SCEN_FOLDER: Scenario Folder descriptions */

update SNP_SCEN_FOLDER S

set S.I_TXT_DESCRIPTION = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_DESCRIPTION = X.I_TXT

)

where S.I_TXT_DESCRIPTION is not null

and exists (select 'X'

from SNP_SCEN_FOLDER S2

where S.I_TXT_DESCRIPTION = S2.I_TXT_DESCRIPTION

)

... Scenario related and Text Type Variables:

/* SNP_VAR_SCEN (1): Values of Variables used during Scenario executions */

update SNP_VAR_SCEN S

set S.I_TXT_VAR = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_VAR = X.I_TXT

)

where S.I_TXT_VAR is not null

and exists (select 'X'

from SNP_VAR_SCEN S2

where S.I_TXT_VAR = S2.I_TXT_VAR

)

/* SNP_VAR_SCEN (2): Default values of Variables used during Scenario executions */

update SNP_VAR_SCEN S

set S.I_TXT_DEF_T = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_DEF_T = X.I_TXT

)

where S.I_TXT_DEF_T is not null

and exists (select 'X'

from SNP_VAR_SCEN S2

where S.I_TXT_DEF_T = S2.I_TXT_DEF_T

)

/* SNP_VAR_DATA: Values of Text Type Variables */

update SNP_VAR_DATA S

set S.I_TXT_VAR_T = (select X.NEW_ID

from XXX_EXP_TXT X

where S.I_TXT_VAR_T = X.I_TXT

)

where S.I_TXT_VAR_T is not null

and exists (select 'X'

from SNP_VAR_DATA S2

where S.I_TXT_VAR_T = S2.I_TXT_VAR_T

)

•on Microsoft SQLServer:

select S.*

into YYY_EXP_TXT

from SNP_EXP_TXT S

where 1=2

insert into YYY_EXP_TXT

(

FIRST_DATE,

FIRST_USER,

IND_CHANGE,

INT_VERSION,

I_TXT,

LAST_DATE,

LAST_USER,

TXT,

TXT_ORD

)

select

S.FIRST_DATE,

S.FIRST_USER,

S.IND_CHANGE,

S.INT_VERSION,

X.NEW_ID,

S.LAST_DATE,

S.LAST_USER,

S.TXT,

S.TXT_ORD

from SNP_EXP_TXT S,

XXX_EXP_TXT X

where X.I_TXT = S.I_TXT

truncate table SNP_EXP_TXT

insert into SNP_EXP_TXT

select * from YYY_EXP_TXT

... execution related information:

/* SNP_SESS_TASK_LOG: Error messages and warnings related to Session Tasks */

update S

set S.I_TXT_TASK_MESS = X.NEW_ID

from SNP_SESS_TASK_LOG S,

XXX_EXP_TXT X

where S.I_TXT_TASK_MESS = X.I_TXT

and S.I_TXT_TASK_MESS is not null

/* SNP_STEP_LOG: Error messages and warnings related to Session Steps */

update S

set S.I_TXT_STEP_MESS = X.NEW_ID

from SNP_STEP_LOG S,

XXX_EXP_TXT X

where S.I_TXT_STEP_MESS = X.I_TXT

and S.I_TXT_STEP_MESS is not null

/* SNP_VAR_SESS (1): Values of Variables used during executions */

update S

set S.I_TXT_VAR = X.NEW_ID

from SNP_VAR_SESS S,

XXX_EXP_TXT X

where S.I_TXT_VAR = X.I_TXT

and S.I_TXT_VAR is not null

/* SNP_VAR_SESS (2): Default values of Variables used during executions */

update S

set S.I_TXT_DEF_T = X.NEW_ID

from SNP_VAR_SESS S,

XXX_EXP_TXT X

where S.I_TXT_DEF_T = X.I_TXT

and S.I_TXT_DEF_T is not null

/* SNP_SESSION (1): Session Parameters and Keywords */

update S

set S.I_TXT_SESS_PARAMS = X.NEW_ID

from SNP_SESSION S,

XXX_EXP_TXT X

where S.I_TXT_SESS_PARAMS = X.I_TXT

and S.I_TXT_SESS_PARAMS is not null

/* SNP_SESSION (2): Error messages and warnings related to the Session */

update S

set S.I_TXT_SESS_MESS = X.NEW_ID

from SNP_SESSION S,

XXX_EXP_TXT X

where S.I_TXT_SESS_MESS = X.I_TXT

and S.I_TXT_SESS_MESS is not null

/* SNP_STEP_REPORT: Error messages and warnings related to Scenario Report Steps */

update S

set S.I_TXT_STEP_MESS = X.NEW_ID

from SNP_STEP_REPORT S,

XXX_EXP_TXT X

where S.I_TXT_STEP_MESS = X.I_TXT

and S.I_TXT_STEP_MESS is not null

/* SNP_SCEN_REPORT: Error messages and warnings related to Scenario Reports */

update S

set S.I_TXT_SESS_MESS = X.NEW_ID

from SNP_SCEN_REPORT S,

XXX_EXP_TXT X

where S.I_TXT_SESS_MESS = X.I_TXT

and S.I_TXT_SESS_MESS is not null

... Scenario related information:

/* SNP_SCEN: Scenario description */

update S

set S.I_TXT_SCEN = X.NEW_ID

from SNP_SCEN S,

XXX_EXP_TXT X

where S.I_TXT_SCEN = X.I_TXT

and S.I_TXT_SCEN is not null

/* SNP_SCEN_FOLDER: Scenario Folder descriptions */

update S

set S.I_TXT_DESCRIPTION = X.NEW_ID

from SNP_SCEN_FOLDER S,

XXX_EXP_TXT X

where S.I_TXT_DESCRIPTION = X.I_TXT

and S.I_TXT_DESCRIPTION is not null

... Scenario related and Text Type Variables:

/* SNP_VAR_SCEN (1): Values of Variables used during Scenario executions */

update S

set S.I_TXT_VAR = X.NEW_ID

from SNP_VAR_SCEN S,

XXX_EXP_TXT X

where S.I_TXT_VAR = X.I_TXT

and S.I_TXT_VAR is not null

/* SNP_VAR_SCEN (2): Default values of Variables used during Scenario executions */

update S

set S.I_TXT_DEF_T = X.NEW_ID

from SNP_VAR_SCEN S,

XXX_EXP_TXT X

where S.I_TXT_DEF_T = X.I_TXT

and S.I_TXT_DEF_T is not null

/* SNP_VAR_DATA: Values of Text Type Variables */

update S

set S.I_TXT_VAR_T = X.NEW_ID

from SNP_VAR_DATA S,

XXX_EXP_TXT X

where S.I_TXT_VAR_T = X.I_TXT

and S.I_TXT_VAR_T is not null

Step 6. Set the next available value for SNP_EXP_TXT identifier.

Synchronize the SNP_ID table with the current largest SNP_EXP_TXT.I_TXT value:

•on Oracle, Microsoft SQLServer, IBM DB2/400 and IBM DB2 UDB:

update SNP_ID

set ID_NEXT = (select max(round(NEW_ID/1000,0)) + 1 from XXX_EXP_TXT)

where ID_TBL = 'SNP_EXP_TXT'

Step 7. Apply changes.

Commit the transactions.

commit

Step 8. Remove temporary objects.

•on Oracle and IBM DB2/400:

drop table XXX_EXP_TXT

•on Microsoft SQLServer:

drop table XXX_EXP_TXT

drop table YYY_EXP_TXT

•on IBM DB2 UDB:

drop table XXX_EXP_TXT

drop table YYY_EXP_TXT /* if step 6ii has been applied */

drop sequence XXX_EXP_TXT_SEQ