DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: changing locallay managed extent size...

  1. #1
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330

    changing locallay managed extent size...

    Hi All,
    Created tablespace XYZ with
    extent management local uniform size 128M.

    Now application is asking to change the extent size to 20M. How to do that?. I am not able to alter the tablespace... help is appreciated..

    Oracle version :9.2.0.4.0
    SunOS :8.
    Thanigaivasan

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166

    Re: changing locallay managed extent size...

    Originally posted by thanigai
    Hi All,
    Created tablespace XYZ with
    extent management local uniform size 128M.

    Now application is asking to change the extent size to 20M. How to do that?. I am not able to alter the tablespace... help is appreciated..

    Oracle version :9.2.0.4.0
    SunOS :8.
    You could create a new tablespace with 20M extent sizes and then move the tables to the new tablespace. Once you have moved all of the tables out of the original tablespace, you can then drop it if you like.

  3. #3
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    Thanks for the update...
    IS there any other way to change this extent size...?.
    can we change the tablespace permanent and alter the value?.
    may be I am wrong?. Please help.


    Thanks,
    Thanigaivasan

  4. #4
    Join Date
    May 2001
    Posts
    736
    If u have gone through some reading on this extent management u willn't ask this question.There is no alternative except what mentioned by gandolf.

  5. #5
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    I strongly agree...
    I am confused after seeing the block diagram for (SYNTAX ) alter tablespace tablespace (all possible statements/commands)....
    and I did not try the command before either..
    hence questioned.. Thanks for pinching my Brain.




    Thanks,
    Thanigaivasan

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    It can be done in 5 steps.

    Change the LMT to DMT.
    Alter the minimum extent length for the tablespace.
    Move tables with new storage parameters with in the tablespace.
    Ensure that all extents in 20MB size.
    Chnage DMT to LMT.

    The qn is : Is it worth to do so?

    Tamil

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by tamilselvan
    It can be done in 5 steps.

    Change the LMT to DMT.
    Alter the minimum extent length for the tablespace.
    Move tables with new storage parameters with in the tablespace.
    Ensure that all extents in 20MB size.
    Chnage DMT to LMT.
    Even if it works, how is that easier than creating a new tablespace and doing it in three steps?

    Originally posted by tamilselvan
    The qn is : Is it worth to do so?
    This would really depend on the amount of data. If the database is using 120mb extents and they don't have anywhere near 120 mb in any given table, then yes it would be worth doing. If all of the tables in that tablespace are much larger than 120 mb, then I'm not sure if it is worth while.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    please read Jurij's sig.. LOL

    Abhay.
    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"

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by gandolf989
    Even if it works, how is that easier than creating a new tablespace and doing it in three steps?
    It would make sense if you didn't have the disk space to create the new TS.

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by DaPi
    It would make sense if you didn't have the disk space to create the new TS.
    But if you were really lacking in disk space then it would make sense to get another set of disks and create a raid, then you would not have to worry as much about not having space, plus you would then split your data among more spindles. Of course the real answer is somewhat specific to the environment that the database is in.

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