6 Replies Latest reply: Feb 17, 2014 7:51 AM by Richard Harrison . RSS

Table locked due to roll back

user13364377 Newbie
Currently Being Moderated


Hi All,

 

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.

Is there a better way to release the lock from the table?

 

SQL> select used_ublk,used_urec from v$transaction;

USED_UBLK  USED_UREC
---------- ----------
     45360    1898141
         2        210

 

SQL> select used_ublk,used_urec from v$transaction;

USED_UBLK  USED_UREC
---------- ----------
     45302    1895407

DB Version:11.2.0.2

OS :linux 64 bit

  • 1. Re: Table locked due to roll back
    saratpvv Journeyer
    Currently Being Moderated

    Bounce the database - If database is not production

  • 2. Re: Table locked due to roll back
    David Berger Pro
    Currently Being Moderated

    Hello user13

     

    No.

     

    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!

  • 3. Re: Table locked due to roll back
    user13364377 Newbie
    Currently Being Moderated

    Hi,

     

    This is a production database. Application team has already killed the delete operation and it started to roll back. But still more than 4 lakh undo blocks to be rolled back. Is there any other option?

  • 4. Re: Table locked due to roll back
    Harmandeep Singh Journeyer
    Currently Being Moderated

    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

    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

    required.

     

     

    Thanks,

    Harman

  • 5. Re: Table locked due to roll back
    rp0428 Guru
    Currently Being Moderated

    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.

  • 6. Re: Table locked due to roll back
    Richard Harrison . Expert
    Currently Being Moderated

    Hi,

    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.

     

    Cheers,

    Rich

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points