Oracle database structure: Understanding the basics

In today's blog post we will be looking at how data is arraigned and stored in the database. 
An Improperly organized data could reduce the database integrity or negatively impact on its performance. 
This arrangement can be likened to an organogram (Organisations hierarchy structure)  where we have segmentation like:
- Groups
- Departments
- Units
- Teams
- Staff (this being the smallest unit)

Now that the concept is clear let us get busy.....

An Oracle Database is divided into Tablespaces. This tablespaces are logical storage units or partitions. They are unique to the database to which they belong.

Let us view the tablepaces available in the database.

Log on to sql as the sysdba by typing the following:

 sqlplus sys as sysdba

Enter you database password 

Note that you will not see the password been typed

Now type and execute the following:

SELECT TABLESPACE_NAME, STATUS, BLOCK_SIZE,

INITIAL_EXTENT, MAX_EXTENTS

FROM DBA_TABLESPACES;

In this query  we can see the list of tablespaces, its status and size as seen in the screen shot above.

Now that you know how to query all the available tablespaces in your database, let us take a look at the attributes of a tablespace.

There are two types of Tablespaces:

Bigfile Tablespace: This is a tablespace that contains a single but very large data file. It can be as large as in the terabyte range. They are used in data warehouse environments where business intelligence is of importance.

Smallfile Tablespace: This is a tablespace that contains small yet several datafiles. It is mostly used in environments like financial institutions where emphasis are made on the redo log groups.

A tablespace consists of one or more physical datafiles. This datafiles can be associated with only one tablespace and only one database. These datafiles interact directly with the operating system as they were created and stored in the operating systems storage.

Datafiles contains a grouping of the main information unit called the Data blocks.

Data blocks are the smallest unit of information and it’s the bases for our storage organization.

Now the datafiles are subdivided to two types of logical partitions which are:

Segments and Extents.

Segments are sets of extents that have been allocated for a specific type of data structure, which are all stored in the same tablespace. For example, each table's data is stored in its own data segment. Examples of segments are:

- tables,

- views,

- sequences,

- indexes etc

There are four types of segments used in Oracle databases:

•  data segments
•  index segments
•  rollback segments
•  temporary segments

Let us see the segments we have in our database

Log in to SQLPLUS as SYS with this command:

 sqlplus sys as sysdba

Type and execute this query:

select segment_name,segment_type,tablespace_name

 from DBA_SEGMENTS

 where rownum < 10;

The output has been limited to just 10 entries as the numbers of segments in the database are many.

As you can see the segments displayed are tables.

Extents are specific number of contiguous data blocks that are allocated for storing a specific type of information.

The first set of contiguous blocks are set up by the system when a segment is created. This extent is called an initial extent. 
After the initial extent has been filled up, oracle allocates more extents automatically. These are known as next extent. 
Please note that the total number of extents that can be allocated in a database is limited by the amount of storage space available.

Confused? please don't be.

All the jargon I have just said can be summarize with this illustration below: 

Like I said earlier an example of a segment is a Table. A physical example of a table is the "Employees" table which is displayed below. You can see the highlighted as an example of an extent in the illustration.

Anatomy of the Data Blocks

At the finest level of granularity, Oracle stores data in data blocks (also called logical blocks, Oracle blocks, or pages). They can be seen as the atom of data. One data block corresponds to a specific number of bytes of physical database space on disk.

The data block size is set during the creation of the database.

Oracle data blocks are the smallest units of storage that Oracle can use or allocate.

Header: The header contains general block information, such as the block address and the type of segment; for example, data, index etc

Free Space: Free space is used for inserting new rows and for updates to rows that require additional space. The free space is compressed to allow the header and row data to grow accordingly

Row Data: This portion of the block contains the information (data) in the table

I hope this blog post has been enlightening.

Here are some references for more studies on oracle database structures

Oracle Documentation

*Clip art credit goes to Oracle.com