Auto Commit; 1

[yash]

Hi ALL:
How can we set auto commit in PL/SQL . So that my following code commit records after every 10,000 rows update.
I'm getting Rollback segment error as there are more than 10,00,000 records in table to update..
ORA-01562: failed to extend rollback segment number 6
ORA-01628: max # extents (121) reached for rollback segment ROLLBACK5

*********** My PL/SQL Code
FOR sku_num_rec IN sku_num_cur
LOOP
v_update_str := 'UPDATE '||sku_num_rec.table_name||
' SET ' ||sku_num_rec.column_name||
'= ''' ||v_prefix1||v_prefix2||'''||'||sku_num_rec.column_name||
' WHERE LENGTH('||sku_num_rec.column_name||') = '||v_sku_len ;

BEGIN
EXECUTE IMMEDIATE v_update_str;
UTL_FILE.put_line(v_file,sku_num_rec.table_name||' successfully updated.');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_text_err := '***ERROR: While updating '||sku_num_rec.table_name;
UTL_FILE.put_line(v_file,v_text_err);
v_text_err := '***ERROR:'||sqlerrm;
UTL_FILE.put_line(v_file,v_text_err);
ROLLBACK;
END;
END LOOP;

 

[stefanhei]

Hi.
Better use a large private rollback segemnt, else you might run into ORA-1555.

Stefan

 

[SantaMufasa]

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&quot. 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: &quot;CREATE PUBLIC ROLLBACK SEGMENT <name_of_rollback_segment> TABLESPACE <rbs_tablespace> STORAGE(INITIAL <size> NEXT <size> MAXEXTENTS UNLIMITED);&quot;
[This doesn't really let the rollback segment have an unlimited number of extents; in this case &quot;infinity&quot; equals 32,765.] To determine <size>, divide your &quot;guesstimate&quot; 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 &quot;ALTER ROLLBACK SEGMENT <name> STORAGE (OPTIMAL <your_preferred_size>);&quot;.

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 &quot;offline&quot; 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:
&quot;ALTER ROLLBACK SEGMENT <large_rbs_name> ONLINE;
SET TRANSACTION USE ROLLBACK SEGMENT <large_rbs_name>;
(invoke your PL/SQL code here).&quot;

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 &quot;private&quot; rollback segment makes it unusable by other transactions, but that is totally false. (A &quot;private&quot; rollback segment implies other behavior that we can discuss under another thread at another time.)] 2) The &quot;SET TRANSACTION...&quot; command can only occur if it is the first statement of a transaction; since the command just prior to the &quot;SET...&quot; was &quot;ALTER...&quot; (a DDL command) and DDL produces an automatic COMMIT, so you are okay with the &quot;SET...&quot; 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 &quot;rbs.sql&quot;:

set linesize 200
set pagesize 35
col s format a10 heading &quot;Segment|Name&quot;
col rssize format 999,999,999,999 heading &quot;RBS Size&quot;
col max format 9,999,999,999 heading &quot;Max|Extents&quot;
col xacts heading &quot;Num|Curr|Xacts&quot; format 999
col sgid heading &quot;Seg|Id&quot; format 99
col opt heading &quot;Optimal&quot; format 999,999,999
col nxt heading &quot;Next|Extent&quot; format 99,999,999
col owner heading &quot;Owner&quot; 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 &quot;RollbackSegments.txt&quot;.
prompt
spool off

You can &quot;SQL> @rbs&quot; 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: &quot;ALTER ROLLBACK SEGMENT <large_rbs_name> STORAGE (OPTIMAL 10m);&quot;. You can wait for Oracle to cause the shrinkage to occur on its own, or you can explicitly invoke the shrinkage with, &quot;ALTER ROLLBACK SEGMENT <large_rbs_name> SHRINK;&quot;. The shrinkage may take many minutes (even hour+). Monitor the size with &quot;rbs.sql&quot;. If shrinkage stops without reaching <optimal-size>, it does not hurt to issue another &quot;ALTER...SHRINK;&quot; command. (Other intervening transactions can interrupt the shrinkage.)

Once the rollback segment is down to its OPTIMAL size, you can issue the command, &quot;ALTER ROLLBACK SEGMENT <large_rbs_name> OFFLINE;&quot; This command prevents any new transactions from entering <large_rbs_name>. If there are any active transactions that may have sneaked (some people say, &quot;snuck&quot 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 &quot;happy medium&quot; of the above options...) Logically divide up your PL/SQL &quot;work&quot; into more &quot;bite-sized&quot; increments. Specifically, cause your PL/SQL &quot;WHERE&quot; 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: &quot;COMMIT Happens&quot; <smile>

Amen.

 

[stefanhei]

Hi.
Sorry for my last reply being so brief (and therefor i guess misleading).
The ORA-1555 referes to the commit every x rows-approach (Santa's Option 1). I've seen this fail too often, so I recomend not to use it.
Usual solutions to this problem are Santa's Option 2 (if it's 1,000,000 rows), or recreation of the table via CREATE TABLE ... AS SELECT ... (if it's 10,000,000 rows).

Stefan

 

[gunjan14]

Hi Dave,

How can we determine the magic figure of 'x' rows before commit? (In the option 1)

Gunjan

 

[SantaMufasa]

Gunjan,

To determine &quot;statistical&quot; growth of your rollback segment for a large or long-running transaction in order to identify a reasonable &quot;increment value&quot; for number of rows per chunk to process, then I would do something like this:

1) Create a rollback segment that can grow to a very large size (See above, &quot;...MAXEXTENTS UNLIMITED&quot so that you do not encounter the &quot;ORA-01628: max # extents (121) reached for rollback segment...&quot; error.

2) From one session (that has DBA privileges), issue the following commands:
a) &quot;ALTER ROLLBACK SEGMENT <large_rbs_name> ONLINE;&quot;
b) &quot;Set transaction use rollback segment <large_rbs_name>;&quot;
c) Begin your update code. This code should run in a fashion that makes it very easy to determine how many updates have occurred so far. For example, either A) a SQL statement with some type of WHERE clause that manages number or rows updated [&quot;UPDATE...WHERE rownum <= 10000&quot or B) a PL/SQL block with a LOOP counter of some sort.

3) From another, concurrent session (that also has DBA privileges), issue the following commands:
a) &quot;ALTER ROLLBACK SEGMENT <large_rbs_name> OFFLINE;&quot; (This command makes your large rollback segment unavailable for any new transactions; it stays available for your already-running update transaction, however.)
b) Execute (iteratively) the &quot;rbs.sql&quot; code from my &quot;26 September&quot; reply, above, to monitor growth of your large rollback segment during your updates initiated in Step 2.

This monitoring of the growth of your large rollback segment should give you the statistics you need to project the rate of &quot;rollback usage per x updates&quot;. Then, using these results, you can determine how many updates you want to allow in order to avoid &quot;rollback overflow.&quot;

Dave
Sandy, Utah, USA @ 17:46 GMT, 10:46 Mountain Time