RE: datafiles space allocation algorithm

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 6 Oct 2006 06:24:37 -0400

What manner of technology was the load? How many rows?


With the answers to those questions and a query including the file numbers
for the datafiles and another with the rowid broken into the useful pieces
and another adding up vsize(column_name) for each column in the table, plus
an assurance that you don't have any columns that might be stored "out of
line", we can answer your question. (Or the answer will become obvious to
you as the query result spills out.)


Computing statistics on the table in question might also be useful if you're
on a release that has statistics. You have tablespaces, so I'm pretty sure
you're at least on Oracle Version 6.0, unless of course you aliased your
column names in the query below.


From the data below you might only have two rows..




PS: If you want to see where Oracle puts each row and the order for sure,
you're going to need the logical row number in the data you are loading.
Then the answer of where Oracle stashed each row will be truly obvious if
you select the useful rowid bits and the logical row number order by logical
row number. (Not rownum, but an actual column added to the table containing
an ordered numeric sequence. Single stride ascending integers would be most
user friendly, and you might want to start with 10001 for reasons beyond the
scope of this thread. Or you could start with 42.



From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of amonte
Sent: Friday, October 06, 2006 4:53 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: datafiles space allocation algorithm



Suppose we have 4 datafiles in a tablespace, when we load data into this
tablespace how will Oracle assign the space? I thought it would use
round-robin like but it does not.

Before load I had datafiles which looked like this: 

------------------------------------------------ ----------
PM_IND_T01                    /u02/oradata/vmw1020/PM_IND_T01_SIMP001.dbf
PM_IND_T01                    /u02/oradata/vmw1020/PM_IND_T01_SIMP002.dbf

After load it shows this:

------------------------------------------------------------ ----------
PM_IND_T01                    /u02/oradata/vmw1020/PM_IND_T01_SIMP001.dbf
PM_IND_T01                    /u02/oradata/vmw1020/PM_IND_T01_SIMP002.dbf

How does this work?



Other related posts: