SQL> DROP TABLE emp;

Table dropped.

SQL> DROP TABLE emp_test;

Table dropped.

SQL> CREATE TABLE emp(
  2    ename  VARCHAR(10),
  3    job    VARCHAR(9),
  4    mgr    INT,
  5    sal    NUMERIC(7, 2),
  6    deptno INT
  7  )
  8  TABLESPACE warehouse_big_data;

Table created.

SQL> CREATE TABLE emp_test(
  2    empno  INT CONSTRAINT emp_uk UNIQUE NOT NULL,
  3    ename  VARCHAR(10),
  4    job    VARCHAR(9),
  5    mgr    INT,
  6    sal    NUMERIC(7, 2),
  7    deptno INT
  8  )
  9  TABLESPACE warehouse_big_data;

Table created.

SQL> DROP SEQUENCE seq_empno;

Sequence dropped.

SQL> CREATE SEQUENCE seq_empno
  2    START WITH 1
  3    MAXVALUE 9999999999999999999999999999
  4    MINVALUE 0
  5    NOCYCLE
  6    CACHE 20
  7    NOORDER;

Sequence created.

SQL> INSERT INTO emp
  2       VALUES (
  3                'TCS',
  4                'AAAA',
  5                7901,
  6                801,
  7                21
  8              );

1 row created.

SQL> INSERT INTO emp
  2       VALUES (
  3               'WIPRO',
  4                'BBBB',
  5                7902,
  6                802,
  7                22
  8              );

1 row created.

SQL> INSERT INTO emp
  2       VALUES (
  3                'INFOSYS',
  4                'CCCC',
  5                7903,
  6                803,
  7                23
  8              );

1 row created.

SQL> commit;

Commit complete.

SQL> INSERT INTO emp_test(
  2                empno,
  3                ename,
  4                job,
  5                mgr,
  6                sal,
  7                deptno
  8              )
  9    SELECT seq_empno.NEXTVAL,
 10           ename,
 11           job,
 12           mgr,
 13           sal,
 14           deptno
 15      FROM emp
 16  /

3 rows created.

SQL> commit;

Commit complete.

SQL> select * from emp_test
  2  /

     EMPNO ENAME      JOB              MGR        SAL     DEPTNO                
---------- ---------- --------- ---------- ---------- ----------                
         1 TCS        AAAA            7901        801         21                
         2 WIPRO      BBBB            7902        802         22                
         3 INFOSYS    CCCC            7903        803         23                

SQL> update emp_test set empno=5 where empno=3
  2  /

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR        SAL     DEPTNO                
---------- ---------- --------- ---------- ---------- ----------                
         1 TCS        AAAA            7901        801         21                
         2 WIPRO      BBBB            7902        802         22                
         5 INFOSYS    CCCC            7903        803         23                

SQL> INSERT INTO emp
  2       VALUES (
  3               'Test',
  4                'MMMM',
  5                7906,
  6                806,
  7                28
  8              );

1 row created.

SQL> INSERT INTO emp
  2       VALUES (
  3               'common',
  4                'zz',
  5                7907,
  6                808,
  7                29
  8              );

1 row created.

commit;

SQL> INSERT INTO emp_test(
  2                empno,
  3                ename,
  4                job,
  5                mgr,
  6                sal,
  7                deptno
  8              )
  9    SELECT seq_empno.NEXTVAL,
 10           ename,
 11           job,
 12           mgr,
 13           sal,
 14           deptno
 15      FROM emp
 16  /
INSERT INTO emp_test(
*
ERROR at line 1:
ORA-00001: unique constraint (CRIS_WAREHOUSE_USER.EMP_UK) violated