CREATE TABLE TABLE1(FOREIGN_ID VARCHAR(5), VV VARCHAR(10));

INSERT INTO TABLE1(FOREIGN_ID, VV) VALUES ('I1', 'XXXXX');
INSERT INTO TABLE1(FOREIGN_ID, VV) VALUES ('I2', 'YYYYY');
INSERT INTO TABLE1(FOREIGN_ID, VV) VALUES ('I2', 'ZZZZZ');
INSERT INTO TABLE1(FOREIGN_ID, VV) VALUES ('I2', 'SSSSS');
INSERT INTO TABLE1(FOREIGN_ID, VV) VALUES ('I2', 'SSSSS');
INSERT INTO TABLE1(FOREIGN_ID, VV) VALUES ('I1', 'TTTTT');

FOREIGN_ID  VV
----- ---------
I1   XXXXX
I2   YYYYY
I2   ZZZZZ
I2   SSSSS
I2   SSSSS
I1   TTTTT

ALTER TABLE TABLE1 ADD SEQ_NUMBER NUMBER(5) DEFAULT 0 NOT NULL;

ALTER TABLE TABLE1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY (FOREIGN_ID, SEQ_NUMBER);

FOREIGN_ID  VV  SEQ_NUMBER
----- --------- -----
I1   XXXXX     0
I2   YYYYY     0
I2   ZZZZZ     1
I2   SSSSS     2
I2   SSSSS     3
I1   TTTTT     1

select t.*,
       row_number() over (partition by t.foreign_id order by t.vv) - 1 seq_number
from TABLE1 t;

FOREIGN_ID  VV  SEQ
I1  TTTTT   0
I1  XXXXX   1
I2  SSSSS   0
I2  SSSSS   1
I2  YYYYY   2
I2  ZZZZZ   3

update TABLE1 t
set t.seq_number = row_number() over (partition by t.foreign_id order by t.vv)-1
;

create table TABLE2 as
select t.foreign_id,
       t.vv,
       row_number() over (partition by t.foreign_id order by t.vv) - 1 seq_number
from TABLE1 t;

ALTER TABLE TABLE2 ADD CONSTRAINT TABLE2_PK PRIMARY KEY (FOREIGN_ID, SEQ_NUMBER);