How to create a unique id for an existing table in PL SQL?

OK then, as you're on Oracle 11g, there's no identity column there so - back to multiple steps. Here's an example:

I'm creating a table that simulates your imported table:

SQL> create table tab_import as 2 select ename, job, sal 3 from emp 4 where deptno = 10; Table created. 

Add the ID column:

SQL> alter table tab_import add id number; Table altered. 

Create a sequence which will be used to populate the ID column:

SQL> create sequence seq_imp; Sequence created. 

Update current rows:

SQL> update tab_import set 2 id = seq_imp.nextval; 3 rows updated. 

Create a trigger which will take care about future inserts (if any):

SQL> create or replace trigger trg_bi_imp 2 before insert on tab_import 3 for each row 4 begin 5 :new.id := seq_imp.nextval; 6 end; 7 / Trigger created. 

Check what's in the table at the moment:

SQL> select * from tab_import; ENAME JOB SAL ID ---------- --------- ---------- ---------- CLARK MANAGER 2450 1 KING PRESIDENT 5000 2 MILLER CLERK 1300 3 

Let's import some more rows:

SQL> insert into tab_import (ename, job, sal) 2 select ename, job, sal 3 from emp 4 where deptno = 20; 3 rows created. 

The trigger had silently populated the ID column:

SQL> select * From tab_import; ENAME JOB SAL ID ---------- --------- ---------- ---------- CLARK MANAGER 2450 1 KING PRESIDENT 5000 2 MILLER CLERK 1300 3 SMITH CLERK 800 4 JONES MANAGER 2975 5 FORD ANALYST 3000 6 6 rows selected. SQL> 

Shortly: you need to

• alter table and add the ID column
• create a sequence
• create a trigger

The end.

You can add an id column to a table with a single statement (Oracle 11g, see dbfiddle):

alter table test_ add id raw( 16 ) default sys_guid() ; 

Example:

-- create a table without an id column create table test_ ( str ) as select dbms_random.string( 'x', 16 ) from dual connect by level <= 10 ; select * from test_ ; STR ULWL9EXFG6CIO72Z QOM0W1R9IJ2ZD3DW YQWAP4HZNQ57C2UH EETF2AXD4ZKNIBBF W9SECJYDER793MQW alter table test_ add id raw( 16 ) default sys_guid() ; select * from test_ ; STR ID ULWL9EXFG6CIO72Z 0x782C6EBCAE2D7B9FE050A00A02005D65 QOM0W1R9IJ2ZD3DW 0x782C6EBCAE2E7B9FE050A00A02005D65 YQWAP4HZNQ57C2UH 0x782C6EBCAE2F7B9FE050A00A02005D65 EETF2AXD4ZKNIBBF 0x782C6EBCAE307B9FE050A00A02005D65 W9SECJYDER793MQW 0x782C6EBCAE317B9FE050A00A02005D65 

Testing

-- Are the id values unique and not null? Yes. alter table test_ add constraint pkey_test_ primary key ( id ) ; -- When we insert more rows, will the id be generated? Yes. begin for i in 1 .. 100 loop insert into test_ (str) values ( 'str' || to_char( i ) ) ; end loop ; end ; / select * from test_ order by id desc ; -- last 10 rows of the result STR ID str100 0x782C806E16A5E998E050A00A02005D81 str99 0x782C806E16A4E998E050A00A02005D81 str98 0x782C806E16A3E998E050A00A02005D81 str97 0x782C806E16A2E998E050A00A02005D81 str96 0x782C806E16A1E998E050A00A02005D81 str95 0x782C806E16A0E998E050A00A02005D81 str94 0x782C806E169FE998E050A00A02005D81 str93 0x782C806E169EE998E050A00A02005D81 str92 0x782C806E169DE998E050A00A02005D81 str91 0x782C806E169CE998E050A00A02005D81 

Regarding your other questions:

{1} Then I'd like to know what PL SQL programmer typically do to ID records after importing a file. Do they do that? The main purpose for me to ID these records is to trace it back after manipulation/copying.

-> As you know, the purpose of an id is: to identify a row. We don't "do anything to IDs". Thus, your usage of IDs seems legit.

{2} Again, I understand there is solution there, my further question is whether PL SQL programmer actually do that, or there is other alternative which making this step not necessary in PL SQL?

-> Not quite sure what you are asking here. Although there is a ROWID() pseudocolumn (see documentation), we should not use it to identify rows.

"You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later."