datafile overhead of tablespace using uniform extent size

    Note that there is datafile overhead for tablespaces with uniform size extent allocation .

     i.e, eight 8k blocks used for 8k block_size tablespace and  four 32k blocks for 32k block size tablespace.

    eg. Add a 1000MB datafile to existing 8k block_size tablespace with 100MB uniform extent size tablespace in CUSTPA, the right size should be 1MB*1000+8k*8 =1000Mb+64Kb=1048641536 bytes. 

     -- If we specify right "size 1000m", there will be only 9 x 100Mb extents available, 1 extent with 100Mb is used for overhead (wasted).  

     -- If specify "size 1G" , it is 1024Mb , we waste (24Mb- 64kb). The datafile size should be (round to integer times of uniform extent size + datafile overhead). To make it easy, just plus 1MB for each data file in the tablespace.

    Please take note when extend tablespaces. 

   SYS > show parameter block_size

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
db_block_size                        integer     32768                          

SYS > create tablespace test datafile '/ods001/oradata/ODSDMS/test.dbf' size 100m
  2  extent management local uniform size 1m;

Tablespace created.


SYS > select file#, bytes ,creation_time from v$datafile where file#=27;

     FILE#      BYTES CREATION_                                                 
---------- ---------- ---------                                                 
        27  104857600 27-MAY-11                                                 


SYS > create user test identified by test;

User created.

SYS > grant dba to test;

Grant succeeded.

SYS > alter user test default tablespace test;

User altered.

SYS > connect test/test;
Connected.
TEST > desc dba_free_space
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                                    VARCHAR2(30)
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

TEST > set pages 1000
TEST > select * from dba_free_space where file_id=27;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   27          5  103809024       3168      
          27                                                                    
                                                                                

TEST > select 103809024/1048576 from dual;

103809024/1048576                                                               
-----------------                                                               
               99                                                               

TEST > prompt 1mb is "missing"
1mb is "missing"
TEST > prompt 1mb is the uniform size
1mb is the uniform size
TEST > desc dba_extents;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

TEST > select * from dba_extents where file_id=27;

no rows selected

TEST > prompt not allocated yet
not allocated yet
TEST > select 32*1024*8 overhead_bytes from dual;

OVERHEAD_BYTES                                                                  
--------------                                                                  
        262144                                                                  

TEST > select 104857600+262144 from dual;

104857600+262144                                                                
----------------                                                                
       105119744                                                                

TEST > alter database datafile '/ods001/oradata/ODSDMS/test.dbf' resize 105119744 ;

Database altered.

TEST > select * from dba_free_space where file_id=27;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   27          5  104857600       3200      
          27                                                                    
                                                                                

TEST > prompt 100MB available for allocation
100MB available for allocation

TEST > alter tablespace test add datafile '/ods001/oradata/ODSDMS/test2.dbf' size 10485760;

Tablespace altered.

TEST > select *  from v$datafile where file#=28;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED     
---------- ---------------- --------- ---------- ---------- ------- ----------  
CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER LAST_CHANGE#       
------------------ --------- --------------------- --------- ------------       
LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TI      BYTES     BLOCKS        
--------- --------------- -------------- --------- ---------- ----------        
CREATE_BYTES BLOCK_SIZE                                                         
------------ ----------                                                         
NAME                                                                            
--------------------------------------------------------------------------------
PLUGGED_IN BLOCK1_OFFSET                                                        
---------- -------------                                                        
AUX_NAME                                                                        
--------------------------------------------------------------------------------
FIRST_NONLOGGED_SCN FIRST_NON                                                   
------------------- ---------                                                   
        28         93782698 27-MAY-11          8         28 ONLINE  READ WRITE  
          93782699 27-MAY-11                     0                              
                        0              0             10485760        320        
    10485760      32768                                                         
/ods001/oradata/ODSDMS/test2.dbf                                                
         0         32768                                                        
NONE                                                                            
                  0                                                             
                                                                                

TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   28          5    9437184        288      
          28                                                                    
                                                                                

TEST > select 9437184/1048576 from dual;

9437184/1048576                                                                 
---------------                                                                 
              9                                                                 

                                                                              


TEST > select 32*1024*2+10485760 from dual;

32*1024*2+10485760                                                              
------------------                                                              
          10551296                                                              

TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize  10551296;

Database altered.

TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   28          5    9437184        288      
          28                                                                    
                                                                                
 -- -- 9 x 1MB extents

TEST > select 32*1024*3+10485760 from dual;

32*1024*3+10485760                                                              
------------------                                                              
          10584064                                                              

TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize  10584064 ;

Database altered.

TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   28          5    9437184        288      
          28                                                                    

-- still 9 x 1MB extents                                                                                

TEST > select 32*1024*4+10485760 "extra4blocks" from dual;

extra4blocks                                                                    
------------                                                                    
    10616832                                                                    

TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize  10616832 ;

Database altered.

TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   28          5   10485760        320  


-- 10 x 1MB extents now