logo for asktheoracle.net
Home
AskTheOracle Blog
Oracle Tips & Tricks
Oracle Training
Oracle Tutorials
PL/SQL
SQL
Advanced Tutorials
Performance Tuning
Certification
Oracle 10g
Oracle 11g
Oracle and .Net
Oracle Utilities
Developer Tools
Oracle Questions?
Oracle News
Search This Site
About Us
Disclaimer
Privacy Policy
Contact Us
leftimage for asktheoracle.net

Oracle Database Architecture (part 2)

This is part 2 of our overview of the Oracle database architecture. Although this article is specific to Oracle 11g architecture, most of the information is applicable to previous versions of Oracle. There may be extra memory structures and background processes or the name of an object may change but the underlying Oracle architecture for Oracle 11g hasn't changed significantly from earler versions.
Part 1 covered the main components of the Oracle architecture including 
Oracle server concepts and terminology.

This article will cover the logical architecture of the physical data files that are part of an Oracle database.

Oracle Database File Structure

In an Oracle database all data is ultimately stored in files on one or
more disks. Which files are on which disk is not really relevant to our discussion - storage may be virtualised or may be managed by a special type of Oracle instance called ASM (Automatic Storage Management). The disks may also be formatted with a flie system or may be just raw devices (on Unix/Linux systems).

Therefore, the least granular object in the Oracle database architecture is the data file (ignoring physical objects such as disks, disk partitions, logical volumes, etc.) and every database must have at least one data file.

A data file is a physical structure that contains a tablespace which is a logical structure. A data file can hold only one tablespace but a tablespace may extend over more than one datafile.  

A tablespace is a logical grouping of segments, usually of one type but not necessarily so. A tablespace may therefore mix index and table sgements.

A segment is a logical grouping of extents of one type of database object (index ,table, undo, temporary). A segment may span several data files but cannot span more than one tablespace.

An extent comprises one or more contiguous Oracle data blocks allocated in one go. An Oracle block may be an integral multiple of physical blocks on the disk.

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber to our ezine you're missing out on a myriad of tips and techniques to help you become a better, faster, smarter Oracle developer or DBA. Subscribe now and ignite your career.

Let's look at tablespaces, segments and extents in a little more detail.

Tablespaces

A tablespace, as mentioned, is a logical grouping of segments and can be of several types:
  • Temporary tablespace - used to hold temporary objects such as sort data, temporary tables and indexes, temporary LOBs and temporary B-trees
  • Permanent tablespace - hold permanent schema objects such as tables and indexes
  • Bigfile tablespace - has just one datafile which can contain up to 4G blocks, with a 128K block size the file could be as large as 128 terabytes. The advantage of this is that for large databases the number of data files is reduced which can reduce teh amonut of memory required to hold database file information
  • Compressed tablespace - as the name suggests this holds compressed data. Compressing data reduces disk space requirements and memory use in the database buffer cache. It may also reduce query execution time during reads (as less data has to be read). There is extra CPU overhead for data loading and DML but this may be offset by reduced I/O requirements.
  • Locally managed tablespace - extent information is held in the tablespace itself in bitmaps, this reduces contention for the data dictionary and can enhance performance
  • Dictionary managed tablespace - the data dictionary needs to be accessed to locate free space and allocate/deallocate extents
  • Encrypted tablespace - holds encrypted data to protect sensitive data. As with a compressed tablespace, the process of encryption/decryption is transparent to applications
Every Oracle database must have a system tablespace and a sysaux tablespace.

The system tablespace holds the objects needed for the database to run such as the data dictionary and the system rollback segment.

The sysaux tablespace is an auxilliary tablespace for the system tablespace. This holds information about various Oracle products and features used by the databases as well as their schema objects.

Segments

As with tablespaces there are different types of segments:
  • data segment - hold data and may be one of regular, partitioned, clustered or global temporary data segment
  • index segment - holds indexed values and the physical location (rowid) of an indexed value and can be one of regular, partitioned, reverse, bitmap or IOT index segment
  • temporary segment - holds data required temporarily for sorting when the sort is too large to be done just in memory
  • undo segement - contains the before image of blocks updated by tranasctions. This is used for read consistency and for rolling back changes.

Extents

Extents are logical units of database storage made up of logically contiguous data blocks. Whilst logically contiguous, they might not be  physically contiguous on the disk if RAIDs are being used or because of file system implementations. An extents is always contained in one data file.

Allocation of Extents

An initial extent for a data segment is allocated by default when the segment is created. Although the blocks in this initial extent are empty, they are exclusively reserved for this segment and cannot be used by any other segment unless they are deallocated (dropped) from the current . The first data block of every segment contains a directory of the extents in the segment.shows the initial extent in a segment in a data file that previously contained no data.