Problem in inserting large data

1. Make sure your base query is indexed well.
2. use the APPEND hint (http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76992/hints.htm#19039)

OK, so how did you get your code to indent on this Forum?

Thanks pwoneill
I ran the following , and still the sql plus hangs.

declare
cursor cur is select col1, col5, col3
from tableB b, tableC
c where b.col4=c.col4;
i NUMBER;
begin
i := 0;
for rec in cur loop
insert into tableA values (rec.col1, rec.col5, rec.col3);
if ( mod(1 ,1000) = 0 ) then
commit;
end if;
i := i+1;
end loop;
commit;
end;

Should I try to assign a bigger rbs?
Presently I have 4 rbs of 4mb each.
Thanks
manjunath

What do you mean by "SQL*Plus hangs"? If it returns an error then let us know what it is. If it simply does not return control that probably mean it is still working. Inserting large dataset might take a lot of time, maybe you simply are not patient enough.

Connect with another Sql*plus session and select count(*) from tableA. If you realy are commiting periodically then you should be able to find this number increasing in batches of 100.

I checked up in task manager. And it says sql plus is not responding.
Thanks
manjunath

But this does not mean the insertion is not taking place! Sql*Plus passes comand (either a query or a PL/SQL block to Oracle Server and waits there to get the control back. But the database will not return control to it until the whole comand is executed. In your case SQL*Plus will probably respond only after all the rows are inserted into TableA.

not respondoing doesnt mean it hangs, do you know how to query v$session_wait? if you know find the sid of the user inserting from v$session and then

select event from v$session_wait
where sid=sid_of_user_inserting

This will show what is going on in that session

Spacing with judicious use of nbsp :)

As already pointed out just because it's hanging doesn't mean it's not doing anything. How many rows are being inserted?


select count(1) from tableB b, tableC c where b.col4 = c.col4;
;

Did you check your alert logs to see if it's actually an RBS extending problem?

Thanks friends,
My mistake in assuming that it was hanging. Indeed it was not.
I got the following error.


ORA-01562: failed to extend rollback segment number 1
ORA-01628: max # extents (200) reached for rollback segment R01
ORA-01562: failed to extend rollback segment number 1
ORA-01628: max # extents (200) reached for rollback segment R01
ORA-06512: at line 13


I guess now I need to create a large rbs and assign to that. Is that OK?
Thanks
manjunath

You could create a larger rollback segment, or at least shrink or set the optimal of the one that grew too big so it's not full anymore. The pl/sql I gave, assuming it's correct, should save you that problem because it commits every 1000 records. In the forum the code looks like 100 0 for some reason, but it should actually be a 1000 (one thousand). You could increase this or decrease it, decreasing if you still get the RBS problem.

When you query v$session, it shows the "Status".
When the status is INACTIVE, does it mean that nothing is happening or is it the same hung up situation ?

Thanks

For fast inserting rows in Sqlplus, consider the following:
1 Set NOLOGGING to the table before insert
2 Create large extents for the table
3 Set a particular rollback segment (large) for this transaction
4 Ensure that indexes are created on joining columns
5 Set auto commit to 5000
6 Disable indexes on the table that gets inserted new rows
6 Run the insert statement
7 Enable (recreate) the indexes on the inserted table

Can someone answer my question please.....
When you query v$session, it shows the "Status".
When the status is INACTIVE, does it mean that nothing is happening or is it the same hung up situation ?

It is not the "hung-up situation", it is just the session is *currently* not doing anything.

SQLPLUSW.exe prior to 8i's JAVA based tools does appear to hang and will also appear to lock your Windows box up. I suggest either running large jobs like this from sqlplus in UNIX or on Windows using plus80.exe from a command prompt.

Things you will check to make sure the inserting is happening (basing this on the fact you are following the LOOP example above)
-- Alert Log (shows redo/archive log switching frequent switching indicates inserts are occuring)
-- select * from v$session_wait;
-- Select row count from the other table
-- Also to speed things up drop indexes and constraints on this table and then recreate them when finished.

Start another SQL*Plus session connected as internal and issue
select * from v$rollstat. You will se on what rollback segment your transaction is settled, how big the rollback is and if you are in danger of exceeding rs dimension. Task manager allways give the message "task not responding" for very long trasactions, so don't worry about it.

Thanks everybody,
I got my problem to work in one step less and using the Loader with the following:


create or replace trigger test_trigger before insert on table_a for each row
begin
select e into :new.b from table_b where d = :new.d;
end;
/


load data
infile *
append
into table system.table_a
FIELDS TERMINATED BY ","
(
a,
c,
d
)
begindata
...
...
...
...

One question though. How do I assign a specific rooback segment when executing the sql*loader?

Thanks once again
manjunath