DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Unable allocate the extent

  1. #1
    Join Date
    Oct 2000
    Posts
    250

    Unable allocate the extent

    Hi everyone,
    anyone encounter the following error message :
    ORA-03232: unable to allocate an extent of 18 blocks from tablespace 30

    I had checked my free space for the file ID 30 is still has plenty of spaces. Had anyone any ideas ?


    Regards

  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530

    Re: Unable allocate the extent

    Hi,
    Though the tablespace has plenty of free space,this error can be occured when there are no continuous free blocks available to create an extent.Since extent is made up of continuous Oracle blocks,it is not able to get the continuous Oracle blocks to create the extent.In this case you should try to coalesce the tablespace for de-fragmenting it and having continuous space in it.Also if possible,try to add a datafile to have extents in it.Add then try whether this error is comming or not.

    Regards,
    Rohit Nirkhe,Oracle/Apps DBA,OCP 8i
    oracle-support@indiatimes.com
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  3. #3
    Join Date
    Oct 2000
    Posts
    250
    I had performed the alter tablespace tbs coalesce; But the error still prompts out.

    I don't think adding datafile to it is the solution cause my tablespace is still has plenty of space.

    Thanks for the reply.

  4. #4
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    Try to change the storage parameters as

    alter tablespace storage (maxextents unlimited)

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by ckwan
    I had performed the alter tablespace tbs coalesce; But the error still prompts out.

    I don't think adding datafile to it is the solution cause my tablespace is still has plenty of space.

    Thanks for the reply.
    Coalesce will not help to defragment if fragmentation is at table level....

    Calculate how much of the space is fragmented....and post us the result.....i guess it has reached its peak....
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Does "Fragmentation" makes sense to you..your tablespaces are fragmented..thats why you are unable to allocate a new extent because there is no contigous free space..You should take a full export of the objects in the tablespace with compress=Y and import it into the same tablespace..

    You can check for fragmentation in your database using the following script..

    select a.tablespace_name,nvl(round(fp) ,0)
    free_space,nvl(round(mfp),0)cont_free_sp,
    nvl(round(op),0) act_space,nvl(round(cop),0) cont_act_space
    from (select tablespace_name,sum(bytes/1024/1024)
    fp,max(bytes/1024/1024)
    mfp
    from dba_free_space
    group by tablespace_name) a,
    (select tablespace_name,sum(bytes/1024/1024) op,max(bytes/1024/1024)
    cop
    from dba_segments group by tablespace_name) b where
    a.tablespace_name=b.tablespace_name(+)
    /

    The difference in free space and continuous free space should be same
    in
    case of a non-fragmented tablespace

    in case you dont want to de-fragment..you should add another datafile there is no way out dude..

    regards
    Hrishy

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Hmmm, it's get on in the afternoon but seeing the responses, I've got to reply...

    Originally posted by ksridhar

    Try to change the storage parameters as

    alter tablespace storage (maxextents unlimited)
    I really don't think that modifying your TABLESPACE storage parameters is going to have ANY affect on your current segment... actually, I'm certain it won't. Besides, even if you modifyed the tables current MAXEXTENTS, the error isn't "MAX EXTENTS REACHED"

    Originally posted by abhaysk


    Coalesce will not help to defragment if fragmentation is at table level....

    Fragmentation at a table level. Do you mean fragmentaion within the table, i.e. with table has extended and contains very little data.. which in case, see next..

    Originally posted by hrishy
    Hi

    You should take a full export of the objects in the tablespace with compress=Y and import it into the same tablespace..
    Oh god no.... why COMPRESS=Y... my advice.. NEVER USE this. Also, if you use COMPRESS=Y, then you'll exacerbate the issue, cause your DB with most likely be looking for a LARGER contiguous block in the tablespace to store the same object in..

    I would....

    a). Determine which tablespace it is, and what TYPE of segments are contained and what type of segment is having the problem.
    b). Coalesce tablespace
    c). Add more SPACE to the tablespace

    And then if you are having trouble with FRAGMENTATION....

    d). ALTER TABLE MOVE... (if >= 8.1.5) to a different tablespace.
    e). CREATE TABLE AS... (< 8.1.5) (Recreating indexes, grants etc).
    f). EXPORT COMPRESS=N, Drop TABLE, re-IMPORT (this will NOT however eliminate your fragmentation... if you indeed have any) If you have a DMT (or a nonuniform LMT) containing segments with objects of different sized INITIAL and NEXT you're tablespace is a candidate for fragmentation. Try and keep you segments in a UNIFORM size in each tablespace.

    And what sort of tablespace name is TABLESPACE 30???? Could be the TEMP Tablespace for all we know... and if it is... ADD MORE SPACE.

    Cheers,
    Last edited by grjohnson; 02-10-2003 at 02:34 AM.
    OCP 8i, 9i DBA
    Brisbane Australia

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Oh god no.... why COMPRESS=Y... my advice.. NEVER USE this. Also, if you use COMPRESS=Y, then you'll exacerbate the issue, cause your DB with most likely be looking for a LARGER contiguous block in the tablespace to store the same object in..

    Hi Jhonson

    what if he is oracle 7.3.4..then he has to do export import with compress=Y to overcome honeycomb fragmentation..alter tablespace coalesce will not owrk in that case..



    d). ALTER TABLE MOVE... (if >= 8.1.5) to a different tablespace.


    Excellent suggestion if he is on 8i and above...

    regards
    Hrishy

  9. #9
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    Increase the value of NEXT for the tablespace using ALTER TABLESPACE DEFAULT STORAGE
    Last edited by ksridhar; 02-10-2003 at 03:45 AM.

  10. #10
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Originally posted by ksridhar
    Increase the value of NEXT for the tablespace using ALTER TABLESPACE DEFAULT STORAGE
    No... once again a tablespace DEFAULT will NOT affect a current segment. A TABLESPACE DEFAULT value is ONLY used by a segment in which is created WITHOUT specificed options.

    Originally posted by hrishy
    what if he is oracle 7.3.4..then he has to do export import with compress=Y to overcome honeycomb fragmentation..alter tablespace coalesce will not owrk in that case..
    COMPRESS=Y will (in the long term) increases fragmentation and in this case won't improve the situtation. If the users exports five 1MB segments, it is comprssed to a single 5MB segment. The user is having trouble finding enough contiguous space for a single extents of 1MB rather than one for 5MB.

    ALSO, Check you rsegments PCTINCREASE setting is set to 0, this COULD be the problem, as it may be trying to expand the segment by NEXT EXTENT * 50.
    OCP 8i, 9i DBA
    Brisbane Australia

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