update emp_id column with 1 billion records

Only 2 steps:

ALTER TABLE emp DROP COLUMN emp_id;
ALTER TABLE emp ADD (emp_id NUMBER GENERATED ALWAYS AS IDENTITY);

db<>fiddle here


Again, 2 steps:

CREATE SEQUENCE emp__emp_id__seq;

UPDATE emp
SET emp_id = emp__emp_id__seq.NEXTVAL;

db<>fiddle here


One step:

If you have overwritten the column data then either ROLLBACK the last transaction or restore the data from backups.


The emp_id column is totally null and I have to fill it with unique values.

If you want to do it one-time-only, then just one step would do:

update emp set emp_id = rownum;

and that column will have unique values. You don't need PL/SQL (but be patient as 1 billion rows is quite a lot, it'll take time).


If you want to automatically populate it in the future, then it depends on database version you use. Before 12c, you'll have to use a sequence and a database trigger. In later versions, you can still use the same (sequence + trigger) or - as MT0 showed - identity column.