最近两天，开发要求，清理掉某些表的部分数据，因为不能停业务，不能采用cast+rman方式实现.只能自己写脚本删除，在这些删除数据中，经过总结，共有三种类型:
1.删除单个表数据
删除a表以dealdate为范围的部分数据
DECLARE
P_SQL VARCHAR2(300) :=
'DELETE FROM a WHERE dealdate<to_date('||''''||
'2010-11-01 00:00:00'||''''||','||''''||'yyyy-mm-dd hh24:mi:ss'||''''||')';
P_COUNT NUMBER := 1000;
BEGIN
WHILE 1 = 1 LOOP
EXECUTE IMMEDIATE P_SQL || ' and rownum <= :nu'
USING P_COUNT;
IF SQL%NOTFOUND THEN
EXIT;
END IF;
COMMIT;
END LOOP;
COMMIT;
END;
2.删除两个关联表数据
tab_a,tab_b两个表通过共有的PRE_ID列关联，然后按照tab_a.ACCEPT_TIME列为条件删除两个表中数据,tab_b表中数据比tab_a多很多
DECLARE
CURSOR CUR IS
SELECT B.ROWID BID, A.ROWID AID
FROM tab_a A, tab_b B
WHERE A.PRE_ID = B.PRE_ID
AND A.ACCEPT_TIME <
TO_DATE('2010-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
ORDER BY B.ROWID;
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR ROW IN CUR LOOP
DELETE FROM tab_a WHERE ROWID = ROW.AID;
DELETE FROM tab_b WHERE ROWID = ROW.BID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 1000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;
3.删除某个表中重复列
删除tab_a表中的COMPANY_ID/PY_DES/PY_DES_Q/PY_TYPE/RELATE_ID列重复数据
DECLARE
CURSOR CUR IS
SELECT A.ROWID AID
FROM tab_a A
WHERE ROWID NOT IN (SELECT MAX(B.ROWID)
FROM tab_a B
WHERE A.COMPANY_ID = B.COMPANY_ID
AND A.PY_DES = B.PY_DES
AND A.PY_DES_Q = B.PY_DES_Q
AND A.PY_TYPE = B.PY_TYPE
AND A.RELATE_ID = B.RELATE_ID)
ORDER BY A.ROWID;
V_COUNTER NUMBER;
BEGIN
V_COUNTER := 0;
FOR ROW IN CUR LOOP
DELETE FROM tab_a WHERE ROWID = ROW.AID;
V_COUNTER := V_COUNTER + 1;
IF (V_COUNTER >= 1000) THEN
COMMIT;
V_COUNTER := 0;
END IF;
END LOOP;
COMMIT;
END;