Deferred Segment Creation in Oracle

Back to: Oracle DBA Tutorials

Deferred Segment Creation in Oracle

In this article, we will learn about Deferred Segment Creation in Oracle with Examples. Please read our previous article discussing Types of Segments in Oracle. While creating the table, we will try to understand the meaning of deferred segment creation.

Step 1: Check the user and container name

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

Command: show con_name

Step 2: Deferred Segment Creation

Statement: show parameter deferred_segment_creation

The value of this parameter is TRUE. The segment will be created for the table after we create the table using the DDL statement and after you insert one record using the DML statement. Let’s try to check that by creating a table.

Statement: create table country (id number, name varchar2(100) );

The table is created with two columns: the ID and the name. Now, if we check the segment details with the segment_name as COUNTRY, we will not find any details because no data has been inserted into the table.

Statement: select * from user_segments where segment_name= upper(‘country’);

Here, we have mentioned the upper because the data is stored in the upper cases. As we can see, no rows are present here. The segment will not be created until we insert one row. In that case, let’s try to insert one row.

Statement: insert into country values (1,’Jordan’);

We have inserted one record with value 1, Jordan. We didn’t commit after this. Even if we don’t commit, Oracle will create segment space. Now, let us look at the segments.

Statement: select * from user_segments where segment_name= upper(‘country’);

As the data is inserted, we can see the segment type and segment_subtype, blocks, total extents, and total bytes stored. Now, we can control the segment immediately. In order to do that, we have to change the parameter DEFERRED_SEGMENT_CREATION to false. We can do this by using an alter session or alter statement.

Statement: alter session set DEFERRED_SEGMENT_CREATION=false;

So, the parameter is set to false. Let’s check the parameter details once.

So, the parameter is FALSE.

Note: We have changed the parameter value to FALSE for this session only.

Let’s try to verify this parameter by creating another table.

Statement: create table jobs (id number, title varchar2(100) );

The table is created with two columns: id and title. As we have changed the parameter behavior, let’s try to check the segment again.

Statement: select * from user_segments where segment_name= upper(‘jobs’);

We can see the segment details without even inserting any record into the table. So, we have mentioned the segment as immediate.

Note: The deferred segment is the default for the tables, indexes, and partitions.

Now, let us put the parameter value back to normal.

Statement: alter session set DEFERRED_SEGMENT_CREATION=true;

So, the session is altered now. We can even control the segment creation without changing the parameter value. Let’s try to create another table to do that.

Statement: create table test (n number) SEGMENT CREATION IMMEDIATE;

In the above create table statement, we have added segment creation immediately. So, we are defining the oracle to create the table and assign the segments immediately. No need to insert records. Let’s try to create the table test.

So, the table TEST is created. Now, let us look at the segment details.

Statement: select * from user_segments where segment_name= upper(‘test’);

So, the segment records are created, because we have mentioned as “segment creation immediate”. Now, let’s create a table with segment creation deferred. This deferred is getting the segment state back to normal so that until we insert records into the table, the segments are not created.

Statement: create table test10 (n number) SEGMENT CREATION DEFERRED;

So, Table TEST10 was also created. Let’s look at the segment details.

Statement: select * from user_segments where segment_name= upper(‘test10’);

So, the segment details are not present, and the table spaces are back to normal. In the next article, I will discuss Space-Saving Features in Oracle. In this article, I explain Deferred Segment Creation in Oracle with Examples. I hope you enjoy this article on Deferred Segment Creation in Oracle.