Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Location
    Karachi, Pakistan
    Posts
    45

    Red face Unanswered: Unable to Extent Index by 321172 by Tablespace Indexes

    During Data Loading through SQLLDR I face that error while i have enough space in my Tablespace. and next extent are also set by 504 KB for this tablespace, what i do please any body help me urgently

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    alter tablespace <tablespace_name> default storage (nextextent <size>{K,M});
    syntax may differ depending on LMT or DMT

  3. #3
    Join Date
    Dec 2003
    Location
    Karachi, Pakistan
    Posts
    45

    How much i extend extent

    How much i extend Extent

  4. #4
    Join Date
    Nov 2002
    Posts
    833
    for your particular index you needed 321172 bytes

    for others it may vary but check pctincrease if it is set to others than 0...

    best is to have equidistant chunks

  5. #5
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    In general when you get that message, it's telling you Oracle tried to create an extent of size X and didn't have enough room. However, there may be a lot more data to be loaded, needing more extents. Just because you increase the tablespace by X, does not mean the problem goes away.

    You should determine how much data you are loading, then allocate space appropriately to your tablespace prior to the load process. I.e., increase the size of the files that support the tablespaces containing the tables / indexes you are loading, and ensure the tables/indexes you are loading have maxextents set high enough or set to unlimited. The extent info at the tablespace level is just default values given to the table if you created the table without such information, so changing at the tablespace level won't really do anything for your existing tables.

    I've been assuming you're talking about space issues in the data / index tablespace. If this is in the undo tablespace, then basically the same rules apply but it will need a bit more space. Undo takes more space than data. Loading 1 GB data might need 4 GB undo space.

  6. #6
    Join Date
    Sep 2003
    Posts
    237
    You have to change PCTINCREASE parameter to 0 for the particular table ; also change the next extent; because PCTINCREASE is nonzero, requests for new extent sizes is increasing in a Geometric progression; it is asking for 321172 BLOCKS of new space; which is assuming 4K DB_BLOCK_SIZE is 1200Meg;
    mota

  7. #7
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    That's an excellent point, but he stated it a bit differently than I would.

    If PCTINCREASE is set to a non-zero integer, then each new extent is larger than the previous extent. It's like compounding interest. At some point, the next extent size was maybe 800 MB and it worked, but then the next extent was going to be 50% larger, or 1200 MB, and that's when Oracle failed with the error you noted. (1200 M is just a guess, and the extent size that caused the error could have really been between 600 MB and 4800 MB because your database block size could be as low as 2K and as high as 16K.)
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

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