Stefan, Please don't confuse Yash's problem with ORA-01555. Using a large rollback segment for HIS transaction does not prevent ORA-01555. Even using the world's largest rollback segment for HIS transaction does not prevent ORA-1555 if other transactions are concurrent in normal-sized rollback segments. [If you have the stomach for it and the patience, please refer to, and re-read, the thread "ORA-01555: snapshot too old - Problem with Rollback Segments?" (Last updated 9/12/03; originally posted 8/12/03.]
Now, Yash, you have a few options to deal with your problem ("...max # extents (121) reached for rollback segment ROLLBACK5"
. First some background: "SET AUTOCOMMIT ON" is one of the few SQL*Plus commands that actually affects the database. If you are running your PL/SQL code from SQL*Plus, then you can issue the command, "SET AUTOCOMMIT ON", but you must issue the command from the SQL*Plus prompt BEFORE initiating your PL/SQL code. BUT ISSUING A "SET AUTOCOMMIT ON" BEFORE RUNNING YOUR PL/SQL CODE STILL WILL NOT SOLVE YOUR PROBLEM. This is because AUTOCOMMIT issues an implicit COMMIT after each "statement" and SQL*Plus considers a single block of PL/SQL code to be one "statement", and, therefore, does not COMMIT until the block of PL/SQL reaches its natural completion. You won't get a COMMIT until after all 10,000,000+ rows have been updated. Thus, you encounter the same error as before.
Now your "error avoidance" options:
Option 1) You create a loop counter within your PL/SQL code that explicitly issues a COMMIT every "x" number of rows processed. (This may not be an option if you drive your processing via a cursor. In the case of a CURSOR, a COMMIT before you reach the logical end of your cursor causes you to lose the context of the CURSOR, in which case this option may be unacceptable for you.)
Option 2) You must process all 10,000,000+ rows before your COMMIT occurs. In such case, you need a rollback segment which will grow to humongous size to hold the rollback for all 10,000,000 rows. (Without first successfully running your PL/SQL code, it may be impossible to predict the exact size of the rollback necessary to accommodate this transaction, so you will need to have plenty of file system space available for the rollback segment tablespace to make it happen.) But you at least need to make an educated "guesstimate" of that size. To make the "guesstimate", multiply the size of the current average column(s) size(s) that the PL/SQL code changes, times the number of rows that are changing. Then,
a) ensure you have that much available file-system space for the tablespace that houses your soon-to-be humongous rollback segment.
b) I recommend creating a new/fresh rollback segment to grow way large: "CREATE PUBLIC ROLLBACK SEGMENT <name_of_rollback_segment> TABLESPACE <rbs_tablespace> STORAGE(INITIAL <size> NEXT <size> MAXEXTENTS UNLIMITED);"
[This doesn't really let the rollback segment have an unlimited number of extents; in this case "infinity" equals 32,765.] To determine <size>, divide your "guesstimate" of total size by 32,000 extents (or less). So, for the sake of discussion, your average pre-change column size per row is 1000 bytes, times 10M rows, you need 10GB or rollback. Divide 10GB by 32,000 extents and your extent <size> needs to be at least 312,500 bytes. (I personally like to round up to the nearest 500K or 1M for <size>.) Also, since we want to know the actual size that the rollback segment becomes, DO NOT specify OPTIMAL <size> just yet. Allow the transaction to run, note the finished size of the large rollback segment, then "ALTER ROLLBACK SEGMENT <name> STORAGE (OPTIMAL <your_preferred_size>);".
Now, some people prefer reserving this large rollback segment for use by just large transactions. To make that happen, you can leave the large rollback segment "offline" until just before running your large transaction. Also, you will need to explicitly assign your large transaction to the large rollback segment. So, this paragraph's code scenario looks like this:
"ALTER ROLLBACK SEGMENT <large_rbs_name> ONLINE;
SET TRANSACTION USE ROLLBACK SEGMENT <large_rbs_name>;
(invoke your PL/SQL code here)."
A couple of notes about this code: 1) Nothing prevents some other transaction from using <large_rbs_name> while your large transaction executes. [Some people believe that creating the rollback segment as a "private" rollback segment makes it unusable by other transactions, but that is totally false. (A "private" rollback segment implies other behavior that we can discuss under another thread at another time.)] 2) The "SET TRANSACTION..." command can only occur if it is the first statement of a transaction; since the command just prior to the "SET..." was "ALTER..." (a DDL command) and DDL produces an automatic COMMIT, so you are okay with the "SET..." command where it is (following DDL).
Once your PL/SQL block finishes, and you have issued the COMMIT, note the size of your very-large rollback segment. I personally use the following code which I run from a script I call "rbs.sql":
set linesize 200
set pagesize 35
col s format a10 heading "Segment|Name"
col rssize format 999,999,999,999 heading "RBS Size"
col max format 9,999,999,999 heading "Max|Extents"
col xacts heading "Num|Curr|Xacts" format 999
col sgid heading "Seg|Id" format 99
col opt heading "Optimal" format 999,999,999
col nxt heading "Next|Extent" format 99,999,999
col owner heading "Owner" format a6
spool RollbackSegments.txt
select d.segment_id sgid
, d.owner
, d.segment_name s
, v.rssize
, v.optsize opt
, d.next_extent nxt
, nvl(v.xacts,0) xacts
, nvl(v.status,'OFFLINE') status
, d.max_extents max
from dba_rollback_segs d, v$rollstat v
where v.usn(+) = d.segment_id
/
prompt
prompt Wrote spool file "RollbackSegments.txt".
prompt
spool off
You can "SQL> @rbs" while your large PL/SQL transaction runs to monitor the growth of your large rollback segment.
When your transaction finishes, you can alter your rollback segment to a reasonable optimal, perhaps: "ALTER ROLLBACK SEGMENT <large_rbs_name> STORAGE (OPTIMAL 10m);". You can wait for Oracle to cause the shrinkage to occur on its own, or you can explicitly invoke the shrinkage with, "ALTER ROLLBACK SEGMENT <large_rbs_name> SHRINK;". The shrinkage may take many minutes (even hour+). Monitor the size with "rbs.sql". If shrinkage stops without reaching <optimal-size>, it does not hurt to issue another "ALTER...SHRINK;" command. (Other intervening transactions can interrupt the shrinkage.)
Once the rollback segment is down to its OPTIMAL size, you can issue the command, "ALTER ROLLBACK SEGMENT <large_rbs_name> OFFLINE;" This command prevents any new transactions from entering <large_rbs_name>. If there are any active transactions that may have sneaked (some people say, "snuck"
into <large_rbs_name> before your OFFLINE command, Oracle allows them to finish normally, then Oracle completes the OFFLINE of the rollback segment.
Option 3: (The "happy medium" of the above options...) Logically divide up your PL/SQL "work" into more "bite-sized" increments. Specifically, cause your PL/SQL "WHERE" clause to carve out work increments smaller than 10 million rows, and re-run the incremental PL/SQL until you finish all 10M+ rows.
Remember, the bottom line to all of this should be on a bumper sticker for DBAs: "COMMIT Happens" <smile>
Amen.
