Types of Segments in Oracle

Back to: Oracle DBA Tutorials

Types of Segments in Oracle

In this article, we will try to learn about the Types of Segments in Oracle with Examples. Please read our previous article discussing Row Chaining and Migrating in Oracle. In the Oracle database, there are ten types of segments. But as of now, we will focus on four types of segments. A segment is a set of extents allocated for a certain logical structure.

Types of Segments in Oracle:

• Table and Cluster: For a partition table, each partition has a data segment.
• Index: For a partitioned index, each partition has an index segment.
• Undo: Oracle maintains information to reverse the changes made to the database. This information consists of records of the actions of transactions, collectively known as undo. Undo is stored in undo segments in an undo tablespace. We use the UNDO tablespace for the rollback. The old version of the insert or update statement is present inside the UNDO tablespace.
• Temporary: The Oracle database server creates a temporary segment when an SQL statement needs a temporary database area to complete execution.

Note: When the statement finishes execution, the extents in the temporary segment are returned to the system for future use.

To understand this more clearly, let’s take an example and do an exercise

Step 1: Check the user and database name

Let’s check the database name and the username that we have connected.

Command: show con_name

Step 2: Create a table

In order to learn more about tables, let’s create a table as it is stored in segments.

create table dept
(
 deptno NUMBER(2),
 dept_name varchar2(100 )
);

As the table is created, let’s check the table details using the data dictionary dba_tables.

Statement: select * from dba_tables where table_name=’DEPT’;

We can see the owner of the table is PDBTS_ADMIN, and the default tablespace is USERS. This is because the user PDBTS_ADMIN default tablespace is USERS. Let’s look at segments to see whether the segments were created or not.

Statement: SELECT * FROM USER_SEGMENTS WHERE SEGMENT_NAME=’DEPT’;

We don’t find any output because there is no data inside the table DEPT. In that case, let’s try to insert two rows inside the DEPT table and check the segment details. The first row is IT DEPT, and DEPT no is 1, and the second row is FINANCE, and DEPT no is 2.

INSERT into dept values (1, 'IT Dept');
INSERT into dept values (2, 'FINANCE');
commit;

We have inserted the data inside the table. Now, let’s take a look at the segments once again.

Statement: SELECT * FROM USER_SEGMENTS WHERE SEGMENT_NAME=’DEPT’;

We can see the segments are created. The segment type is TABLE, and the subtype is ASSM. The extents are 1, and the blocks are 8. The Bytes size is 65536.

Step 2: Creation of Index

Let’s create an index on the column deptno in the table DEPT.

Statement: create unique index dept_inx on DEPT (deptno);

So, the index is created on the column deptno. As the index is created, let’s check the segment details.

Statement: select * from user_segments;

The above output lists two segments: a table and an index.

Step 3: Checks of Undo Segment

Let’s try to learn about the undo segment using the update operation. To do so, update the dept table and check the undo segment.

Statement: update dept set dept_name=’XXXX’;

The table rows were updated. We didn’t commit, or rollback, so the old version of these values will be inserted into the undo tablespace. The user can roll back at any chance. Let’s check the segment details again to cross-verify the undo segment.

Statement: SELECT * FROM dba_SEGMENTS where owner=’PDBTS_ADMIN’;

So, there is no change in the segments. We cannot see any information about the undo segment. In this situation, there is a data dictionary called v$session, which gives the details of the currently active sessions in the database. We can fetch the username, sid, serial#, and transaction about the undo operation.

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where  a.saddr = b.ses_addr
order by b.used_ublk desc;

From the above output, we can see that Sid is 399, and the username is PDBTS_ADMIN. We have two columns, USED_UREC & USED_UBLK.

• USED_UREC: Number of Undo records used
• USED_UBLK: Number of Undo blocks used.

From the output, we can see that 2 undo blocks and 1 undo record were used. In this block, there are two records. Now, let’s try to roll back the statement.

So, the rollback is completed. Now, let’s look at the previous query to see whether the undo records and undo blocks are present or not.

select a.sid, a.serial#, a.username, b.used_urec, b.used_ublk
from   v$session a,
       v$transaction b
where a.saddr = b.ses_addr
order by b.used_ublk desc;

This is because we have taken the rollback, and the old records were written back to their previous blocks. These are the benefits of the undo segment.

In the next article, I will discuss Deferred Segment Creation in Oracle with Examples. In this article, I explain the Types of Segments in Oracle with Examples. I hope you enjoy this article on Types of Segments in Oracle.