×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

IDENTITY column and Copy tables

IDENTITY column and Copy tables

IDENTITY column and Copy tables

(OP)
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:
ID NUMBER(38) GENERATED ALWAYS AS IDENTITY ( START WITH 18 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CYCLE NOCACHE ORDER KEEP) NOT NULL,

So the next record will have a PK value of 18

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.

RE: IDENTITY column and Copy tables

"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

RE: IDENTITY column and Copy tables

(OP)
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.

RE: IDENTITY column and Copy tables

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close