If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I got a problem when loading data from our production database into our data warehouse using a procedure. When we were executing this procedure, we got the following errors:

ERROR at line 1:
ORA-02055:
distributed update operation failed; rollback required
ORA-01653:
unable to extend table xxx by 1024 in tablespace 'tblspc'
ORA-02063: preceding line from WRHS
ORA-06512: at 'procname', line 599
ORA-06512: at line 1

Checked ORA-0255, the action is: Roll back to a savepoint or rollback the entire transaction and resubmit the rolled-back statements. We rolled back the entire transaction. Also,there are two datafiles on that tablespace, one of these two got autoExtend enabled. I enabled the Autoextend on the other datafile and tried to execute the procedure, again, we got:

We use Oracle 7.3.4 on Novell 5.0 and I checked the operating system-specific Oracle documentation, there is no OSD-0281. What should I do to fix this problem. Should I resize the rollback segment? Any advice would be appreciated. Thanks in advance.

Basically you are out of extents. It doesn't matter if you have 2000GB of space left if you did something like
Initial 5MB, Next 5MB, MAXEXTENTS = 99. Every time
you need an additional 5MB the table will extend another
5MB up to 98 more times (if minextents = 1) for a total of
99*5 or 455MB. The minute you try to extend another
5MB you will get the error. The idea here is to have some
control over table growth. You could set max extents to
unlimited if you don't care how much space the table
takes up in the tablespace. A better idea would be to figure
out just how much space each row takes on the average and
how many rows you will typically load and set initial to
that figure. If you need to know how to size the table
properly, Oracle has a set of formulas in Appendix A of the administrators guide that will tell you how to do this. I take it that this is a staging table you are loading into. You'll be truncating it anyway after the load so why add the additional overhead involved with extent management.