Back to: Oracle DBA Tutorials
tSegments Extents and Data Blocks in Oracle
In this article, we will learn about Segments, Extents, and Data Blocks in Oracle. Please read our previous article, which discussed Managing Storage Space in Oracle. We will do this in the exercise format.
Step 1: Connect to the pluggable database
Step 2: Check the user and container name
Command: show con_name
So, we are connected to the database JAYAPDB, and the user is PDBTS_ADMIN.
Step 3: Create a tablespace
We will be creating the tablespace for our exercise.
Statement: create tablespace ts1 datafile ‘/u01/app/oracle/oradata/JAYADB/jayapdb/ts1.dbf’ size 1g;
So, the tablespace ts1 is created. We haven’t mentioned any other options regarding tablespace creation, so let’s get the DDL behind it.
Statement: select dbms_metadata.get_ddl(‘TABLESPACE’,’TS1′) from dual;
From the above statement, we can generate all the DLL statements for any object in the database. Here, we have mentioned the object type as tablespace and the object name as TS1. Let’s execute the query.
From the above query output, we can see the data file location, and we have mentioned the size as 1 GB. The logging option is online and permanent. The block size for this tablespace is 8kb. The extent management is local. This is using bitmaps. The segment space management is set to auto. The Oracle will search for the blocks automatically. So, now let’s check the tablespace details using the dba_tablespaces data dictionary.
Statement: select * from dba_tablespaces where tablespace_name=’TS1′ ;
We can see the details of the tablespace. The initial extent of the tablespace is 65536, which is 8 blocks. The block size is 8192, and the initial extent is a combination of 8 blocks. The relation of the tablespace and the objects is as follows
TABLESPACE -> SEGEMENTS -> EXTENT -> BLOCKS.
The combination of blocks is called an extent and the combination of extents is called Segments. The combination of segments is referred to as tablespaces. The smallest unit is a block.
Step 4: Create a user and table with the default tablespace as TS1.
Let’s first create a user with the name JAYA.
Statement: CREATE USER JAYA identified by JAYA default tablespace ts1;
So, the user JAYA is created with the default tablespace as TS1. Let’s try to grant some default privileges in order to connect the database and create table privilege and unlimited tablespace privilege so that data can be stored in the tablespace ts1.
Statement: grant create session, create table, unlimited tablespace to Jaya.
So, the user has been created, and the privileges granted have been completed. Let’s try to check the user details using the dba_users data dictionary.
Statement: select * from dba_users where username=’JAYA’;
We can see the username, user_id, and the default tablespace as ts1. So, any data that is stored with the user Jaya will be stored in ts1 tablespace. Let’s create a table with the EMP.
Statement: CREATE TABLE JAYA.EMP ( EMP_ID NUMBER, NAME VARCHAR2(100), NOTE VARCHAR2(1000) );
The table contains columns emp_id, name, and note. Let’s try to execute the statement.
So, the table is created. Let’s check the table details using dba_tables.
Statement: SELECT * FROM DBA_TABLES WHERE TABLE_NAME=’EMP’ AND OWNER=’JAYA’;
So, the table is present, and the default tablespace is TS1, but the segments are not allocated. The table and the index are segments, and anything that needs storage in the database is a segment. We have a dictionary called dba_segment, which displays all the segments. Let’s try to get the segment details of the table EMP.
Statement: SELECT * FROM DBA_SEGMENTS WHERE OWNER=’JAYA’ AND SEGMENT_NAME=’EMP’;
We didn’t get any output. This is because Oracle didn’t create any segment because there are no rows created in the table. So, in order to create a segment, let’s try inserting a row inside the table.
INSERT INTO JAYA.EMP VALUES (1,’SAMPLE’,’the best teacher ever : )’)
commit;
So, the insert is completed. So, now let’s try to check the segment details using dba_segments.
Statement: SELECT * FROM DBA_SEGMENTS WHERE OWNER=’JAYA’ AND SEGMENT_NAME=’EMP’;
The row is inserted into the table. The segment_name is EMP, the segment_type is the table, and the segment_subtype is ASSM. This segment contains one extent and 8 blocks. The information for the extent can be found using the data dictionary dba_extents.
Statement: select * from dba_extents where owner=’JAYA’ and SEGMENT_NAME=’EMP’;
We can see the extent of the details. Only one extent is stored here, and its extent_id is 0. This extent ID has 8 blocks.
Step 5: Insert 100 rows into the table.
Let’s try to insert 100 rows into the table and look for the segments.
begin for i in 1..100 loop insert into JAYA.EMP values (i,'just test name', 'just test comments'); end loop; commit; end;
From the above statement, we are using a for loop to insert the 100 rows with emp_id as i, emp_name as “just test name,” and note as “just test comments.” The commit is used after the loop ends. Let’s try to execute the statement.
As the 100 rows were inserted, let’s try to check the segment details.
Statement: select * from dba_extents where owner=’JAYA’ and SEGMENT_NAME=’EMP’;
The segment didn’t change much after we inserted 100 rows. Let’s try to check the extent details.
Statement: select * from dba_extents where owner=’JAYA’ and SEGMENT_NAME=’EMP’;
So, here also, the extent details didn’t change much. In this case, let’s try to insert 10000 rows into the same table and looks for the change details.
begin for i in 1..10000 loop insert into JAYA.EMP values (i,'just test name', 'just test comments'); end loop; commit; end;
So, the 10000 rows were inserted. Let’s try to look at the segment details in the EMP table.
Statement: select * from dba_extents where owner=’JAYA’ and SEGMENT_NAME=’EMP’;
Here, we can see there is a slight change in the extents. The total extents are mentioned as 9 extents, the blocks are 72, and the total bytes are 589824. Let’s try to check the details of the extents.
Statement: select * from dba_extents where owner=’JAYA’ and SEGMENT_NAME=’EMP’;
We can see that 9 extents are mentioned here. Now, let’s try inserting 1 lakh records into the same table and looking for the extent and segment changes.
begin for i in 1..100000 loop insert into JAYA.EMP values (i,'just test name', 'just test comments'); end loop; commit; end;
1 Lakh rows were inserted successfully. Now, let’s look at the extent and segment details.
Statement: SELECT * FROM DBA_SEGMENTS WHERE OWNER=’JAYA’ AND SEGMENT_NAME=’EMP’;
The extents recorded here are 21, and the total byte size is 6291456, which is approximately 6 GB. Now, let’s look at the extent of the details.
Statement: select * from dba_extents where owner=’JAYA’ and SEGMENT_NAME=’EMP’;
We can see multiple extent_ids were created as there are multiple rows present inside the table. From row 17, Oracle changed the block size to 128 blocks. So, one extent will hold 128 blocks. This is according to ASSM. Oracle will allocate the extents according to the data. We can see the file_no as 13.
Step 6: Check the Row_id
Now, we have to find out how Oracle retrieves data from the data files. There should be a pointer referred to as row_id. Let’s try to fetch a record using the row_id from the table EMP that we have created.
Statement: select rowid, emp_id, name, note from JAYA.emp where emp_id=1000000;
From the above output, we can see the emp_id as 10000, name, and note as the same as the time while executing the insert statement. We can also see the row_id. The row_id is not stored in the database and contains 18 digits.
- The first 6 digits of the row_id are referred to as the object number.
- The next 3 digits are mentioned as Relative File Number.
- The next 6 digits are referred to as Data Block Number.
- The last 3 digits are referred to as row numbers.
Step 7: Delete all the rows from the table
Now, let’s try to delete all the rows from the table and then check the segment and extent details.
delete from JAYA.EMP;
commit;
We have deleted all 110,101 rows from the table. Now, let’s try to check the segments and extents because while the data is present inside the table, the segments and extents were allocated.
Statement: SELECT * FROM DBA_SEGMENTS WHERE OWNER=’JAYA’ AND SEGMENT_NAME=’EMP’;
We can still see that the extents are 21, and the blocks are 768. The size of the table has also not been changed. So, the blocks can be inserted using new data. The ASSM will insert the new data in these blocks for future inserts or updates. So, the delete statement will not reset the size. Let’s try checking the extents as well.
Statement: select * from dba_extents where owner=’JAYA’ and SEGMENT_NAME=’EMP’;
The extents are still present. If we truncate the table, the extents will be replaced with new data or deleted. The delete statement will delete the records but not the segments and extents.
Note: Never make a delete statement to free the table.
Step 8: Truncate the table
Let’s try to truncate the table and check if the segments will be cleared or not.
Statement: truncate table JAYA.EMP;
As the table is truncated, all the extents and segments allocated for this table will be cleared and returned to 0.
In the next article, I will discuss the Availability and Optimization of Free Space in Oracle. In this article, I explain Segments, Extents, and Data Blocks in the Oracle with examples. I hope you enjoy this Segments, Extents, and Data Blocks in the Oracle article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.