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 SlideShow below to see how a database grows.

  1. This illustration shows a database in which two physical files
  2. Here, a new table(PERSON) is created with 50K of initial space allocated.
  3. Next, another new table (ORDERS) is created with 100K of initial space.
  4. Some time has passed, and the initial extent for the PERSON table has been used up.
  5. The same event happens to the ORDERS table that happened to the PERSON table:
  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.

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