e.g.
steps;
lock table -> add new column for primary key -> update data with counter -> define sequence -> define trigger -> lastly, define primary key -> recompile invalid objects (optional)
SQL>
LOCK TABLE HR.EMPLOYEES_NEW IN EXCLUSIVE MODE NOWAIT
/
ALTER TABLE HR.EMPLOYEES_NEW
ADD (ID NUMBER)
/
DECLARE
str_exec VARCHAR2 (250);
counter NUMBER := 0;
last_seq_id NUMBER;
BEGIN
FOR cur_node IN ( SELECT ROWID, id
FROM HR.EMPLOYEES_NEW
ORDER BY HIRE_DATE ASC)
LOOP
BEGIN
str_exec :=
'update HR.EMPLOYEES_NEW set id = '
|| counter
|| ' where rowid = '
|| ''''
|| cur_node.ROWID
|| '''';
--DBMS_OUTPUT.put_line (str_exec);
EXECUTE IMMEDIATE str_exec;
COMMIT;
counter := counter + 1;
END;
END LOOP;
SELECT ID
INTO last_seq_id
FROM ( SELECT ID
FROM HR.EMPLOYEES_NEW
WHERE ID IS NOT NULL
ORDER BY ID DESC)
WHERE ROWNUM < 2;
str_exec :=
'CREATE SEQUENCE HR.EMPLOYEES_NEW_ID_SEQ START WITH '
|| last_seq_id
|| ' MAXVALUE 999999999999999999999999999999 MINVALUE '
|| last_seq_id
|| ' NOCYCLE NOCACHE NOORDER';
--DBMS_OUTPUT.put_line (str_exec);
EXECUTE IMMEDIATE str_exec;
END;
/
CREATE OR REPLACE TRIGGER HR.TRG_EMPLOYEES_NEW_ID
BEFORE INSERT
ON HR.EMPLOYEES_NEW
FOR EACH ROW
BEGIN
IF :new.ID IS NULL
THEN
:new.ID := HR.EMPLOYEES_NEW_ID_SEQ.NEXTVAL;
END IF;
END TRG_EMPLOYEES_NEW_ID;
/
ALTER TABLE HR.EMPLOYEES_NEW ADD
CONSTRAINT EMPLOYEES_NEW_PK
PRIMARY KEY (ID)
ENABLE
VALIDATE
/
EXEC SYS.UTL_RECOMP.recomp_parallel(4)
/