changing the storage parameter next
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: changing the storage parameter next

  1. #1
    Join Date
    Jan 2001
    Posts
    230
    Hi:

    In one of our production database has storage problem.
    Tablespace was created with intial 500M next 2M, due to the next extent
    size we have so many extents.

    I know one of the solution for this
    1)get the tablename which are in this tablespace.
    2)export those tables.
    3)take the tablespace offline.
    4)drop the tablespace
    5)create a new tablespace with new storage parameter
    6)import those tables.

    If I change the next extent then it will affect when object need more space
    but it will not change the used extent.

    Is there any better way to change the storage parameter?

    How about TRASPORT_TABLESPACE? will it work?

    Let me know.

    Thanks.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    see [url]http://www.dbasupport.com/forums/showthread.php?threadid=5648[/url]
    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
    Jan 2001
    Posts
    230
    Hi:

    I have a problem with tablespace storage. next extent is 1M so I have somany extents.
    In order to reduce the number of extent and improve the perf. I need to change my
    next extent size. I know I can change the new extent which will be effected only for
    new next extent but no the exist one. Objects in this tablespace use the default parameter
    from tablespace.

    As you suggested, recreate a new tablespace with new storage parameter then move
    the tables to this new tablespace one-by-one but if I have 50 tables then this process
    will take more time. Also, when I move the table what happened to my extents?
    extent are going to be recreated as new tablespace storage parameter?

    Once I move all the tables then drop the old tablespace and rename the new
    tablespace to old tablespace name.

    Is there any easier way to do this because I have more then 400 objects and
    more then 50 tablespace. Out 50 tablespace I have problem with about 30 tablespace.

    TRANSPORT_TABLESPACE will it work?

    Thanks.



  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Are you worried about the "extents" of the tablespace? A tablespace doesn't work like a table. If your tablespace extends, you don't have multiple extents, your datafile just gets bigger. Having a NEXT 1M parameter for the autoextend is not a problem.
    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."

  5. #5
    Join Date
    Jan 2001
    Posts
    230
    Hi

    If you know any thing about this, please let me know.

    Thanks.

  6. #6
    Join Date
    Jan 2001
    Posts
    230
    Hi Jeff:

    If I don't specify the storage parameter for table then it will take tablespace's storage parameter
    as default storage parameter. Once the intial extents fills up then it will take next extent (next extent size is 1M). So, I am having so much extents in my production database. As far as I know
    having a so many extent can impact on performance.

    So, I want to change the next extent size big enough so I wan't have so many extents.
    Also, I want to reduce the number of extents which I have at this point.

    If I am going to use tablespace's storage parameter as default then I need to
    change the next extent size. Otherwise I have to create an object with it's own
    storage parameter to overwrite tablespace storage parameter.

    Let me know.

    Thanks for your help.



  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    To change the default storage parameter for a tablespace:
    alter tablespace xyz default storage (initial n next n ...)

    However, this will not change any existing structures in the database. There, you will have to either export/import or move them.
    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."

  8. #8
    Join Date
    Jan 2001
    Posts
    230

    Jeff:

    Thanks.

    How TRANSPORT_TABLESPACE will work?


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