-
Hello all,
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:
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-09217: sfsfs: failed to resize file
OSD-02081:
Message 2081 not found; product=RDBMS73; facility=SOSD
(OS 1)
ORA-09217: sfsfs: failed to resize file
OSD-02081:
Message 2081 not found; product=RDBMS73; facility=SOSD
(OS 1)
ORA-02063: preceding 7 lines from WRHS
ORA-06512: at 'procname', line 599
ORA-06512: at line 1
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.
Li
[Edited by cui_li on 11-15-2000 at 07:56 PM]
-
I tried to increase the size for these two datafiles manually, it failed, and I got the same errors as before--
ORA-01653:
unable to extend table xxx by 1024 in tablespace 'tblspc'
ORA-09217: sfsfs: failed to resize file
OSD-02081:
Message 2081 not found; product=RDBMS73; facility=SOSD
(OS 1)
ORA-09217: sfsfs: failed to resize file
OSD-02081:
Message 2081 not found; product=RDBMS73; facility=SOSD
(OS 1)
Any idea what should I do to fix the problem? Thanks in advance.
Li
-
What are the storage parameters of the table?
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.
Let me know if you need additional help.
Joe
[Edited by wjramsey on 11-15-2000 at 11:23 PM]
-
Check with oracle. It may be a bug.
I have the same problem with the combination server 7.3.4.3 and AIX 4.3.3.
--> Cannot resize a datafile
Regards
Gert
-
1. check for maxextents on the tablespace
2. bounce the db and then try to resize
3. add another dbf if none of the above work
4. call tech support
