Re: Strange tablespace overflow error

"Paul Dixon" <root_at_127.0.0.1> wrote in message news:<bforei$3pf$1_at_pheidippides.axion.bt.co.uk>...
> "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

I think Paul is right. The update is causing columns to expand so the row expands and will no longer fit back into the block it is in so Oracle has to either chain or migrate the row to another block. Oracle is looking for an empty block to use, does not have any, so it attempts to allocate another extent. The tablespace does not have enough contiguous free space to meet the allocation request so you get an error. Add another file. It is very possible that this table has a fair amount of space trapped in blocks that it cannot effecively reuse. An analyze will show you the chained row count. The table might benefit from a re-org via exp/imp or alter table move command.

HTH -- Mark D Powell -- Received on Thu Jul 24 2003 - 15:34:32 CDT