4 Replies Latest reply: Dec 30, 2012 12:53 PM by jgarry RSS

    Confusion Regarding Data Storage

    sandy121
      B is a tablespace with two datafiles a and b, both of the datafiles have adequate space in it. Now I am inserting n number of rows into x table which is in B tablespace. Now the newly inserted data will be stored in which datafile?
        • 1. Re: Confusion Regarding Data Storage
          Srini Chavali-Oracle
          Pl post details of OS and database versions. Rows will be inserted wherever space is available (based on an algorithm that Oracle internally determines) - could be either datafile or both datafiles.

          HTH
          Srini
          • 2. Re: Confusion Regarding Data Storage
            EdStevens
            918868 wrote:
            B is a tablespace with two datafiles a and b, both of the datafiles have adequate space in it. Now I am inserting n number of rows into x table which is in B tablespace. Now the newly inserted data will be stored in which datafile?
            Both.

            Remember taht the basic unit of IO is the block. A block holds 'n' number of rows. Since a block is the basic unit of IO, a given block can only exist in a single data file. Oracle allocates space 'extents', which is a number of blocks that are logically contiguous within a data file. So an extent has to exist within one given data file. When all of the blocks in an extent are filled, oracle will allocate another extent. If multiple files are available within a TS, oracle will choose which file gets the new extent. It may decide to fill one file completely before using the next, or it may decide to go 'round-robin' and keep the files relatively balanced.

            Bottom line is you have no control over oracle's decision. Oracle will use space as it is available to the TS, regardless of distribution across multiple files.
            • 3. Re: Confusion Regarding Data Storage
              Osama_Mustafa
              Also check
              http://www.siue.edu/~dbock/cmis565/module4-tablespaces_and_datafiles.htm
              • 4. Re: Confusion Regarding Data Storage
                jgarry
                Ed's reply is generally correct, but you can control which datafile by making the other datafile less attractive to Oracle. The easiest way to do that is not have any space left in the datafile. You can also do strange things like pre-extend one datafile to have lots of space with no auto-extend, and have the other one be smaller with little space, auto-extend, with a max of the same size as the other data file. Oracle will use the one with empty space first, as opposed to normally doing round-robin with similar data files. In general, it makes little sense to try to control things like this, although it might if you have very different devices. I use the effect as a size warning - when the small one starts to extend, I make it bigger and make another small one (after shrinking any tables that may benefit). YMMV.

                If you have a specific situation, test it. Sometimes results are unexpected and interesting.

                (Edited to clarify normality)

                Edited by: jgarry on Dec 30, 2012 10:53 AM