2 Datafiles in 1 Tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: 2 Datafiles in 1 Tablespace

  1. #1
    Join Date
    Sep 2008
    Posts
    3

    2 Datafiles in 1 Tablespace

    I just want to clarify things about the following matters:

    1) Is it possible to have two datafiles in one tablespace where one of the datafiles in Autoextensible while the other one is not.

    2) If #1 is ppossible, how would the datafiles behave. Does this mean that the datafile which is autoextended will just continue to grow and the other datafile will never be used.

    Your inputs will be highly appreciated.

  2. #2
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Yes, you can add more than one datafiles to a tablespace, you can make one datafile autoextend on and the other off...

    for example

    ALTER TABLESPACE users
    ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
    AUTOEXTEND ON
    NEXT 512K
    MAXSIZE 250M;

    The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.

    disables the automatic extension for the datafile.

    ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND OFF;

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Answering your questions...

    1) Yes.
    2) Not necessarily. It dependes of Oracle determination of the candidate datafile that happens each time space has to be allocated. Last time I checked the "candidate datafile determining" algorithm was propietary -meaning is not published - but testing suggested it was sort of an altered round-robin process... being the "altered" part the unpublished one. By the way, if somebody out there has specifics on this matter it would be nice to hear from you.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Sep 2008
    Posts
    3
    Quote Originally Posted by PAVB
    Answering your questions...

    1) Yes.
    2) Not necessarily. It dependes of Oracle determination of the candidate datafile that happens each time space has to be allocated. Last time I checked the "candidate datafile determining" algorithm was propietary -meaning is not published - but testing suggested it was sort of an altered round-robin process... being the "altered" part the unpublished one. By the way, if somebody out there has specifics on this matter it would be nice to hear from you.
    Does this mean that you do not have control of whether data will be put to Datafile1 (autoextended datafile) or Datafile2 (not autoextended)? So it could happen that even though Datafile1 is not yet full, there is still the possibility that it will already start writing on Datafile2?

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Exactly, that's the beauty of it.

    If you want to have control over where data is written you create just one non-extensible datafile then when this one is close to be full you add a new datafile to the tablespace...
    OR
    ...you go back in time to VSAM architecture
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Sep 2008
    Posts
    3
    Quote Originally Posted by PAVB
    Exactly, that's the beauty of it.

    If you want to have control over where data is written you create just one non-extensible datafile then when this one is close to be full you add a new datafile to the tablespace...
    OR
    ...you go back in time to VSAM architecture

    Thanks for the input. Just wanted to clarify how datafiles behave... Anyway, after all, the main concern with database administration is to have enough space readily available to hold data. I just came across this issue when our senior DBA asked me to act on a tablespace which is getting full. I examined the datafile and found out that is autoextended. He insist to add another datafile. But as far as I am concerned, there's no need to worry since the existing datafile is already autoextended and will continue to grow if space is needed.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by iopunx02
    ...after all, the main concern with database administration is to have enough space readily available to hold data.
    In my personal opinion backup/recovery strategy should be a higher concern.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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