Problem in inserting large data

[manjunathk]

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

[pwoneill]

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.

[sonaliak]

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

[tamilselvan]

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

[sonaliak]

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 ?

[jmodic]

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

[hinermak]

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.

[Mara]

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.

[manjunathk]

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