hat block size did you choose for your database? How many chunks have been allocated for your table? How large is each chunk and what will the size of the next chunk be?

If you are a SQL Server DBA, you are probably wondering at this point, “What was that guy smoking when he wrote this?” From a SQL Server perspective, you are correct to wonder because SQL Server hides many of the details from you about how space is allocated for your data. As I demonstrate in this article, however, Oracle allows a DBA to go much further under the hood to help properly manage data’s space requirements.

Understanding the Pieces of the Puzzle: Tablespaces, Extents, and Blocks
An Oracle database can contain many files. But you don’t directly choose which file to store your data in. Instead, you specify which tablespace to use. A tablespace is simply a logical grouping of files. It is similar to the SQL Server concept of a filegroup.

To discover the tablespaces in your database, query dba_tablespaces:

SELECT tablespace_nameFROM dba_tablespaces.

Some common tablespaces in Oracle databases include SYSTEM for Oracle’s meta data, a TEMP tablespace for temporary objects, and RBS for rollback segments (which I’ll discuss in my next article).

To discover which files are used for each tablespace, execute the following SQL:

SELECT  file_name,tablespace_nameFROM  dba_data_files;

When you create a table in a tablespace, Oracle allocates a chunk of space for the object. Each chunk is called an extent (sounds more technical than chunk, doesn’t it?). As you will see, each extent can vary in size. An extent is itself made up of a number of blocks, the smallest unit of space (more on that soon).

So a tablespace is a grouping of files that stores data in extents, of which each is made up of a number of blocks. Now that you hopefully understand the basic terminology involved, let’s look at the various decisions one makes when allocating space in Oracle.

Allocating Space for Data
As I said above, the smallest unit of space in both Oracle and SQL Server is the block. But while a block is fixed at 8,192 bytes in SQL Server, you can determine the size you want when you create a database in Oracle. Once you’ve created the database, however, you cannot change this setting.

The sample sizes that Oracle provides in its configuration file is 2K for a small database, 8K for a medium-sized one, and 16K for a large one. Unlike in SQL Server, a row can span more than one block. So if a row in a table takes up 12K and you have chosen an 8K-block size, 8K of the row will be stored in one block and the remaining 4K will be stored in a separate block. This is called row chaining. As you can imagine, it is more effective to fit most of your rows in one block.

Each extent is made up of a number of blocks. As a table grows, the initial extent that Oracle allocated can be used up. Oracle then allocates another extent for the table?which again is made up of a number of blocks. In Oracle, you can specify the size of the initial extent and all subsequent extents. Typically, you specify these settings when you create a tablespace. Tables will use the storage settings of the tablespace they are created in. If necessary, however, you can override these tablespace settings by specifying different settings in the CREATE TABLE statement itself.

Allocating an extent is a relatively expensive process; you don’t want to slow Oracle down by forcing it to allocate extents for every row or two that’s inserted in your table. It is also easier if you keep extents in a tablespace the same size. A useful analogy that I once read compares extents to laying down tiles on a floor. If all the tiles are reasonably sized and uniform, it is easy to lay them down. However, if they are either very small or differently sized, it becomes much harder to manage.

Creating a Tablespace
Let’s create a tablespace that we can use to store relatively small tables between 100K to less than 10MB. I’d like to allocate 256K to start. If a table needs more space, I’ll allocate it in sizes of 500K increments. However, because this tablespace is for smaller tables, I’ll set the maximum number of extents to 20. If a table needs more space than that, I’ll move it to another tablespace that is built for larger tables.

Here’s the command I use:

CREATE TABLESPACE small_acctdataDATAFILE  'e:oracleoradatajlaxt2small_acctdata01.dbf' SIZE 50M,'e:oracleoradatajlaxt2small_acct_data02.dbf' SIZE 50MDEFAULT STORAGE(INITIAL 256KNEXT 500KPCTINCREASE 0MAXEXTENTS 20)

Locally Managed Tablespaces: Autoallocate or Uniform Clause?
Oracle has to store information regarding the extents for each tablespace somewhere. By default, this information is stored in the data dictionary (which is in the system tablespace). Therefore, whenever an extent is freed or allocated, the data dictionary has to be updated. However, Oracle 8i also provides locally managed tablespaces, where data regarding the extents is stored in a bitmap in the tablespace itself. This is more efficient than the data dictionary.

On a locally managed tablespace, you cannot specify the default storage settings. Instead, you have two additional settings that can be used to simplify space allocation. If you specify AUTOALLOCATE, Oracle will automatically handle the sizing for objects. If you specify the UNIFORM SIZE clause, however, Oracle creates all extents in the locally managed tablespace using size you give it.

Here’s an example of two tablespaces that I’ve created using the above options:

create tablespace appl_datadatafile 'e:oracleoradatajlaxt2appl_data01.dbf' size 50M,'e:oracleoradatajlaxt2appl_data.dbf' size 50Mextent management local uniform size 512Kcreate tablespace large_acctdatadatafile 'e:oracleoradatajlaxt2large_acctdata01.dbf' size 50M,'e:oracleoradatajlaxt2large_acctdata02.dbf' size 50Mextent management local autoallocate;

To see the settings on your tablespaces, you can execute the following query:

select tablespace_name, initial_extent,next_extent,   min_extents,    max_extents,    pct_increase,   extent_management,allocation_typefrom dba_tablespaces

In an application that we are rolling out at work, we’ve created 12 locally managed tablespaces. In each one we’ve specified different uniform sizes so that we can group tables appropriately. We’ve also divided the tablespaces among different disks to reduce contention.

Although I haven’t covered all the options that Oracle provides, I’ve hopefully covered enough to show you that space allocation in Oracle is complicated. If you understand the options and utilize them correctly, you can tweak your database in ways that SQL Server does not allow. But doing so correctly requires expert knowledge in both Oracle and your application. If you don’t have that expert knowledge, your decisions can impact performance negatively. I believe the move towards locally managed tablespaces shows that Oracle believes that?at least in this area?it is best to let the database engine manage most of the decisions.