RelationalDBDesign RelationalDBDesign

Database Architecture   «Prev  Next»
Lesson 6Tablespaces
ObjectiveExplain how Oracle manages space.

What is tablespace?

While Oracle physically stores 1) table and 2) index data in datafiles, it manages the relationship between tables, indexes, and datafiles using a logical concept called the tablespace. A tablespace is a grouping of one or more datafiles. When you create a table or an index, you need to tell Oracle where to store that object. You do that by providing a tablespace name. In turn, the tablespace definition includes a list of one or more datafiles, which is where Oracle actually stores the data. Move your mouse over the following diagram to see how this works:
Mapping of Tables to Table Data to Tablespaces
  1. Table A: Table A's data is stored in Tablespace A
  2. Table B: Table B's data is stored in Tablespace A.
  3. Table C: Table C's data is stored in Tablespace B.
  4. Tablespace A: Tablespaces may contain data from several objects, in this case Tables A and B.
  5. Tablespace B: Tablespace B contains only data from Table C.
  6. Datafile A: May contain data from Table A or Table B or both.
  7. Datafile B: May contain data from Table A or Table B or both.
  8. Datafile C: May contain data from Table A or Table B or both.
  9. Datafile D: Will only contain data from Table C.

Tablespaces Datafiles
Note: While a tablespace may contain many datafiles, a datafile can only be part of one tablespace.

How tablespaces create flexibility

You can see that the tablespace stands between the object (table, index, etc) that you have created, and the physical location where that object is stored. The benefit you get from this relationship is the flexibility to add datafiles as the objects in a tablespace grow. Tablespaces allow a many-to-many relationship between database objects and datafiles. Many tables and indexes may be stored in a datafile, and many datafiles may be used for one table or index. The advantage to this twofold: you can easily add new datafiles whenever you need more space, and you can spread your datafiles over multiple disks as a way to distribute the I/O load.

Overview of Tablespaces

A tablespace is a logical storage container for segments. Segments are database objects, such as tables and indexes, that consume storage space. At the physical level, a tablespace stores data in one or more data files or temp files. A database must have the SYSTEM and SYSAUX tablespaces. The following figure shows the tablespaces in a typical database. The following sections describe the tablespace types.

Figure 3-6: Tablespaces