Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    56

    Question Unanswered: Tablespace growth and management

    Hi I am curious to find out more information explaining exactly how tablespaces actually grow. I will explain what I understand thus far, and hopefully someone can fill in the gaps:

    CREATE TABLESPACE test
    DATAFILE 'x:\database\test.dbf' SIZE 50M
    DEFAULT STORAGE (
    INITIAL 50K
    NEXT 50K
    MINEXTENTS 2
    MAXEXTENTS 50
    PCTINCREASE 0);

    Once this information has been specified, the test.dbf file is created of size 50M. The initial extent inside this table space is 50K. Once this initial extent fills up, then another one is created of size 50K and so on and so on untill either max extents fills up or the physical 50M is reached (size specified during creation). I believe that I understand this part ok.

    What I am unsure of is, we have a database here where a number of the table spaces are a lot larger than what they were when created. Is it possible for tablespaces to also autoextend? If so, is this a setting by default or something that needs to be specified during the actual creation of the tablespace?

    Basically I want to cap these tables spaces to be of a specific size and not to grow past this size unless I specify so (physically resize them).

    Can some one let me know if I am on the right track with my thinking and what the easiest way to do this would be?

    Cheers

    Dan.
    Daniel Buckingham
    Technical Consultant
    Mercia Software

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Dear dbuckingham,

    I guess you have some misunderstanding about the Oracle Tablespaces.
    Let me explain you how tablespace works.y whenever you create a tablespae, the datafile(s) that belongs to it will be allocated a header and Unallocated space (i.e, Free space). Extents will not be allocated here. They will be allocated when you create a segment(s) in this tablespace.
    For e.g.
    Your code creates a TEST tablespace with only one datafile test.dbf.
    The size of the datafile is 50M. Now at this moment No extent is allocated and they will be allocated when you create segments in it.

    To manage the tablespace Please read the Oracle Documentation. You will find lot of tricks to manage them.

    Thanks

    Hings

    ================

    Still Learning...

  3. #3
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146

    Re: Tablespace growth and management

    Originally posted by dbuckingham
    Hi I am curious to find out more information explaining exactly how tablespaces actually grow. I will explain what I understand thus far, and hopefully someone can fill in the gaps:

    CREATE TABLESPACE test
    DATAFILE 'x:\database\test.dbf' SIZE 50M
    DEFAULT STORAGE (
    INITIAL 50K
    NEXT 50K
    MINEXTENTS 2
    MAXEXTENTS 50
    PCTINCREASE 0);

    Once this information has been specified, the test.dbf file is created of size 50M. The initial extent inside this table space is 50K. Once this initial extent fills up, then another one is created of size 50K and so on and so on untill either max extents fills up or the physical 50M is reached (size specified during creation). I believe that I understand this part ok.

    What I am unsure of is, we have a database here where a number of the table spaces are a lot larger than what they were when created. Is it possible for tablespaces to also autoextend? If so, is this a setting by default or something that needs to be specified during the actual creation of the tablespace?

    Basically I want to cap these tables spaces to be of a specific size and not to grow past this size unless I specify so (physically resize them).

    Can some one let me know if I am on the right track with my thinking and what the easiest way to do this would be?

    Cheers

    Dan.

    Hi Dan,
    You can have auto-extend feature for tablespace which will manage & grow as per the defined way while creating it.

    You may use AUTO EXTEND ON NEXT 50K MAX SIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

    This will increase the extent by 50K when tablespace filled-up. I will keep on growing. You have be sure that you are having proper free space on your disk for tablespace datafile. Or yu may even limit the Max Size parameter. Plesae check the Doc. about this command.

    May it help you.

    Regards,
    - KR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •