10 Replies Latest reply: Dec 24, 2008 9:52 PM by Aman.... RSS

    tablespaces or datafile

    650105
      hi if we have a tablespace like user with 4 datafile all datafile size 500mb and auto extent unlimited. my question is how oracle deal with datafile. how much it fill the datafile to switch second one if first datafile reached 500MB what oracle does switch to second one or extent the first one. if after 500MB it switch to 2nd and 3rd so on when i will come to first how much it extent and move to second.
      if we already know our data is so huge and take so much space in datafile 100GB or over. what size we set to the datfile. or did we use big file for large database
        • 1. Re: tablespaces or datafile
          Mahesh Menon
          If you have more than one datafiles in a tablespace, Oracle will stripe whatever data is stored in that tablespace to different datafiles. That is managed by Oracle automatically.
          When a datafile becomes full, Oracle automatically adds one more extent to that datafile which is of size 1MB,(unless extent size specified), since automatic extension is provided to the datafile.

          If you create a bigfile tablespace, then you can extend the datafile to a bigger size, but you cannot add datafile to a bigfile tablespace.

          Edited by: Street Hawk on Dec 23, 2008 2:18 PM
          • 2. Re: tablespaces or datafile
            NavneetU
            Oracle manages the space using Blocks and Extents not datafiles. It will look for the next available block and then write to it. now oracle dont bother that to which file that block belongs. once the datafile gets completly filled then it will not extend the file untill it is needed in the tablespace.

            regards
            • 3. Re: tablespaces or datafile
              Aman....
              Oracle Studnet wrote:
              hi if we have a tablespace like user with 4 datafile all datafile size 500mb and auto extent unlimited. my question is how oracle deal with datafile. how much it fill the datafile to switch second one if first datafile reached 500MB what oracle does switch to second one or extent the first one. if after 500MB it switch to 2nd and 3rd so on when i will come to first how much it extent and move to second.
              if we already know our data is so huge and take so much space in datafile 100GB or over. what size we set to the datfile. or did we use big file for large database
              Adeel,
              Oracle allocates the data in the files in the round robin fashion. Means when the extents will be allocated, it will be in a round robin fashion among all the datafiles. Both the files will be extended simultaneously by Oracle and if it can't grow anyone, it will throw the error.See a small demo
              Rem--I am creating a tablespace with 2 datafiles which are intially of 5m in size and can grow till 10m.I shall create a big table and I shall move the table into it. 
              SQL> create tablespace test_t1 datafile 'd:\test1.dbf' size 5m autoextend on next 1m maxsize 10m;
              
              Tablespace created.
              
              SQL> alter tablespace test_t1 add datafile 'd:\test2.dbf' size 5m autoextend on next 1m maxsize 10m;
              
              Tablespace altered.
              
              SQL> create table t as select * from aman.t;
              
              Table created.
              
              SQL> insert into t select * from t;
              
              68036 rows created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> insert into t select * from t;
              
              136072 rows created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> insert into t select * from t;
              
              272144 rows created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> insert into t select * from t;
              
              544288 rows created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> alter table t move tablespace test_t1;
              alter table t move tablespace test_t1
                          *
              ERROR at line 1:
              ORA-01652: unable to extend temp segment by 128 in tablespace TEST_T1
              
              
              SQL>
              In another session, I have the files of the tablespace listed with the blocks and bytes in them. You can see that when I moved my table in it, the number of occupied blocks are same within both the files. And it went on increasing in the parallel manner.When I got the error, I got it for the entire tablepsce which is mentioned above.
              SQL> select blocks, bytes,file_id from dba_data_files where tablespace_name='TEST_T1';
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                     640    5242880          8
                     640    5242880          9
              
              SQL> select blocks, bytes,file_id from dba_data_files where tablespace_name='TEST_T1';
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                     640    5242880          8
                     640    5242880          9
              
              SQL> select blocks, bytes,file_id from dba_data_files where tablespace_name='TEST_T1';
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                     640    5242880          8
                     640    5242880          9
              
              SQL> select blocks, bytes,file_id from dba_data_files where tablespace_name='TEST_T1';
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                     640    5242880          8
                     640    5242880          9
              
              SQL> select blocks, bytes,file_id from dba_data_files where tablespace_name='TEST_T1';
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                     640    5242880          8
                     640    5242880          9
              
              SQL> select blocks, bytes,file_id from dba_data_files where tablespace_name='TEST_T1';
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                     640    5242880          8
                     640    5242880          9
              
              SQL> select blocks, bytes,file_id from dba_data_files where tablespace_name='TEST_T1';
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                     640    5242880          8
                     640    5242880          9
              
              SQL> select blocks, bytes,file_id from dba_data_files where tablespace_name='TEST_T1';
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                     640    5242880          8
                     640    5242880          9
              
              SQL> /
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                     896    7340032          8
                     896    7340032          9
              
              SQL> /
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                    1152    9437184          8
                    1152    9437184          9
              
              SQL> /
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                    1280   10485760          8
                    1280   10485760          9
              
              SQL> /
              
                  BLOCKS      BYTES    FILE_ID
              ---------- ---------- ----------
                    1280   10485760          8
                    1280   10485760          9
              
              SQL>
              About the anticipation of the large database and file increase, it depends actually how you want to manage it.In one about 50TB of database, I had seen the space being allocated manually by the dbas. They didn't use Bigfile. If you want to make things simple, surely you can go ahead and use it.
              HTH
              Aman....
              • 4. Re: tablespaces or datafile
                EdStevens
                Aman,

                You beat me to it. I just drug up an old test I had put together to demo the same concept, but coming from a slightly different angle. For those interested, here's my version. Note the use of rowid as a means of tracking where things went.
                SQL> --
                SQL> -- create a multi-file tablespace
                SQL> --
                SQL> create SMALLFILE tablespace bubba_ts
                  2           datafile 'c:\oradata01\edst\bubbatbs_01.dbf'
                  3                   size 1m
                  4                   autoextend off,
                  5                 'c:\oradata01\edst\bubbatbs_02.dbf'
                  6                   size 1m
                  7                   autoextend off,
                  8                 'c:\oradata01\edst\bubbatbs_03.dbf'
                  9                   size 1m
                 10                   autoextend off
                 11            EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;
                
                Tablespace created.
                
                SQL> --
                SQL> -- create a test user
                SQL> --
                SQL> create user bubba
                  2            identified by bubbapw
                  3            default tablespace bubba_ts
                  4            temporary tablespace temp
                  5            quota unlimited on bubba_ts;
                
                User created.
                
                SQL> grant create session,
                  2          create table
                  3    to bubba;
                
                Grant succeeded.
                
                SQL> --
                SQL> -- create a test table
                SQL> --
                SQL> create table bubba.rowid_test
                  2            (
                  3            key_col number,
                  4            big_col1 char(2000),
                  5            big_col2 char(2000),
                  6            big_col3 char(2000)
                  7            );
                
                Table created.
                
                SQL> --
                SQL> -- load the table with enough data
                SQL> -- to go several extents
                SQL> --
                SQL> BEGIN
                  2       for i in 1..100 loop
                  3          insert into bubba.rowid_test
                  4             values (i,
                  5                  'xxxxx',
                  6                  'xxxxx',
                  7                  'xxxxx'
                  8                  );
                  9       end loop;
                 10  END;
                 11  /
                
                PL/SQL procedure successfully completed.
                
                SQL> --
                SQL> -- check results
                SQL> --
                SQL> prompt Result set 1
                Result set 1
                SQL> select KEY_COL,
                  2           DBMS_ROWID.ROWID_RELATIVE_FNO(rowid, 'SMALLFILE') fileno,
                  3           DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) blockno,
                  4           DBMS_ROWID.ROWID_ROW_NUMBER(rowid) rowno
                  5  from bubba.rowid_test
                  6  order by 1,2,3,4;
                
                   KEY_COL     FILENO    BLOCKNO      ROWNO
                ---------- ---------- ---------- ----------
                         1          8         16          0
                         2          8         12          0
                         3          8         13          0
                         4          8         14          0
                         5          8         15          0
                         6          9          9          0
                         7          9         13          0
                         8          9         14          0
                         9          9         15          0
                        10          9         16          0
                        11          9         10          0
                        12          9         11          0
                        13          9         12          0
                        14         10         13          0
                        15         10         14          0
                        16         10         15          0
                        17         10         16          0
                        18         10         10          0
                        19         10         11          0
                        20         10         12          0
                <snip the rest, you see the pattern>
                
                100 rows selected.
                
                SQL> --
                SQL> -- check results
                SQL> --
                SQL> prompt Result set 5
                Result set 5
                SQL> col file_name for a35
                SQL> select e.extent_id,
                  2           e.file_id,
                  3           f.file_name
                  4  from dba_extents e,
                  5         dba_data_files f
                  6  where e.owner = 'BUBBA'
                  7  and   e.segment_name = 'ROWID_TEST'
                  8  and   e.file_id = f.file_id
                  9  order by e.extent_id
                 10  ;
                
                 EXTENT_ID    FILE_ID FILE_NAME
                ---------- ---------- -----------------------------------
                         0          8 C:\ORADATA01\EDST\BUBBATBS_01.DBF
                         1          9 C:\ORADATA01\EDST\BUBBATBS_02.DBF
                         2         10 C:\ORADATA01\EDST\BUBBATBS_03.DBF
                         3          8 C:\ORADATA01\EDST\BUBBATBS_01.DBF
                         4          9 C:\ORADATA01\EDST\BUBBATBS_02.DBF
                         5         10 C:\ORADATA01\EDST\BUBBATBS_03.DBF
                         6          8 C:\ORADATA01\EDST\BUBBATBS_01.DBF
                         7          9 C:\ORADATA01\EDST\BUBBATBS_02.DBF
                         8         10 C:\ORADATA01\EDST\BUBBATBS_03.DBF
                         9          8 C:\ORADATA01\EDST\BUBBATBS_01.DBF
                        10          9 C:\ORADATA01\EDST\BUBBATBS_02.DBF
                        11         10 C:\ORADATA01\EDST\BUBBATBS_03.DBF
                        12          8 C:\ORADATA01\EDST\BUBBATBS_01.DBF
                        13          9 C:\ORADATA01\EDST\BUBBATBS_02.DBF
                
                14 rows selected.
                • 5. Re: tablespaces or datafile
                  EdStevens
                  I would take exception to your use of the word 'striping' in this case. As Aman and I have both demonstrated (using different techniques) it is not striping data across files, but simply selecting in round-robin fashion which file will be used for the next block. Striping would indicate that a block itself is systematically written across multiple files. remember, a block is the unit with which Oracle performs actual disk i/o (well, requests it of the OS)
                  • 6. Re: tablespaces or datafile
                    Aman....
                    Stevens,
                    HAHA no such thing that beat you.Infact I was thinking about creating a similar sort of test but than thought that I would let it be simple. Yours is a more refined version I would say :-).
                    Regards
                    Aman....
                    • 7. Re: tablespaces or datafile
                      289595
                      Hi,
                      Although not directly related, but an useful information.

                      If a datafile has autoextend ON, it doesn't mean it will keep growing indefinitely. Actually, it all depends on the DB_BLOCK_SIZE. Oracle limits to roughly 4 million data blocks per datafile. Therefore, if the block size is 2K, the datafile max size will be 8GB, with 4 K it will be 16GB and so on.

                      Thanks and regards,

                      Santosh.
                      • 8. Re: tablespaces or datafile
                        Mahesh Menon
                        Thank you for the correction...

                        But in 9i,, I remember I studied an a chapter (Oracle University Course Material),
                        Stripe the table data
                        You can use any of three methods
                        *1.You can use Operating system striping*
                        *2.RAID*
                        *3.Add datafiles to tablespace*

                        Could you please explain, why Oracle University is teaching like that...If what I mentioned was wrong....

                        Edited by: Street Hawk on Dec 24, 2008 9:01 AM
                        • 9. Re: tablespaces or datafile
                          EdStevens
                          Street Hawk wrote:
                          Thank you for the correction...

                          But in 9i,, I remember I studied an a chapter (Oracle University Course Material),
                          Stripe the table data
                          You can use any of three methods
                          *1.You can use Operating system striping*
                          *2.RAID*
                          *3.Add datafiles to tablespace*

                          Could you please explain, why Oracle University is teaching like that...If what I mentioned was wrong....

                          Edited by: Street Hawk on Dec 24, 2008 9:01 AM
                          I have no idea why that is in the teaching materials. You'd have to ask someone involved with the development of those materials. All I can say is that even teaching materials can be wrong. Clearly, adding a file to the ts is not the same, does not have the same effect or implications, as OS striping or RAID.

                          In fact, I'm not sure I know what they mean by "Operating system striping", as opposed to RAID. I've worked with RAID that is implemented in the disk controller firmware, or the SAN controller, or even software that was part of the OS (this in IBM's OS2), but even that was RAID, just implemented at a different layer.
                          • 10. Re: tablespaces or datafile
                            Aman....
                            Street Hawk wrote:
                            Thank you for the correction...

                            But in 9i,, I remember I studied an a chapter (Oracle University Course Material),
                            Stripe the table data
                            You can use any of three methods
                            *1.You can use Operating system striping*
                            *2.RAID*
                            *3.Add datafiles to tablespace*

                            Could you please explain, why Oracle University is teaching like that...If what I mentioned was wrong....

                            Edited by: Street Hawk on Dec 24, 2008 9:01 AM
                            OU books, in general are not incorrect. If you can tell me which module's book it is mentioned , probably I can also look at it. The interpretation of the last line , IMO is that the extent allocation is going to be in the round robin fashion.So if you have , for example a tablespace TEST having 6 files , each on a different disk, the extent allocation policy of ORaclewill allocate extents in each of the tablespace in a round robin.Now as the data files are on different disks, so when the extents are accessed, in a way, all the disks IO capabilities are given to you which may add up to your performance. If you put up all the files in one disk, this will not be there as the IO will be limited to a single disk and its power only.
                            That's my guess for the last line but its always better to ask the instructor when you are attending the class. Generally OU books tell a lot in a small para/portion so an explanation is needed most of the times.
                            HTH
                            Aman....