An Introduction to the Data Dictionary Model for Storage Objects

David Clement

26 March 2008

Oracle versions 7-10g

1. Introduction

The SYS user owns some of the data dictionary objects. Among these are five tables whose relationships lay out how the RDBMS organizes storage space. These tables form a data model that makes the dictionary management of storage space easy to understand. They don’t help with raw devices, locally managed tablespaces, or automatic segment space management, but they do support getting your head around the basic notions.

2. Data Files

The tables in question are FET$, UET$, SEG$, FILE$, and TS$. One of these represents operating system files used for database storage. No points for guessing which one.

The names of the files are in a view, V$DBFILE, while the table FILE$ holds the file size and the maximum size to which the file can grow, expressed in database blocks. You can report the interesting information about your database files as follows.

select as “OS File Name”, f.blocks as “Blocks”, f.maxextend as “Max Growth”
from v$dbfile v, file$ f
where v.file# = f.file#;

OS File Name                                       Blocks  Max Growth
-------------------------------------------------  ------  ----------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF     640     4194302

To run this query you need ‘select’ privilege on the objects owned by SYS.

3. Tablespaces

Looking at FILE$, you will see that it has a FILE# column, a RELFILE# column, and a TS# column. The first of these is the file identifier, that is, the primary key for this table. The second is a value to which the segment header refers; more on this later. The third points to the parent table of the FILE$ table. That table is TS$, which gives information on tablespaces. This relationship in the data model shows that files belong to tablespaces. They don’t map to each other in some arcane way, through multiple many-to-many relationships; rather, files belong to tablespaces, quite simply.

You can see which tablespaces own which files with the following query.

select as “Tablespace Name”, as “Data File Name”
from ts$ t, v$dbfile v, file$ f
where t.ts# = f.ts#
and v.file# = f.file#;
Also, you can now begin to construct an entity-relationship diagram (ERD) of the storage space data model, as follows.

This shows the many-to-one relationship between a file and a tablespace, as implemented by the tables FILE$ and TS$.

Something to keep in mind is that the file is defined in terms of the OS file system, and allocated as some collection of disk blocks, which are also constructs of the OS file system. Everything else discussed in this article is an RDBMS construct, and therefore that much farther removed from reality.

4. Segments

The next storage-management object to consider is the segment. Segments are modeled by the table SEG$. Any database object created in a tablespace receives at least one segment, so that, for example, you can see table names by issuing the following query.

select segment_name
from dba_segments d
, seg$ s
where d.header_file = s.file#
and d.header_block = s.block#
and s.user# = 61
and s.type# = 5;


This is merely a demonstration. It would have been easier to get the same information from the data dictionary view, DBA_SEGMENTS, without resorting to SEG$.

The interesting point about SEG$ is its attributes. It refers to FILE$ and to TS$ and it tracks the extents and their storage characteristics in columns with names like minexts, maxexts, and extpct. It may be immediately clear from this that a segment manages extents in much the same way that a tablespace manages data files. A segment fits between extents and the higher-level constructs, so it can be added to our ERD as follows.

A segment refers both to a file (use the SEG$ column FILE# and join to the FILE$ column RELFILE#) and to a tablespace.

5. Extents

An extent is a contiguous sequence of data blocks. The database block size is typically a multiple of the OS file system block size. The file system block size is the amount of data that is written to disk and read from disk in a single operation. One example: for the Ext2 file system that used to be everywhere in Linux installations, the size was usually 1024 bytes. An installation of Oracle on one of those boxes would typically use a more substantial database block size, because the database writer (DBWR) process would work more efficiently with a larger size than 1K, so you might find, for example, 8K as the database block size. An extent, therefore, would be no less than 8K on such a box.

“Contiguous” in this case refers to how the allocation of data blocks looks to Oracle. At a lower level in the IO system, the apparently contiguous blocks are probably scattered about on a DASD. Oracle can’t look past the disk controller card or SAN cache or whatever other device may be in the way, so the blocks seem contiguous to the RDBMS. The situation is like that of the word “physical,” which, in programming, almost always means “virtual, but at a level I can’t see.” This seems worth mentioning because DBAs sometimes worry about making their data blocks really contiguous. Give up. You can’t.

Since extents are contiguous sequences of data blocks (well, sort of), they reside within files. Segments are collections of extents that make them easier to manage. Since files belong to tablespaces, it follows that (in an indirect way) extents also reside within tablespaces. You can see this from how the extent tables refer to the tablespace and file with the columns ts# and file#.

The data dictionary uses two different tables to track extents. One is for used extents and is called UET$. The other is for free extents and is called FET$. They have a somewhat different set of columns; UET$ has three more columns, which point to the segment that uses the space.

Now it is possible to complete the ERD, as shown, that summarizes the relationships among the dictionary-managed storage objects.

A free extent, which in some way more or less direct represents magnetic storage on a disk, belongs to a file, which is an operating system concept, and to a tablespace, which is an RDBMS concept for managing files. A used extent belongs not only to a file and to a tablespace but also to a segment. A segment represents a database object such as a table (something we’ve only briefly glanced at) and collects used extents together to manage them. Further, a segment belongs to a file and to a tablespace.

6. Conclusion

It is interesting both from the storage point of view and from the data modeling point of view to see how storage objects are modeled in the Oracle data dictionary. Five tables owned by SYS, namely FET$, UET$, SEG$, TS$, and FILE$, nicely summarize the conceptual organization of space in the database and how it is accessed.