-
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.
-
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
-
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.
-
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 ???
-
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.