Home > Workload Solutions > Oracle > White Papers > Oracle ASM on ScaleIO Best Practices > Database/tablespace block size
The basic unit of storage in any Oracle database is the database block. Before Oracle 9i, the block size was fixed for the entire database at creation time, but with 9i Oracle introduced the ability to use different block sizes for each tablespace.
Small block sizes such as 2K or 4K mean that relatively large amounts of each block are wasted for metadata, such as the block header and tail check.
Large block sizes result in wasted storage and memory, since entire blocks have to be brought into the SGA block cache for a single row requested by a user.
In addition, on RAC systems, larger block sizes increase the likelihood of inter-node block contention where users selecting different rows of data on different RAC nodes, find they both need the same physical block.
Selecting a block size that is too small may also result in ORA-01450 maximum key length exceeded errors, where root index blocks of composite indexes can no longer fit into a single tablespace block.
Block size also affects the total amount of data that an Oracle database can store.
As of Oracle 10g, each data file of the database can store 4 billion blocks, and each database can have up to 65,536 files.
With these limits, the maximum database size is shown for each block size in Table 9.
Table 9. Maximum datafile and database size
Block size |
Maximum datafile size |
Maximum database size |
2 K |
8 GB |
512 TB |
4 K |
16 GB |
1 PB |
8 K |
32 GB |
2 PB |
16 K |
64 GB |
4 PB |
32 K |
128 GB |
8 PB |
With Oracle 10g a new feature was introduced called BIGFILES.
BIGFILE tablespaces allow for one very large datafile per tablespace instead of multiple smaller files. This can simplify management, facilitate larger databases and improve performance since checkpoint operations no longer have to update so many data file headers.
Note that BIGFILE datafiles can be backed up by RMAN using multiple channels simultaneously. The amount of data backed up by each channel is controlled by the RMAN SECTIONSIZE parameter.
This is an example of a BIGFILE tablespace:
SQL> create bigfile tablespace my_large_data
2 datafile '+MYDATA' size 1024G
3 blocksize 8K;
Tablespace created.
With this option, maximum datafile and database sizes increase as shown in Table 10.
Table 10. Maximum datafile and database size
Block size |
Maximum datafile size |
Maximum database size |
2 K |
8,192 GB |
536 PB |
4 K |
16,384 GB |
1,073 PB |
8 K |
32,768 GB |
2,147 PB |
16 K |
65,536 GB |
4,294 PB |
32 K |
131,072 GB |
8,589 PB |
Dell EMC recommends an 8 K block size for OLTP applications including Oracle E-Business Suite and SAP. OLAP and Data Warehouse applications should consider a 16 K or 32 K block size.
Dell EMC recommends the use of BIGFILEs where large amounts of data will be stored.
Mixed block sizes are also recommended where applicable, provided there is enough SGA to create buffer caches for each block size in the database.