Różności …

ALTER JAVA CLASS ODM.”/10b943ec_CompoundPredicate” resolve;

Kasowanie dużej ilości wierszy z tabeli:

– Create a test table
CREATE TABLE test_tbl
(test_col INTEGER)
/
– Populate it with 100,000 rows
BEGIN
FOR i in 1..100000 LOOP
INSERT INTO test_tbl
VALUES (i – TRUNC(i, -1));
END LOOP;
END;
/
COMMIT
/
– Delete 60% of the rows. This is the actual delete. You would just replace the
– where clause with yours.
DECLARE
TYPE tt_delete IS TABLE OF ROWID;
t_delete tt_delete;
CURSOR c_delete IS
SELECT ROWID
FROM test_tbl
WHERE test_col < 6;
l_delete_buffer PLS_INTEGER := 5000;
BEGIN
OPEN c_delete;
LOOP
FETCH c_delete BULK COLLECT
INTO t_delete LIMIT l_delete_buffer;
FORALL i IN 1..t_delete.COUNT
DELETE test_tbl
WHERE ROWID = t_delete (i);
EXIT WHEN c_delete%NOTFOUND;
COMMIT;
END LOOP;
CLOSE c_delete;
END;
/

(źródło: http://forums.oracle.com/forums/thread.jspa?threadID=345861)

i trochę inne podejście:

CREATE TABLE test_tbl_temp AS
SELECT *
  FROM TEST_TBL
 WHERE
MONTHS_BETWEEN(SYSDATE, TO_DATE(YRMO_NBR||'01','YYYYMMDD')) <= 38;

TRUNCATE TABLE test_tbl;
lub drop test_tbl; 

INSERT /*+ APPEND +/ INTO test_tbl
SELECT *
  FROM test_tbl_temp;

lub

RENAME TABLE test_tbl_temp to test_tbl;

ale tutaj trzeba uważać na ORA=02449 (unique/primary keys in table referenced by foreign keys)
aby sprawdzić jakie constraints odwołują się do tabeli można:
SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = “test_tabl”;

lub

DELETE FROM myfata$ WHERE rownum < 100000; –Start with a small number and gradually increase to see how many rows you can delete in one shot without hitting the UNDO problem.

lub

CREATE OR REPLACE PROCEDURE delete_tab (tablename IN VARCHAR2,
                                            empno IN NUMBER  ,
                                            nrows IN NUMBER    ) IS

 sSQL1    VARCHAR2(2000);
 sSQL2    VARCHAR2(2000);
 nCount    NUMBER; 

BEGIN

  nCount := 0;
  sSQL1:='delete from '|| tablename ||
         ' where ROWNUM < ' || nrows || ' and empno=' || empno;
  sSQL2:='select count(ROWID) from ' || tablename ||
         ' where empno= ' || empno;

  LOOP

    EXECUTE IMMEDIATE sSQL1;

    EXECUTE IMMEDIATE sSQL2 INTO nCount;

    DBMS_OUTPUT.PUT_LINE('Existing records: ' || to_char(ncount) );

    commit;    

    EXIT WHEN nCount = 0;

  END LOOP;

END delete_tab;
/

Wyłączenie wyzwalacza:
ALTER TRIGGER trigger_name DISABLE;

ALTER TABLE inventory
    ENABLE ALL TRIGGERS;