DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Autoallocate issue.

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    22

    Exclamation

    Hello all.
    Is there someone who knows (or has doc)
    how exactly Oracle decides which
    datafile to extend if the tablespace
    have more than one datafile.
    Example :
    Tablespace TS1 has 3 datafiles :
    data1.dbf - 10M free
    data2.dbf - 20M free
    data3.dbf - 15M free

    Autoextend is enabled for the datafiles.
    EMP table wants to allocate next extent of 50M.

    Which datafile oracle will choose to
    extend and why ?

    Good luck.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you could try oracle documentation, Oracle 8i concepts chapter 4

    4 Data Blocks, Extents, and Segments
    Introduction to Data Blocks, Extents, and Segments
    Data Blocks
    Data Block Format
    An Introduction to PCTFREE, PCTUSED, and Row Chaining
    Extents
    When Extents Are Allocated
    Determining the Number and Size of Extents
    How Extents Are Allocated
    When Extents Are Deallocated



  3. #3
    Join Date
    Nov 2000
    Posts
    22
    I have checked it - no info in any Oracle docs.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Step 1:
    Oracle will first check on the 1st data (relative file number to the tablespace) file whether it has already its maxsize reached, if it is not, then it will extend its size based on the autoextend next value.

    Step 2:
    If step 1 fails, then it will try on the 2nd file.

    Step 3.
    If the step 2 fails, it will try on the 3rd file.

    Step 4.
    If steps 1,2,3 fail, it will report an error.



  5. #5
    Join Date
    Nov 2000
    Posts
    22
    Thanks a lot for the info.

    SGA.

    P.S - Do you have any sql script
    that checks for a next extent failure
    (including autoextend on datafiles check) ?

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    -- next_extent_will_fail.sql
    SELECT TABLESPACE_NAME, EXTENTS, NEXT_EXTENT , SEGMENT_NAME, SEGMENT_TYPE
    FROM DBA_SEGMENTS DS
    WHERE NEXT_EXTENT > ( SELECT MAX(BYTES) FROM DBA_FREE_SPACE DF
    WHERE DS.TABLESPACE_NAME = DF.TABLESPACE_NAME)
    ;

  7. #7
    Join Date
    Nov 2000
    Posts
    22
    Hello tamilselvan,
    Someone from experts-exchange.com wrote me
    different answer than you :

    The server builds an array of files for the tablespace whose autoextend parameter is set. The files
    are stored in descending order of the calculation ( maxsize - currentsize ). Then it scans down this
    array until the NEXT value is smaller than the difference determined. This file will be the one that
    Oracle server chooses to extend.


    What do you say ?
    SGA

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