Strange tablespace overflow error

From: Squeller27 <squeller27_at_piotr.com>
Date: Thu, 24 Jul 2003 10:10:09 +0200
Message-ID: <bfo48s$cdi$1@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?

Thanks in advance,

Eric Received on Thu Jul 24 2003 - 03:10:09 CDT