Basics of Logical Structures in Oracle 12c - dummies

Basics of Logical Structures in Oracle 12c

By Chris Ruel, Michael Wessler

All the logical structures that you’ll see are in the database’s data files. Oracle 12c’s logical structures allow you to organize your data into manageable and, well, logical, pieces.

Let’s examine the relationship of logical to physical objects. The arrow points in the direction of a one-to-many relationship.


Tablespaces in Oracle 12c

Tablespaces are the first level of logical organization of your physical storage.

Every 12c database should have the following tablespaces:

  • SYSTEM: Stores the core database objects that are used for running the database itself.

  • SYSAUX: For objects that are auxiliary and not specifically tied to the core features of the database.

  • UNDO: Stores the rollback or undo segments used for transaction recovery.

  • TEMP: For temporary storage.

Each tablespace is responsible for organizing one or more data files. Typically, each tablespace might start attached to one data file, but as the database grows and your files become large, you may decide to add storage in the form of multiple data files.

So what’s the next step to getting your database up and running? You create some areas to store your data. Say your database is going to have sales, human resources, accounting data, and historical data. You might have the following tablespaces:









Segments in Oracle 12c

Segments are the next logical storage structure after tablespaces. Segments are objects in the database that require physical storage and include the following:

  • Tables

  • Indexes

  • Materialized views

  • Partitions

These object examples are not segments and don’t store actual data:

  • Views

  • Procedures

  • Synonyms

  • Sequences

The latter list of objects don’t live in a tablespace with segments. They’re pieces of code that live in the SYSTEM tablespace.

Whenever you create a segment, specify what tablespace you want it to be part of. This helps with performance.

Extents in Oracle 12c

Extents are like the growth rings of a tree. Whenever a segment grows, it gains a new extent. When you first create a table to store items, it gets its first extent. As you insert data into that table, that extent fills up. When the extent fills up, it grabs another extent from the tablespace.

When you first create a tablespace, it’s all free space. When you start creating objects, that free space gets assigned to segments in the form of extents. Your average tablespace is made up of used extents and free space.

When all the free space is filled, that data file is out of space. That’s when your DBA skills come in and you decide how to make more free space available for the segments to continue extending.

Extents aren’t necessarily contiguous. For example, when you create an items table and insert the first 1,000 items, it may grow and extend several times. Now your segment might be made up of five extents. However, you also create a new table.

As each table is created in a new tablespace, it starts at the beginning of the data file. After you create your second table, your first table may need to extend again. Its next extent comes after the second extent. In the end, all objects that share a tablespace will have their extents intermingled.

This isn’t a bad thing. In years past, before Oracle had better algorithms for storage, DBAs spent a lot of their time and efforts trying to coalesce these extents. It was called fragmentation. It’s a thing of the past. Don’t get sucked in! Just let it be. Oracle 12c is fully capable of managing such situations.

There are situations where you have multiple data files in a tablespace. If a tablespace has more than one data file, the tablespace automatically creates extents in a round-robin fashion across all the data files. This is another Oracle performance feature.

Say you have one large table that supports most of your application. It lives in a tablespace made of four data files. As the table extends, Oracle allocates the extents across each data file like this:

1,2,3,4,1,2,3,4,1,2,3,4 . . . and so on

This way, Oracle can take advantage of the data spread across many physical devices when users access data. It reduces contention on segments that have a lot of activity.

Oracle blocks in Oracle 12c

An Oracle block is the minimum unit that Oracle will read or write at any given time.

Oracle usually reads and writes more than one block at once, but that’s up to Oracle these days. You used to have more direct control of how Oracle managed its reads and writes of blocks, but now functionality is automatically tuned. You can tune it manually to a certain extent, but most installations are best left to Oracle.

Regardless, blocks are the final logical unit of storage. Data from your tables and indexes are stored in blocks. The following things happen when you insert a new row into a table:

  • Oracle finds the segment.

  • Oracle asks that segment if there’s any room.

  • The segment returns a block that’s not full.

  • The row or index entry is added to that block.

If no blocks are free for inserts, the segment grabs another free extent from the tablespace. By the way, all this is done by the server process to which you’re attached.

Oracle blocks also have a physical counterpart just like the data files do. Oracle blocks are made up of OS blocks. It is the formatted size of the minimum unit of storage on the device.