SQL Extensions   «Prev  Next»

Lesson 3 Tablespaces and datafiles
Objective Describe how tables, tablespaces, and datafiles fit together.

Tablespaces and Datafiles

Now that you have seen how to create a table with columns, let us add some details to the CREATE TABLE statement. These additional parameters define the way that the table uses physical storage space in the database. Before describing the syntax, a little background information is needed. Before creating a table, you create a database and define the total storage space it can use. When you create a table without specifying any space-related parameters, Oracle allocates a predefined default amount of blocks within a predefined default tablespace to that table. Once you begin to add rows to the table, the row data is written to a block and the blocks are filled sequentially for each table. Use the following series of images below to see how a database grows.

This illustration shows a database in which two physical files (FILE1 and FILE2) are assigned to one tablespace (USER1).
1) This illustration shows a database in which two physical files (FILE1 and FILE2) are assigned to one tablespace (USER1).
Here, a new table(PERSON) is created with 50K of initial space allocated. It is assigned to the USER1 tablespace. So, 50K is allocated inside USER1, and in turn 50K is allocated inside USER1, and in turn, 50K is allocated in FILE1.
2) Here, a new table(PERSON) is created with 50K of initial space allocated. It is assigned to the USER1 tablespace. So, 50K is allocated inside USER1, and in turn 50K is allocated inside USER1, and in turn, 50K is allocated in FILE1.
Next, another new table (ORDERS) is created with 100K of initial space. The table is also assigned to the USER1 tablespace. 100K is allocated inside the USER1 tablespace for this table. In turn, 100K is allocated in the physical files. First, 50K is allocated from FILE1 and then 50K is allocated from FILE2.
3) Next, another new table (ORDERS) is created with 100K of initial space. The table is also assigned to the USER1 tablespace. 100K is allocated inside the USER1 tablespace for this table. In turn, 100K is allocated in the physical files. First, 50K is allocated from FILE1 and then 50K is allocated from FILE2.
Some time has passed, and the initial extent for the PERSON table has been used up. A new row inserted into the PERSON table causes the database to allocate a new extent. The NEXT parameter for the PERSON table directs the database to allocate 15K for the new extent. The USER1 tablespace assigns another 15K to the PERSON table and that 15K is allocated to FILE2.
4) Some time has passed, and the initial extent for the PERSON table has been used up. A new row inserted into the PERSON table causes the database to allocate a new extent. The NEXT parameter for the PERSON table directs the database to allocate 15K for the new extent. The USER1 tablespace assigns another 15K to the PERSON table and that 15K is allocated to FILE2.
The same event happens to the ORDERS table that happened to the PERSON table: more space is needed. In this case, the NEXT parameter specifies that 250K be allocated in the next extent. USER1 and USER2 in turn allocate 250K for this table. FILE2 is now full, as is the USER1 tablespace.
5) The same event happens to the ORDERS table that happened to the PERSON table: more space is needed. In this case, the NEXT parameter specifies that 250K be allocated in the next extent. USER1 and USER2 in turn allocate 250K for this table. FILE2 is now full, as is the USER1 tablespace.
A new file (FILE3) is assigned to the USER1 tablespace, giving it another 50K of space.
6) A new file (FILE3) is assigned to the USER1 tablespace, giving it another 50K of space.
In this final illustration, the PERSON table has used up the first and second extents and a new row is being inserted. This causes the database to allocate a third extent. The PCTINCR parameter specifies that each subsequent extent should be increased by 50% over the previews extent. Therefore, 20K of space is allocated to the PERSON table. This space is allocated in the USER1 tablespace and in the FILE3 datafile. As you can see, the physical allocation of the data can be very different from the logical location of the data in the tablespace.
7) In this final illustration, the PERSON table has used up the first and second extents and a new row is being inserted. This causes the database to allocate a third extent. The PCTINCR parameter specifies that each subsequent extent should be increased by 50% over the previews extent. Therefore, 20K of space is allocated to the PERSON table. This space is allocated in the USER1 tablespace and in the FILE3 datafile. As you can see, the physical allocation of the data can be very different from the logical location of the data in the tablespace.

The next lesson shows you how to specify the space parameters when creating a table.
Oracle Database SQL