Re: Strange tablespace overflow error

"Squeller27" <squeller27_at_piotr.com> wrote in message news:bfo48s$cdi$1_at_mrelay2.eur.nl...
> 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?

Eric,

is the update causing chained rows ?

Paul Dixon Received on Thu Jul 24 2003 - 09:37:27 CDT