This discussion is archived
10 Replies Latest reply: Dec 24, 2008 7:52 PM by Aman.... RSS

tablespaces or datafile

650105 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points