Useful shortcuts for vi editor

Friday 19 January 2018

How to define primary key on existed table?

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)
/