Inserting records from one table into another

[anandkl]

Hi Friends,
I have a table which is 350MB and another table which is 4GB.Now i want to insert all the records from small table into the large table.Both tables have same table structure.Can any one suggest the most fasted method of doing this.I have written a procedure, but it errors out with "Unable to allocate rollback segment" even specifying a big rollback segment.
I am using oracle 8.1.7.4 on solaris 2.8

regards
anandkl

[stmontgo]

why don't you post the proc

steve

[anandkl]

create or replace procedure INSERT_SNAPSHOTS
(
p_error_code OUT NUMBER,
p_error_text OUT VARCHAR2
)
IS

begin
begin
insert into SNAPSHOT1 select * from SNAPSHOT_TEMP;
insert into STER_SNAPSHOT select * from STER_SNAPSHOT_TEMP;

COMMIT;

p_error_code := 0;
p_error_text := '';

exception
when DUP_VAL_ON_INDEX then

delete from SNAPSHOT1 where C_ID in (select C_ID from SNAPSHOT_TEMP);
delete from STER_SNAPSHOT where C_ID in (select C_ID from STER_SNAPSHOT_TEMP);

insert into SNAPSHOT1 select * from SNAPSHOT_TEMP;
insert into STER_SNAPSHOT select * from STER_SNAPSHOT_TEMP;

COMMIT;

p_error_code := 0;
p_error_text := '';
end;

exception
when OTHERS then
p_error_code := 1;
p_error_text := 'Error in inserting into snapshot tables ' || SQLERRM;
ROLLBACK;
RETURN;

end INSERT_SNAPSHOTS;
/
show errors

[ganga]

Hi ,
This code sniffet may help you
Use commit between inserts when you do bulk insert;
For example commit insert for every 1000 row insert


declare
x number =0;
a number a.eno%type;
b number b.ename%type;
... ...;
... ...;
... ...;
n .... ...;
begin
cursor c1 is select * from table1;
open c1;
loop
fetch c1 into a,b,.......n;
insert into tableb values(a,b,.........);
x=x+1;
if (x=1000)then
commit;
x=0;
end if;
end loop;
end;
/


Tks
Gangadhara

[anandkl]

Thanks Ganga,
Thanks very much

regards
anandkl

[slimdave]

Originally posted by ganga
Hi ,
This code sniffet may help you
Use commit between inserts when you do bulk insert;
For example commit insert for every 1000 row insert


declare
x number =0;
a number a.eno%type;
b number b.ename%type;
... ...;
... ...;
... ...;
n .... ...;
begin
cursor c1 is select * from table1;
open c1;
loop
fetch c1 into a,b,.......n;
insert into tableb values(a,b,.........);
x=x+1;
if (x=1000)then
commit;
x=0;
end if;
end loop;
end;
/


Tks
Gangadhara

This is a great way to get a snapshot too old error -- commiting within a cursor like this is very bad practice.

How about setting the target table to nologging, and using "insert /*+ append */ into ..."? That'd help, i would think.

You might also disable any nidexes on the target table, set skip_unusable_indexes=true, perform your insert, then rebuild the indexes.

[DaPi]

Originally posted by slimdave
This is a great way to get a snapshot too old error -- commiting within a cursor like this is very bad practice.

I would have thought it should be OK in this case since the table being updated is not in the cursor. Have I missed something?

Mind you, you will have a problem sorting out how much has been inserted and commited if it crashes in the middle . . .

[slimdave]

Originally posted by DaPi
I would have thought it should be OK in this case since the table being updated is not in the cursor. Have I missed something?

Mind you, you will have a problem sorting out how much has been inserted and commited if it crashes in the middle . . .

Always a difficult point, this, but i don't think it makes any difference -- if the SCN taken as the consistency point for the cursor is not available in the rollback, because committing the changes to the target table allowed the rollback to be overwritten, then a snapshot error would result. Different databases for source and target,no problem -- same database, problem.

[ganga]

Hi slimdave,
in the cursor if we use commit after 1000 row insertion, what is the problem, let rollback seg overwritten, anandkl intension is to avoid larger rollback segment requirement,

What i know is select statement doesnot need any rollback segs let transaction use rollback segement only for 1000 row insertion and may overwrite.

I agree your suggestions like setting nologging, using query hints, disableing constraints but we should be cautious to enable all paramters which are disabled at the time bulk data loading

Could U Plz explain me Y ora 1555 errors may come if we use commits inside cursor

Tks
Gangu

[slimdave]

Tome Kyte has good examplesat http://asktom.oracle.com, including this one