Need help on loading data to data warehouse
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Need help on loading data to data warehouse

  1. #1
    Join Date
    Jul 2000
    Posts
    6
    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]

  2. #2
    Join Date
    Jul 2000
    Posts
    6
    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

  3. #3
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    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]
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  4. #4
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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


  5. #5
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width