Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    9

    Unanswered: ORA-01654: unable to extend index

    Hey there,

    I'm receiving the following error messages when trying to insert
    records (error #1) and update records (error #2).

    #1. ORA-01654: unable to extend index SYSTEM.TDW_CHARGES_IDX04 by 112085 in tablespace TDWTS_INDEX
    #2. ORA-01654: unable to extend index SYSTEM.TDW_DAILY_FEED_IDX04 by 39488 in tablespace TDWTS_INDEX

    All of the indexes are stored in a single physical file named: TDW_INDX.ORA
    which is 8.3gig in size. The application is running on a Windows 2000
    server, using NTFS file system and Oracle Server version 7.3. All of
    my indexes and tablespaces are created with unlimited extents.

    I can drop and rebuild the indexes successfully, but after letting the
    application run a while, I begin getting the error messages again.
    Oracle documentation says to add another datafile for the tablespace,
    but I'd like to avoid doing this. This application is running at several
    sites and I would like to keep each site consistant as far as files, etc.

    I know that Oracle has a bug with version 7 files keeping the unlimited
    feature set, but I've checked and this doesn't seem to the the problem.

    Any ideas?

    Thanks for any input that ya'll might have.

    Jim

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    A. Do you have diskspace available...
    B. Is the tablespace TDWTS_INDEX set for autoextending ... if so
    will the current size + extend size > available diskspace.
    C. How many extents are the current indexes ?
    D. I would be leary of a file > 4gig on Windows. I have seen the file
    actually trucate back to a 4gig limit (which WILL hose the database).

    Gregg

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I would go along with Gregg in that I've had problems with files larger than 4GB (and even if you dont now you might when you migrate to 8i/9i/10g). I would suggest the best thing to do is

    1) create a new tablespace with 2 (or more) datafiles of 3.9GB each (or with autoextend on maxsize 3900MB) .
    2) Rebuild your indexes into the new tablespace.
    3) Drop your old tablespace.

    AND repeat this at all your sites so they are consistent.

    Alan

  4. #4
    Join Date
    Sep 2003
    Posts
    9
    Gregg,

    Thanks for your input.

    A. I have 15gig left on disk

    B. datafile is set for autoextend (below is create statement)
    create tablespace TDWTS_INDEX datafile '&2\tdw_indx.ora'
    size 400M autoextend on next 200M maxsize UNLIMITED
    default storage(initial 050K next 050K pctincrease 50 maxextents UNLIMITED);

    C. below is extent info (is this what you're asking?)

    INDEX_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS
    ----------------------- -------------- ----------- ----------- ----------- ------------ ------
    TDW_CHARGES_IDX04 60354560 229550080 1 2.147E+09 50 VALID
    TDW_DAILY_FEED_IDX04 3145728 80621568 1 2.147E+09 50 VALID


    TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS
    ----------------------- -------------- ----------- ----------- ----------- ------------ ------
    TDWTS_DATA 51200 51200 1 2.147E+09 50 ONLINE
    TDWTS_INDEX 51200 51200 1 2.147E+09 50 ONLINE
    TDWTS_RBS 1048576 1048576 1 2.147E+09 50 ONLINE
    TDWTS_TEMP 1048576 1048576 1 2.147E+09 50 ONLINE
    TDWTS_TRX 1048576 1048576 1 2.147E+09 50 ONLINE
    TDWTS_RPTW 1048576 1048576 1 2.147E+09 50 ONLINE


    Should the next extent of the tablespace (51200) be larger than the next extent of the
    indexes? I just noticed this.


    D. truncating back to 4gig? Have you seen this happen often? Do
    you know any details of what caused it? This concerns me!


    Thanks again.

    Jim

    (sure hope the table above lines up good for you!)

  5. #5
    Join Date
    Sep 2003
    Posts
    9
    Alan,

    Thanks for the info. I was under the impression that NTFS allowed
    files to grow to the device's capacity. Is this not right? I've been
    running several sites with 8G database files for a couple of years, but
    if I'm about to have a trainwreck, I'd like to try to head it off.

    Thanks again.

    Jim

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I've seen it happen 1 time... I haven't allowed any of my customers
    to have datafiles larger than 3995m.

    Tablespace extensions are expensive (disk hits). You try to size the
    tablespace so that there is minimal extension (but it will happen). It
    it needs to extent, it will and Oracle will have to "touch" each data
    block of the new extent. This is costly.

    Gregg

  7. #7
    Join Date
    Sep 2003
    Posts
    9
    Hey Gregg - thanks for the info. I appreciate it. Sounds like I need to consider splitting my files up. Good luck to you and thanks again.

Posting Permissions

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