Extending tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Extending tablespace

  1. #1
    Join Date
    May 2001
    Posts
    285

    Cool Extending tablespace

    Hi,

    I have a index tablespace which includes 6 different data files on 2 different mount points (Solaris 5.8). 2 of the 6 data files are set to 'autoextend = true', while the others are set to false. Right now, overall this tablespace is about 95% full, while for the 2 autoextendable tablespaces, one is 100% full, the other is 98% full. Most of the available space resides in one of the un-autoextendable tablespace.

    My question is --

    Shall I add a new data file for this tablespace (or increase the size of one/more existing data file), or I should rely on the auto extendable data file to increase its own size when the whole tablespace becomes full?

    Right now for the 6 data files for this tablespace, they are sized around 3000MB to 4000 MB each. So in case I choose to not rely on the data file to extend itself automatically, shall I increase the size of an existing data file or add a new one?

    When there are 6 data files for one tablespace, how are they being used? In a round robin fashion of what? (assuming more than one file has space available).

    Thanks a lot,
    Elaine

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095

    Re: Extending tablespace

    Originally posted by elaine3839

    Shall I add a new data file for this tablespace (or increase the size of one/more existing data file), or I should rely on the auto extendable data file to increase its own size when the whole tablespace becomes full?

    I would set all your datafiles to AUTOEXTEND ON MAXSIZE xxxM where xxx is the maximum size you wish to deal with. For example, I like to keep my datafiles under 2047m or 4095m depending on the app.


    Right now for the 6 data files for this tablespace, they are sized around 3000MB to 4000 MB each. So in case I choose to not rely on the data file to extend itself automatically, shall I increase the size of an existing data file or add a new one?

    personally, I would let the autoextend feature do it's job.


    When there are 6 data files for one tablespace, how are they being used? In a round robin fashion of what? (assuming more than one file has space available).
    Yes, the extents are spread out on all the datafiles if there is free space.

    I like to create my tablespaces with 64m initial size and a defined maxsize and let Oracle handle the space allocation. When the datafiles get close to full, you'll know it and you can add another set of datafiles.

    For example, I'd:
    Code:
    CREATE TABLESPACE myData DATAFILE 
    '/u01/oradata/xyz/mydata01.dbf' SIZE 65600K AUTOEXTEND ON NEXT 64M MAXSIZE 4095M,
    '/u02/oradata/xyz/mydata02.dbf' SIZE 65600K AUTOEXTEND ON NEXT 64M MAXSIZE 4095M,
    '/u03/oradata/xyz/mydata03.dbf' SIZE 65600K AUTOEXTEND ON NEXT 64M MAXSIZE 4095M
    EXTENT MANAGEMENT LOCAL;
    Then, when my datafiles got around 4000M I would start looking to add another three datafiles each starting at 64M. (Of course, I have automated this whole process so I don't every worry about space).
    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
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hi Jeff,
    personally, I would let the autoextend feature do it's job
    Wouldn't you prefer to schedule the RESIZE off-peak?


    BTW, Meatlink on >2GB datafiles:
    http://metalink.oracle.com/metalink/...T&p_id=62427.1

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by DaPi
    Hi Jeff, Wouldn't you prefer to schedule the RESIZE off-peak?
    nope. I just let autoextend do its thing.
    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
    May 2001
    Posts
    285

    thanks Jeff for sharing your data file strategy...

    Here for some reason, people like to turn autoextend off and use foglight to mointor tablespace usage and act accordingly. (Actually, I'm surprised to see the tablespace I mentioned earlier in this post has 2 files set autoextend = TRUE).

    I was told from the people here that it was turn off to prevent the tablespace takes all the disk space, but that was not very convincing to me since if it's going to grow, you have to give it more space anyway, so why do we have to do it manually when Oracle can do it for us? So as far as you know, is there any downside of setting AUTOEXTEND = TRUE?

    Thanks again...

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    That's true. If you have autoextend on and don't give it a maxsize, it could eat up all your disk space. If you typically run with 95% full devices, you would not like autoextend. Generally people don't like autoextend because they are uninformed.
    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."

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