Creating duplicate data .....
SQL> create table emp_dup as select * from emp;
Table created.
SQL> insert into emp_dup select * from emp;
14 rows created.
SQL> /
14 rows created.
SQL> insert into emp_dup select * from emp_dup;
42 rows created.
SQL> commit;
Commit complete.
Total no of rows...
SQL> select count(*) from emp_dup;
COUNT(*)
----------
84
These are the unique row
SQL> select * from (SELECT rowid,empno,deptno, ename, hiredate, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) AS emp_id
FROM emp_dup )
where emp_id=1;
ROWID EMPNO DEPTNO ENAME HIREDATE EMP_ID
------------------ ---------- ---------- ---------- --------- ----------
AAAaL6AAEAAABDkAAA 7369 20 SMITH 17-DEC-80 1
AAAaL6AAEAAABDkAAB 7499 30 ALLEN 20-FEB-81 1
AAAaL6AAEAAABDkAAC 7521 30 WARD 22-FEB-81 1
AAAaL6AAEAAABDkAAD 7566 20 JONES 02-APR-81 1
AAAaL6AAEAAABDkAAE 7654 30 MARTIN 28-SEP-81 1
AAAaL6AAEAAABDkAAF 7698 30 BLAKE 01-MAY-81 1
AAAaL6AAEAAABDkAAG 7782 10 CLARK 09-JUN-81 1
AAAaL6AAEAAABDkAAH 7788 20 SCOTT 19-APR-87 1
AAAaL6AAEAAABDkAAI 7839 10 KING 17-NOV-81 1
AAAaL6AAEAAABDkAAJ 7844 30 TURNER 08-SEP-81 1
AAAaL6AAEAAABDkAAK 7876 20 ADAMS 23-MAY-87 1
AAAaL6AAEAAABDkAAL 7900 30 JAMES 03-DEC-81 1
AAAaL6AAEAAABDkAAM 7902 20 FORD 03-DEC-81 1
AAAaL6AAEAAABDkAAN 7934 10 MILLER 23-JAN-82 1
14 rows selected.
SQL> select rowid from (SELECT rowid,empno,deptno, ename, hiredate, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) AS emp_id
FROM emp_dup )
where emp_id=1;
ROWID
------------------
AAAaL6AAEAAABDkAAA
AAAaL6AAEAAABDkAAB
AAAaL6AAEAAABDkAAC
AAAaL6AAEAAABDkAAD
AAAaL6AAEAAABDkAAE
AAAaL6AAEAAABDkAAF
AAAaL6AAEAAABDkAAG
AAAaL6AAEAAABDkAAH
AAAaL6AAEAAABDkAAI
AAAaL6AAEAAABDkAAJ
AAAaL6AAEAAABDkAAK
AAAaL6AAEAAABDkAAL
AAAaL6AAEAAABDkAAM
AAAaL6AAEAAABDkAAN
14 rows selected.
SQL> delete from emp_dup
where rowid in (select rowid from (SELECT rowid,empno,deptno, ename, hiredate, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) AS emp_id
FROM emp_dup )
where emp_id!=1)
/
70 rows deleted.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select * from emp_dup;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7900 JAMES CLERK 7698 03-DEC-81 950 30
14 rows selected.
=======================================
>>>>>>>>>>>>>>>>>>>>>>>>>>> Important
=======================================
How to perform long deletes ( commiting every n number of records)
When performing a DELETE from a table, Oracle will generate rollback. If the particular table you are trying to delete from contains a large number of records, it is possible for the transaction to fail because of the rollback segment not being large enough
Solution would be to use the PL/SQL block below.
set serveroutout on
DECLARE
cnt NUMBER := 0;
total NUMBER := 0;
BEGIN
FOR rec IN (select rowid from (SELECT rowid,empno,deptno, ename, hiredate,
ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) AS emp_id
FROM emp_dup )
where emp_id!=1)
LOOP
DELETE FROM emp_dup
WHERE rowid = rec.rowid;
total := total + 1;
cnt := cnt + 1;
IF (cnt >= 5000) THEN
COMMIT;
cnt := 0;
END IF;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from .');
END;
