AUTOEXTEND - what Happens?
Hi,
I have a general question regarding what happens when Oracle decides it's time to AUTOEXTEND a datafile. Here's the scenario:
I have a single-table tablespace. I know that the initial load of data into the table will leave me with a table that is 10Gb in size. I also know that over the next two years this table will grow to be 150Gb. So, I create a tablespace for this table with 10 data files, each with an initial size of 1Gb. I set AUTOEXTEND ON for all of the data files, with an increment of 1GB and a max size of 15Gb. So, this takes care of my initial and projected data volumes.
Now, as far as I know, when Oracle is placing data in the table (either during the initial data load or during normal operation after go-live) it will try and "Stripe" the data across the available data files. So, in this case, the data would be evenly spread across the 10 data files. However, what happens when I need to add a row to the table, and Oracle recognises that there isn't enough space left in any of the data files? Will Oracle AUTOEXTEND just one datafile and put the row in there? Or will it AUTOEXTEND all 10 data files at the same time. If it does all 10, then that's great, and I'm happy, because I can keep the "striping". However, if it does just one, does that mean that all subsequent inserts into the table will be placed in that one data file, or is Oracle clever enough to know that it CAN extend the other data files, and so keep the striping?
Sorry this is such a long question - I wanted to make sure you all understood where I was coming from. If you have any insights into how this works, then I'd be extremely appreciative.
Cheers,
Kevin Callaghan