IDENTITY column and Copy tables

[Andrzejek]

We have 2 environments: Test and Production. From time to time, after we mess the data in Test, we copy all tables from Production to Test. (By ‘we’ I mean out DBA.) He had to also re-set all sequences because – for some reason – you cannot copy sequences (or can you?).

But now instead of a Trigger and a Sequence (to keep track of Primary Key in a table) we can use an IDENTITY field, where the next value of the PK is a part of the script. Something like:[tt]
ID NUMBER(38) GENERATED ALWAYS AS IDENTITY ( START WITH [blue]18[/blue] MAXVALUE 9999999999999999999999999999 MINVALUE 1 CYCLE NOCACHE ORDER KEEP) NOT NULL,[/tt]
So the next record will have a PK value of [blue]18[/blue]

How does that work if we want to Copy Production tables to Test?
Tables used to be Truncated and re-populated with the new data. But now, do we need to Drop tables and re-create them again because of the IDENTITY field?



---- Andy

There is a great need for a sarcasm font.

 

[Beilstwh]

Use your own manually generated sequence. Store the next available value in a small table and write a stored procedure or trigger to get the next available number and increment the control table. Do not rely on the Oracle sequence. "

Really bad idea, unless you lock the entire table you will have duplicates, and will kill performance. Since identity columns behind the scene are simply the good old sequence and before insert triggers, don't use identity column and use the old sequence and trigger. You can then simply either rebuild the sequence or set it back to where you want it to be. For example the following code will reset a sequence to 1.

1. Get the next value for the sequence:
select [sequencename].nextval from DUAL;
This should return a numeric value, such as 160.

2. Alter the sequence by incrementing the value by the negative "current value":

alter sequence [sequencename] increment by –[value returned above] minvalue 0;

For example, if the current value returned was 160, then:

alter sequence [sequencename] increment by -160 minvalue 0 ;

3. Get the next value again, which should return the value of 0

select [sequencename].nextval from DUAL;

4. Set the sequence to increment by 1 again:

alter sequence [sequencename] increment by 1 minvalue 0 ;

5. Get the next value, should return 1;

select [sequencename].nextval from DUAL;



Bill
Lead Application Developer
New York State, USA

 

[Andrzejek]

The issue with sequences is – let’s say we have 2 tables: TableA and TableB
In Production, TableA has 100 records, so its sequesnce’s next value is 101, and let’s say the same is for TableB
In Test, TableA has 50 records (sequence points to the next value of 51), and TableB has 200 records (with next value of 201)
When the two tables are copied from Production to Test, now in Test TableA has 100 records (but its sequence points to next value of 51, so when a new record is inserted, crash!). TableB has 100 records and its sequence next value is 201 (which will not crash with the new record inserted, but still ‘next value’ is not really the next value, which should be 101, not 201)
So, either the sequences need to be copied as well (again, can you copy a sequence?) or all sequences need to be re-set based on the last value in the corresponding table.

Two tables are not a big deal, but when 200 tables are being copied, that’s a lot of work.



---- Andy

There is a great need for a sarcasm font.

 

[Beilstwh]

Actually it is easy to do with a script. Setup a "refresh" table with the name of the sequenc, name of the table, and the column containing the sequence values.

In your script get the maximum value in the sequence column, use execute immediate to reset the referenced sequence to the next value (see my comment above) and then go to the next row in the "refresh" table. If you get a new table with new data simply add it to the "refresh" table.

Bill
Lead Application Developer
New York State, USA