Re: Strange tablespace overflow error

If you are updating columns with variable length datatypes, and you increase the length of the data you are stuffing the column, then it is possible that the row of data will no longer fit into a data block. If that is the case, then the entire row is migrated to another block, thus requiring more space. It is also possible that an update statement makes the row to big to fit into any one, single block. So the row must be chained across mutiple blocks. In either case, it is possible for a table to grow in size with an update statement.

HTH,
Brian

Squeller27 wrote:
>
> Hi,
>
> My script has to update records of a large table (> 40 million records); it
> get the following error message after running the script for about 8 hours:
>
> 09:49:58 SQL> exec emc_aut_toekennen('ALLES', TO_DATE('01-04-2002',
> 'dd-mm-yyyy'))
>
> BEGIN emc_aut_toekennen('ALLES', TO_DATE('01-04-2002', 'dd-mm-yyyy')); END;
>
> *
>
> FOUT in regel 1:
>
> .ORA-01653: unable to extend table MGTINF.UITGEVOERDE_VERRICHTINGEN by
> 138254 in
>
> tablespace MGTINFODAT
>
> ORA-06512: at "MGTINF.EMC_AUT_TOEKENNEN", line 200
>
> ORA-06512: at line 1
>
> 17:19:24 SQL>
>
> Furthermore,
>
> - the table to be updated is called 'uitgevoerde_verrichtingen', its
> tablespace is MGTINFODAT
> - ONLY updates take place, no records are inserted in the tablespace
> MGTINFODAT (= the tablespace of the table 'uitgevoerde_verrichtingen').
> - the updates take place in a loop; after every update of 1 million records
> a commit is done (in order to prevent a rollback segment overflow)
> - the rollbacksegments are stored in a tablespace called RBS (thus a
> different tablespace)
>
> This code is responsible for the action (dynamic SQL is used, the variables
> v_sql etc contain a big update statement):
>
> v_uve_verr_id := 1;
> v_step := 1000000;
> WHILE v_uve_verr_id <= v_max_uitgevoerde_verr_id LOOP
> v_where_clause := ' WHERE uitgevoerde_verr_id >= ' ||
> TO_CHAR(v_uve_verr_id) ||
> ' AND uitgevoerde_verr_id <= ' ||
> TO_CHAR(v_uve_verr_id+v_step) ||
> ' AND ( dbc_status = ''I'' ' ||
> ' OR dbc_status = ''C'')';
> DBMS_OUTPUT.PUT_LINE('Koppel: ' || TO_CHAR(v_uve_verr_id) || ' <=
> uitgevoerde_verr_id <= ' || TO_CHAR(v_uve_verr_id+v_step));
> EXECUTE IMMEDIATE(v_sql || v_where_clause || v_rest_sql ||
> v_where_clause);
> COMMIT;
>
> v_uve_verr_id := v_uve_verr_id + v_step;
> END LOOP;
>
> IMO the error should no occur because there are no records inserted in the
> tablespace MGTINFODAT.
>
> Can anyone explain this?
>
> Thanks in advance,
>
> Eric

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"