Printable View
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.
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
I have checked it - no info in any Oracle docs.
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.
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) ?
-- 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)
;
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