Для устранения проблемы SNAPSHOT TOO OLD надо понимать, что его причина:
ВРЕМЯ ЖИЗНИ сегмента отката но НЕ РАЗМЕР.
т.е КОЛИЧЕСТВО других транзакций больше, чем могут принять ВСЕ экстенты ВСЕХ RBS.
Устранение:
- увеличение количества RBS
- увеличения количества экстентов RBS

По поводу OPTIMAL в данной ситуации он не работает (почти)
поскольку это параметр RBS который действует ПОСЛЕ транзакции но не ВО ВРЕМЯ ТРАНЗАКЦИИ

О размере RBS его нужно расчитывать исходя из :
INESRT/DELETE - исходя из однократного размера всех записей
UPDATE - исходя из двухкратного размера всех записей

ORA-01555 snapshot too old: rollback segment number string with name "string" too small
Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.
If in system only 1 transaction where we can get "other writers"
----------------------------------------------------------------------------------
About mait question:

U can use TRANSACTION SPECIFIC GLOBAL TEMPORARY TABLE,
timelife data in that table 1 trancaction,
Oracle don't create RBS of this kind of table never. After genetate your rows
into GTT u can use :
insert into YOUR_MAIN_TABLE select * from GTT;
commit;

I tested size of temp tablespace for gtt, it not big.
This is my post about GTT and temporary TS

Tarry, Ly
ALTER ... = commit
in any situation
---------------------------------------------------------------------------------------------------------------------
Try to discover situation with temporary tables :

I opened 2 session:

1) with system account (system user)
2) with train account (training user)

then in train session i did:

-- alter sort area size (made it smaller)
-- got summary USA and PGA for train user (from system session)
and
-- got sotrt segments for this session (train user)

SQL> alter session set sort_area_size=32000;
Session altered.

(IN system session )
---------------------------------------------------------------------------------------------------------------------------------
O/S PGA PGA UGA UGA User
User User Oracle Login Memory MaxMemory Memory MaxMemory MaxMemory
Sid Spid PROCESS User ID ID TERM STAT KB KB KB KB KB
----- ----- ---------- ------------ -------- -------------------- -------- ---------- ---------- ---------- ---------- ----------
.. 6497 *? ? ? ACTIVE 74.797 74.797 19.113 19.113 93.910
13 14267 *8673 TRAIN sgene pts/4 INACTIVE 108.047 108.047 44.305 44.305 152.352
.. 6511 *? ? ? ACTIVE 74.797 74.797 19.113 19.113 93.910

(IN Train session)

SQL> select USER, TABLESPACE, SEGTYPE, sum(BLOCKS)
from v$sort_usage
where USER='TRAIN'
group by USER, TABLESPACE, SEGTYPE, session_num
;

no rows selected

default temporary ts for user train -> TEMP

then I created global temporary table in train session:

create global temporary table temp_train
as select * from all_objects where rownum <= 10;

---------------------------------------------------------------------------------------------------------------------------------
O/S PGA PGA UGA UGA User
User User Oracle Login Memory MaxMemory Memory MaxMemory MaxMemory
Sid Spid PROCESS User ID ID TERM STAT KB KB KB KB KB
----- ----- ---------- ------------ -------- -------------------- -------- ---------- ---------- ---------- ---------- ----------
13 14267 *8673 TRAIN sgene pts/4 INACTIVE 684.762 684.762 63.922 63.922 748.684
30 6499 *? ? ? ACTIVE 74.797 74.797 19.113 19.113 93.910

14 rows selected.

Oracle increased PGA from 74.797 up to 684.762 bytes WITHOUT SORTS IN THIS SESSION

SQL> commit;

Commit complete.

SQL> select count(*) from temp_train;

COUNT(*)
----------
0

SQL> insert into temp_train
select * from all_objects where rownum <=5000
order by object_id;

2000 rows created.

select USER, TABLESPACE, SEGTYPE, sum(BLOCKS)
from v$sort_usage
where USER='TRAIN'
group by USER, TABLESPACE, SEGTYPE, session_num
;

USER TABLESPACE SEGTYPE SUM(BLOCKS)
------------------------------ ------------------------------- --------- -----------
TRAIN TEMP DATA 50

SQL> commit;

Commit complete.

select USER, TABLESPACE, SEGTYPE, sum(BLOCKS)
from v$sort_usage
where USER='TRAIN'
group by USER, TABLESPACE, SEGTYPE, session_num
;

no rows selected

After any kind of commit statment (we remember ALTER..=COMMIT
oracle deallocate all temporary segments from temp ts for this session.

Then I increased sort area size for sessin:

SQL> alter session set sort_area_size=4000000;

Session altered.
SQL> select count(*) from temp_train;

COUNT(*)
----------
0

insert into temp_train
select * from all_objects where rownum <=2000;

2000 rows created.

SQL> select count(*) from temp_train;

COUNT(*)
----------
2000

SQL> select USER, TABLESPACE, SEGTYPE, sum(BLOCKS)
from v$sort_usage
where USER='TRAIN'
group by USER, TABLESPACE, SEGTYPE, session_num
;

no rows selected

SQL> insert into temp_train
select * from all_objects where rownum <=2000;

2000 rows created.

SQL> select count(*) from temp_train;

COUNT(*)
----------
4000

SQL> select USER, TABLESPACE, SEGTYPE, sum(BLOCKS)
from v$sort_usage
where USER='TRAIN'
group by USER, TABLESPACE, SEGTYPE, session_num
;

USER TABLESPACE SEGTYPE SUM(BLOCKS)
------------------------------ ------------------------------- --------- -----------
TRAIN TEMP DATA 100


OK!!! oracle use temporary ts ONLY it couldn't allocate enogth memory.
But still question --> Which memory oracle used?


O/S PGA PGA UGA UGA User
User User Oracle Login Memory MaxMemory Memory MaxMemory MaxMemory
Sid Spid PROCESS User ID ID TERM STAT KB KB KB KB KB
----- ----- ---------- ------------ -------- -------------------- -------- ---------- ---------- ---------- ---------- ----------
7 6497 *? ? ? ACTIVE 74.797 74.797 19.113 19.113 93.910
13 14267 *8673 TRAIN sgene pts/4 INACTIVE 684.762 684.762 47.480 64.027 748.789
14 6511 *? ? ? ACTIVE 74.797 74.797 19.113 19.113 93.910

PGA and UGA size still the same!

SQL> commit;

Commit complete.

SQL> select count(*) from temp_train;

COUNT(*)
----------
0

SQL> insert into temp_train
select * from all_objects where rownum <=5000
order by object_id;

4361 rows created.

User User Oracle Login Memory MaxMemory Memory MaxMemory MaxMemory
Sid Spid PROCESS User ID ID TERM STAT KB KB KB KB KB
----- ----- ---------- ------------ -------- -------------------- -------- ---------- ---------- ---------- ---------- ----------
10 6503 *? ? ? ACTIVE 74.797 74.797 19.113 19.113 93.910
13 14267 *8673 TRAIN sgene pts/4 INACTIVE 1236.723 1236.723 47.480 64.027 1300.750
14 6511 *? ? ? ACTIVE 74.797 74.797 19.113 19.113 93.910

SQL> insert into temp_train
select * from all_objects where rownum <=5000
order by object_id;

4361 rows created.

SQL> select count(*) from temp_train;

COUNT(*)
----------
8722

I used sort (order by) in two selects

Conclusion 1 --> oracle use temp segments for temporty tables and probably for sort.
I espesially used 2 statments because:
-- it has to increase size of temporary table
-- it hasn't use a lot of sort_area

Results:


User User Oracle Login Memory MaxMemory Memory MaxMemory MaxMemory
Sid Spid PROCESS User ID ID TERM STAT KB KB KB KB KB
----- ----- ---------- ------------ -------- -------------------- -------- ---------- ---------- ---------- ---------- ----------
10 6503 *? ? ? ACTIVE 74.797 74.797 19.113 19.113 93.910
13 14267 *8673 TRAIN sgene pts/4 INACTIVE 1236.723 1236.723 47.480 64.027 1300.750
14 6511 *? ? ? ACTIVE 74.797 74.797 19.113 19.113 93.910

USER SESSION_NUM TABLESPACE SEGTYPE SUM(BLOCKS)
------------------------------ ----------- ------------------------------- --------- -----------
TRAIN 39424 TEMP DATA 225


Oracle used temp tablespase probably for temporary table only and sort area for sort operation only.
Because afrer second insert with order by PGA/UGA didn't insrease.

> Решение с temporary таблицами не подойдет, т.к. ему надо делать выборка->апдейт->коммит в цикле.

Who says that we can't write pieses of data from GTT, after data will
write into main table we don't need keep rows in GTT. We may reuse this
table again "in scratch"