Aug 9, 2019

Restore multi-million rows from existing table via pl/sql block

There is request to copy multi million rows from one archive table to original table with downtime.
I tried with export and import to copy fast but not succeeded even after many hours.

So I tried with below approach and it completed fast. This pl/sql block copy 10 thousand rows at a time and commit. So that we can avoid huge rollback segment and huge switch logs etc.

-- Here is the Pl/SQL block

declare 
cursor C1 is
select id,comment_text,comment_by,comment_date,cell_number,language
from scott.big_table_archive;
begin
for r1 in C1
loop
insert into scott.big_table values (
r1.id,
r1.comment_text,
r1.comment_by,
r1.comment_date,
r1.cell_number,
r1.language);
if (c1%rowcount mod 10000=0) then
commit;
end if;
end loop;
commit;
end;
/

-- if you want to add existing records again to original table, then you need to use merge command. If any PK constraints are there and updated records are there, then below pl/sql block will help you:

MERGE INTO scott.big_table a
USIING 
   (SELECT * FROM scott.big_table_archive)  b
ON (b.id=a._id and b.comment_text=a.comment_text)  /* PK */
WHEN MATCHED THEN
UPDATE
    SET a.comment_by=b.comment_by,
        a.comment_date=b.comment_date,
        a.Tcell_number=b.cell_number,
        a.language=b.language
WHEN NOT MATCHED THEN
INSERT  
    VALUES 
       (r1.ID,
        b.comment_text,
        b.comment_by,
        b.comment_date,
        b.cell_number,
        b.language
 );



Try this and post your feedback.



No comments:

Post a Comment

Translate >>