SQL Extensions   «Prev  Next»

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

Tablespaces and Datafiles

In an Oracle database, tables, tablespaces, and datafiles are interrelated components that work together to store and manage data. Here's how they fit together:
  1. Tables:
    • Tables are logical structures used to organize and store data in an Oracle database.
    • Each table consists of a collection of rows, where each row represents a single record or instance of data.
    • Tables have a defined structure, which includes columns, data types, constraints, and indexes.
  2. Tablespaces:
    • Tablespaces are physical storage containers that hold the data of one or more tables in an Oracle database.
    • They provide a way to logically group and manage data on different storage media or physical devices.
    • Each table must reside in a tablespace, and a tablespace can contain multiple tables.
  3. Datafiles:
    • Datafiles are physical files on disk that store the actual data and metadata of tables in an Oracle database.
    • They are the fundamental storage units of an Oracle database.
    • Each tablespace is made up of one or more datafiles, and a single datafile can belong to multiple tablespaces.

The relationship between tables, tablespaces, and datafiles can be summarized as follows:
  • Tables are logical structures that define the structure and organization of data.
  • Tablespaces are physical containers that hold the data of one or more tables.
  • Datafiles are physical files on disk that store the actual data and metadata of tables.

Example:

Consider a scenario where you have a table called "Customers" that stores customer information such as customer ID, name, address, and phone number. The "Customers" table is created in a tablespace called "UserData." The "UserData" tablespace is composed of two datafiles named "UserData01.dbf" and "UserData02.dbf." In this example, the "Customers" table is the logical structure that defines the data to be stored. The "UserData" tablespace is the physical container that holds the data of the "Customers" table. The "UserData01.dbf" and "UserData02.dbf" datafiles are the physical files on disk that store the actual customer data and metadata.
By using tablespaces and datafiles, Oracle allows you to efficiently manage and organize data in your database, optimize storage resources, and improve performance.

CREATE TABLE Statement

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.
Ad Oracle Database SQL