extent question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: extent question

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586

    Exclamation

    I have a objectname table objx size 72.66 Mbs with initial of
    1,048,576 with NUMBER OF EXTENTS 263 and MAX EXTENTS
    of 505. size 76 megs.

    since number of extents is near max extents I am looking for guidance in what to do to make sure that I dont run out of extents.

    would i wanna increase max extents or export then coalesce then import with new initial etc.?
    "High Salaries = Happiness = Project Success."

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by jlakhani
    since number of extents is near max extents I am looking for guidance in what to do to make sure that I dont run out of extents.

    would i wanna increase max extents or export then coalesce then import with new initial etc.?
    Depends on your version of Oracle. If you are on 8.1.x, you can either set the maxextents to unlimited (ALTER TABLE xyz STORAGE (maxextents UNLIMITED);) or you could "move" the table with larger storage paramters (ALTER TABLE xyz MOVE TABLESPACE same_tbs STORAGE (INITIAL 8M next 8M MAXEXTENTS UNLIMITED);)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    QUESTION:I have a objectname table objx size 72.66 Mbs with initial of 1,048,576 with NUMBER OF EXTENTS 263 and MAX EXTENTS of 505. size 76 megs.

    What I would like to do is decrease the number of extents. I believe If I exp the table compress extents then resize the initial and next then I imp back then my extents should be fine.

    what are the procedures and what do i need to keep in mind?
    "High Salaries = Happiness = Project Success."

  4. #4
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    1. export the table (default will be compress=y)
    2. drop the table
    3. precreate the table with (say) initial=table_size. Check for this space size in the ts, since there will be ts frag. upon dropping the table. If necessary, increase ts size to accomodate initial extent size.
    4. import table with ignore=y
    5. create indexes associated with the table.

  5. #5
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    Halo
    I have over 500 Megs left on the tablespace and since the current initial is 1,048,576 with NUMBER OF EXTENTS 263
    what how big should i make the initial>?
    "High Salaries = Happiness = Project Success."

  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    You can size the initial to be the size of the table itself, or initial=next=(say) half the table size. Another thing to keep in mind is the rate of table growth and setting next and pctinc. accordingly.

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    With the information you provided I would say your tablespace is fragmented.

    Thats depends upon the max chunk size you have on the tablespace. If you do have max free chunk of 25/50 meg you can say your inital extent size equal to 25/50 M.

    Whatever you do, you run outta space if it looks for space requirement in future unless you extend your tablespace size.
    Reddy,Sam

  8. #8
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    Sam you mean Max extents by max chunks?
    Max extents is 505
    "High Salaries = Happiness = Project Success."

  9. #9
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Max chunk meaning the largest free extent size in the ts. If this is relatively small, you might want to:
    1. increase size of ts to have a larger extent OR
    2. set inital=largest free extent size, next=next largest extent size.

  10. #10
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I meant Max free space chunk you have on your tablespace, not max extents.

    If and only if you have that much freespace chunk(you are trying to allocate for your initial extent) your allocation will be succesfull otherwise it can't allocate and you get an error.
    Reddy,Sam

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