Setting the Block Size and Choosing an Appropriate Block Size for Your Database

One of the most consequential decisions you face when creating a new database is the database block size. The block is the unit Oracle actually performs I/O in, data is always read and written a block at a time, never in smaller pieces. The db_block_size parameter controls this, and it matters more than most initialization parameters because of one hard constraint: once the database exists, this value cannot be changed. If you later decide you need a different block size, your only real option is creating an entirely new database and migrating everything into it.

The default value is 8192 bytes (8 KB), and the valid range is 2048 to 32768 bytes (2 KB to 32 KB), though your specific operating system may impose a narrower range than that. Whatever value you choose must be a multiple of your operating system's own block size at the device level, if your OS block size is 512 bytes, a database block size of 4096 (512 × 8) works cleanly, while something like 3,723 bytes, not evenly divisible by 512, would actively hurt performance rather than just being an odd choice.
Oracle's own documentation calls out 4096 and 8192 as the two typical values in practice. Which one fits your situation depends on workload:

• OLTP and general-purpose workloads (lots of small, random row-level access): tend to do well at the default, 8 KB. Going smaller rarely pays off on modern storage, the I/O overhead per block outweighs any benefit.
• DSS and data warehouse workloads (sequential scans, large full-table reads): tend to benefit from larger blocks, 16 KB or 32 KB, since each I/O operation pulls more useful data per trip to disk.
• Large rows or LOB columns (BLOBs, CLOBs): also tend to favor larger blocks, which reduce row chaining and improve how efficiently large objects get chunked and stored.

Every block carries a fixed amount of housekeeping overhead regardless of size, so larger blocks devote a greater percentage of their space to actual data relative to that overhead. That's the core tradeoff: bigger blocks are generally more I/O-efficient for large scans, smaller blocks are generally more efficient when you have heavy, small, concurrent updates competing for the same data.

Since every Oracle database today is a multitenant container database, block size isn't just an instance-level decision, it's a CDB-wide one. DB_BLOCK_SIZE is not modifiable at the PDB level at all. Every pluggable database in a CDB inherits the root's block size with no way to override it individually. That's one more reason to think this decision through carefully before CREATE DATABASE rather than after: it isn't just locked for the database as a whole, it's locked for every PDB you'll ever plug into it.

Oracle does allow individual tablespaces to use a block size different from the database's overall default, 2 KB, 4 KB, 16 KB, or 32 KB, configured through the matching DB_nK_CACHE_SIZE parameter and its own dedicated buffer pool, the same mechanism covered back in Module 5's lesson on multiple buffer pools. This is a real, supported feature, but Oracle generally discourages mixing block sizes within one database unless you have a specific, well-understood reason, the added manageability overhead usually isn't worth it for the marginal benefit.

Because this can't be changed after the fact, it's worth being deliberate rather than accepting whatever value happens to be typed into a script you copied from somewhere. Before running CREATE DATABASE, look honestly at your actual row sizes, your dominant access pattern (index lookups versus full scans), and whether you're carrying large LOB data. If you're seriously considering something other than the 8 KB default, for example 16 KB or 32 KB for a genuine data warehouse workload, test it against a representative workload first, using AWR/ADDM reports or an I/O calibration tool, rather than guessing. And if none of this feels clear-cut for your situation, that's a reasonable moment to consult Oracle's own documentation or someone with hands-on tuning experience before committing to a value you'll be stuck with for the life of the database.
You can always confirm what's actually in effect once the instance is up:

SELECT name, value FROM V$PARAMETER WHERE name = 'db_block_size';

For the COIN database, we'll use a 4096 block size, one of the two values Oracle's own documentation calls typical, and a reasonable, well-supported choice for a small training database that won't see production-scale I/O. It's worth knowing this is a deliberate choice rather than the default, if you'd rather match what most real-world databases actually run today, 8192 is equally valid and arguably the more representative choice to practice with. If you're running Oracle, add this line to your initCOIN.ora file:

db_block_size = 4096