If you have to delete millions of records then you can consider using a CTAS operation which can be better than deleting millions of records in a table.
-> But of course it depends on your possibilities (DB Privileges) and the original size of the Table, Constraints etc.. there are many factors.
If you have to do a delete in this case you have to count with a long running process. -> If you have an oracle enterprise edition then you can profit from the parallel executions.
DML-Operations are disabled by default.
But you an enable it. "ALTER SESSION FORCE PARALLEL DML"
And you can use a hint in your DELETE-Statement DELETE /*+ PARALLEL(32) */... (The parallel Degree depends on your hardware)
I hope it helps!
As David told, CTAS is best option for this. Perform this operation during off peak hours
1) First create your dummy hold table: create table xyz_HOLD as select * from xyz where rownum<1.
Alter tablexyz nologging.
2) insert /*+ append parallel (xyzhold,12) */ into xyz_hold xyzhold (field1, field2, field3) select
/*+ parallel (x,12) */ xyz.field1, my_new_value_for_field2, xyz.field3 from xyz x where blah blah
3) When done, either rename the table, or swap the partition if your original table is partitioned,
and you only updated one partition as we do. Obviously you need to rebuild indexes, etc as
A delete statement to delete millions of records from a table was in progress and application team killed the process as it was exectuing for.
more than 10 hrs. After killing the process, the table is locked as the roll back is still in progress. For roll back to get completed, it will take another 10 hrs.
Yes - it likely WILL take more time to do the rollback than it took to do the delete to that point. Going backwards and 'undoing' things takes a LOT longer than just doing them.
That has been known for over a decade.
Is there a better way to release the lock from the table?
The BEST way is: DON'T DO THAT! Once a large DML has begun let it finish.
This is a production database. Application team has already killed the delete operation and it started to roll back.
Why wasn't a DBA doing this operation? The App team should NOT have access to production to begin with. In shops I have worked with the DBAs are responsible for production database operations and most DBAs would know better than to try to roll back such a large operation.
Is there any other option?
Not unless you want to restore your database from a backup.
You can maybe look into fast_start_parallel_rollback and let smon do the recovery in parallel (you'll need to restart to get it to pick it up), in theory this should be much faster though to be honest my own experience with it hasn't really shown that. Might be worth a try though.... other people seem happy with it.