Considerations when deleting millions of useless transactions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Considerations when deleting millions of useless transactions

  1. #1
    Join Date
    Feb 2001
    Posts
    83

    Question

    We have a multimaster environment, where master definition site is in Buffalo, and two master sites are in Hongkong and france. Now the france has been disconnected, but still the deferred transactions are produced for france. So we run a oracle supplied package to delete these transactions in Buffalo. Unfortunately for long time they didnít do that in france and now it has gone over 6 million records. Now I need to delete that. The size of the table approximately may be 4GB . Now to delete the records, how should I configure rollback segments and what are the other considerations I should take care of.

    My next doubt is how to stop the production of the deferred transactions to france.

    Thanks & Regards
    Prasanna S.
    with regards
    Prasanna S

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    you should use PL/SQL and commit often (let's say every 20k to 50k lines)
    you should assign the transaction to your biggest RBS
    if you have a numeric key (some id for example), you could delete everything that finishes with a 1, then with a 2, etc ... so you approximately delete 10% of the lines each time, and you're less subject to having problems with RBS

  3. #3
    Join Date
    Feb 2001
    Posts
    83
    Hello Pipo,

    I can't do set by set operation as the data is in such a way. If i give commit in between after assigning a rollback segment to a transaction, i doubt that, the transaction is not set anymore to that particular rollback segment.

    with regards
    Prasanna S

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    if you do :

    declare
    n number := 0;
    cursor curs is
    select rowid from table;
    begin
    set transaction use rollback segment RBS1;
    for v_curs in curs
    loop
    delete table where rowid = v_curs.rowid;
    n:=n+1;
    if mod(n,20000) = 0
    then
    commit;
    set transaction use rollback segment RBS1;
    end if;
    end loop;
    end;
    /

    it should work, no ???

  5. #5
    Join Date
    Feb 2001
    Posts
    83
    Thanks for your reply pipo, but i am going to delete the deffered transactions in system tables which is related to replication and i am using oracle supplied package called

    DBMS_DEFER_SYS and method is DELETE_TRAN. I can't directly delete the data in the system tables, as it has lots of rist factor.
    with regards
    Prasanna S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width