Database Reference
In-Depth Information
■
here's a little-known fact: the default block size for a database does not have to be a power of two. powers of
two are just a commonly used convention. You can, in fact, create a database with a 5KB, 7KB, or
n
KB block size, where
n
is between 2KB and 32KB. i don't advise making use of this fact in real life, though—stick with the usual as your block
size. Using nonstandard block sizes could easily become a support issue—if you are the only one using a 5KB block size,
you may well encounter issues that other users would simply never see.
Note
The relationship between segments, extents, and blocks is shown in Figure
3-1
.
2MB Segment
1MB
Extent
1MB
Extent
8KB
8KB
8KB
8KB
8KB
8KB
8KB
8KB
8KB
8KB
8KB
8KB
8KB
8KB
8KB
8KB
Data Blocks
Figure 3-1.
Segments, extents, and blocks
A segment is made up of one or more extents, and an extent is a logically contiguous allocation of blocks. Starting
with Oracle9
i
Release 1, a database may have up to six different block sizes in it.
■
this feature of multiple block sizes was introduced for the purpose of making transportable tablespaces usable
in more cases. the ability to transport a tablespace allows a DBa to move or copy the already formatted data files from
one database and attach them to another—for example, to immediately copy all of the tables and indexes from an Online
transaction processing (Oltp) database to a Data Warehouse (DW). however, in many cases, the Oltp database might be
using a small block size, such as 2KB or 4KB, whereas the DW would be using a much larger one (8KB or 16KB). Without
support for multiple block sizes in a single database, you wouldn't be able to transport this information. tablespaces with
multiple block sizes should be used to facilitate transporting tablespaces; they are not generally used for anything else.
Note
There will be the database default block size, which is the size specified in the initialization file during the
CREATE
DATABASE
command. The
SYSTEM
tablespace will have this default block size always, but you can then create other
tablespaces with nondefault block sizes of 2KB, 4KB, 8KB, 16KB, and, depending on the operating system, 32KB. The
total number of block sizes is six if and only if you specified a nonstandard block size (not a power of two) during
database creation. Hence, for all practical purposes, a database will have at most five block sizes: the default size and
then four other nondefault sizes.
Any given tablespace will have a consistent block size, meaning that every block in that tablespace will be the same
size. A multisegment object, such as a table with a LOB column, may have each segment in a tablespace with a different
block size, but any given segment (which is contained in a tablespace) will consist of blocks of exactly the same size.
