Deleting records takes long time and unltimately servers hang

[PAVB]

can we do something like "< trunc(sysdate - 2)'

select trunc(sysdate -2) from dual;

[CoolGuyz]

So you are cascade deleting? I see, you are adding pieces of information one-at-a-time, some people would get crazy about that

What else should we know?

By the way, are you sure your predicate is no like: ... < trunc(sysdate - 2) ?

Can we do something like < trunc(sysdate - 2) ???

[CoolGuyz]

select trunc(sysdate -2) from dual;

How can I change my query using "select trunc(sysdate -2) from dual" ???

[malay_biswal]

You can do it in a pl/sql procedure.
Place the delete statement inside the loop. something like
For i in reverse 3..100 Loop
delete from table where date < sysdate-i;
commit;
end loop;

[davey23uk]

You can do it in a pl/sql procedure.
Place the delete statement inside the loop. something like
For i in reverse 3..100 Loop
delete from table where date < sysdate-i;
commit;
end loop;

welcome to ORA-1555

[gandolf989]

How can I change my query using "select trunc(sysdate -2) from dual" ???

So you are suggesting that it would be faster if you used a constant instead of sysdate-2 or trunc(sysdate-1). How do you know that sysdate-2 isn't a constant? It seems like a constant to me. That is constant as of the time it is being used. I don't see how your suggestion will change anything.

You should go back and create explain plans for every table that gets deleted from when you delete from the main table. You might also try partitioning those tables by day as well. This way you would go around and drop and recreate partitions from all of your tables.

For what you are doing 45 minutes might not be that bad, but dropping a bunch of partitions would be much faster, IMHO.