Basics Of Tablespace, Data files, Extents And Blocks

Understanding The Basic Terminologies: 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.

To discover the tablespaces in your database, query dba_tablespaces:

SELECT tablespace_name

FROM dba_tablespaces.

Some common tablespaces in Oracle databases include

SYSTEM for Oracle's meta data,

TEMP tablespace for temporary objects and

RBS for rollback segments .

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

SELECT file_name,tablespace_name

FROM 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. 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 . 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 Oracle is the block. 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 are 2K for a small database, 8K for a medium-sized one, and 16K for a large one. 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.

Creating a Tablespace

Let's create a tablespace with the initails (default) size of extent as 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.

CREATE TABLESPACE TECHAEON

datafile 'd:\tablespaces\appl_data01.dbf' SIZE 50M,

'd:\tablespaces\appl_data02.dbf' SIZE 50M

DEFAULT STORAGE

(

INITIAL 256K

NEXT 500K

PCTINCREASE 0

MAXEXTENTS 20

)

Locally Managed Tablespaces: Autoallocate And Uniform Clause:

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_data

datafile 'd:\tablespaces\appl_data01.dbf' size 50M,

‘d:\tablespaces\appl_data.dbf' size 50M

extent management local uniform size 512K

create tablespace large_acctdata

datafile 'd:\tablespaces\appl_data01.dbf' size 50M,

‘d:\tablespaces\appl_data.dbf' size 50M

extent 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_type

from dba_tablespaces

Purity Levels Of Function - PRAGMA RESTRICT_REFERENCES

An Oracle database cannot determine the work done by a packaged function when the function is executed from inside a DML statement. Therefore, if packaged functions are to be executed from within a DML statement, developers must use a PRAGMA to define a purity level for functions defined as part of the package spec. A PRAGMA is a compiler directive that instructs the compiler to handle code in a specific manner. To define a purity level for a packaged function, the PRAGMA RESTRICT_REFERENCES is used.

A purity level defined within a package spec instructs Oracle about the kinds of operations that the function performs. Below are the four purity levels that can be defined for a function.

WNDS	Write No Database State. The function doesn’t alter the contents of any database table.
RNDS	Read No Database State. The function doesn’t read the contents of any database table.
WNPS	Write No Package State. The function doesn’t alter any variables within another package.
RNPS	Read No Package State. The function doesn’t read any variables within another package.

The following example shows, how a purity level could mentioned for a function, while defining the package specification. The example makes the function get_id to the WNDS purity level.

CREATE OR REPLACE PACKAGE P_TEST

FUNCTION GET_ID RETURN NUMBER;

PRAGME RESTRICT_REFERENCES(GET_ID,WNDS);

END;

Note: The purity level for the functions can also be defined for the functions used in an Oracle object type.