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

    Table locked due to roll back

    user13364377


      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

          Bounce the database - If database is not production

          • 2. Re: Table locked due to roll back
            David Berger

            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

              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

                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

                  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 .

                    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