As mentioned before that ORACLE DATABASE server consist of two components one is INSTANCE and another one is DATABASE.
Database consist of different file system and in this topics we will discuss about DATA FILE. So data file is are those file where you data’s get stored. For more clear, let’s say I am going to trigger INSERT query with some value on EXAMPLE table.
For example like this:
Insert into EXAMPLE ( 1, abc, def, 1024 )
So here 1, abc, def, 1024 are called DATA and where I am going to stored in a file called DATA FILE.
But the scenario many not be going like that there might be thousands of data file and inside that data file one data file may consist 2 GB of space. So, the question here is how you going to organize those number of DATA FILES and another question is how you going to organize those data inside the DATA FILE that what we will discussed in this topics.
Let me show you the list of data file first located in my pc. From the below screenshot you can see that there are different types of data files like EXAMPLE, SYSAUX,SYSTEM,UNDO, USERS which we will discussed about this on next topic but in this topics how we going to discussed how this data files are going to managed. So here we going to learn how this data get organized.
So let’s start with simple example whenever you going to create table. This is a DDL SQL statement. Say like I am going to create table named EXAMPLE.
CREATE TABLE EXAMPLE ( a number)
So here whenever you going to create a table example, oracle is going to create special storage unit and that storage unit is called SEGMENT. That means for any object that is going to consume some storage that means one SEGMENT storage must be created on that. So table EXAMPLE and this is table EXAMPLE segment.
Note: This segment will have an initial size which can be given at the time of create statement. In this case I haven’t gave any those parameter. If parameter in not given then ORACLE will going to take default parameter.
Let’s assume like 2 MB space will be allocate for data which is going to store at table EXAMPLE segment.
As I have mentioned above assuming 2 MB space is provided for SEGMENT. Again this segment contains multiple extents that means, you can assume like this 2 MB segment as two extent where 1 MB space is given on each extent. This means segment consist of extent. Note: There can be numerous extent but As per table EXAMPLE scenario it consist of TWO EXTENTS and inside extent the MEMORY ALLOCATION is contiguous but there is no information that each extent should not be / should be far away from each other.
Again inside extent it consist of number of BLOCKs.
Now let’s discuss how block is look like?
Firstly, It has space allocated inside the block which is called header or BLOCK HEADER. So header contains what kind of header inside it? Which mean if it is going to put table data, index data or plsql procedure. So what types of data’s this block is going to stored? We put that in the header.
Secondly, If any activities going on right now inside the block. Let’s assume like this is table block and for that table let’s say i am triggering update. This means i am doing some sort of update activities / transaction. So activities / transactions information.
Third, is Address which contains the location for where data is located?
TABLE DIRECTORY: There is some space allocated for table directory. For example if this is table block then it going to contains the name of the table whose data is going to store in this block
ROW DIRECTORY: Let’s assume in block C , we have some row like following. We have three rows. So what is the row address of the following three rows that is going to be stored under ROW DIRECTORY? Therefore ROW DIRECTORY contains the address of rows only.
1 abc def 1024
2 bbc bef 1025
3 abc def 1024
DATA BLOCK: Then we also have data block which contains the information about the data that going to be stored here.
Then I have some empty space those empty space is put for future updates or for updates task.
This three together the header, table directory and row directory is called BLOCK OVERHEAD. BLOCK OVERHEAD inside we store metadata about what things are going to store inside the block
Now if BLOCKS information is clear then lets got to above SEGMENT, EXTENT AND BLOCK figure again.
All the SEGMENT, EXTENT and BLOCKS, they are put together in a container and that container is called TABLESPACE. So tablespace is something which contains segments, extent and blocks.
Let’s take an another example
Assume like I have a table named T1, T2 then I have index named I1 and I2
So whenever I created table t1 I will give what is the tablespace that I am going to have for table named t1. Let’s assume like table space for t1 table is TA which means t1 is a part of TA
Let’s say T1 table segment named is S1
Similarly Table T2 is also part of TA tables pace, T2 table segment named is S2. Similarly index I1 segment and I2 segment. The segment is consist of number of extent and number of extents consists of number of blocks.
Then table space TA is associated with file called 1.dbf file. In my case file are like SYSAUX, USERDBF
- So from above figure what you are seeing is 1.dbf/user.dbf with .dbf format is physical file which is data file and the table space is actually a part of dbf. So below what we are seeing is LOGICAL STRUCTURE (tablespace à segment -à extent -à blocks) and PHSYCIAL STRUCTURE is a data file.