Free Programming Books
Free download ebooks on computer and programming

In the first three chapters, I set the stage for working with Oracle. It's time now to learn about the fundamental structures of Oracle Database 10g. Oracle uses a set of logical structures called data blocks, extents, segments, and tablespaces as its building blocks. Oracle's physical database structure consists of data files and related files. Oracle memory structures and a set of database processes constitute the Oracle instance, and are responsible for actually performing all the work for you in the database.

To understand how the Oracle database works, you need to understand several concepts, including transaction processing, backup and recovery, undo and redo data, the optimization of SQL queries, and the importance of the data dictionary. Oracle's key features include the Recovery Manager, SQL*Plus and iSQL*Plus, Oracle Backup, the Oracle (job) Scheduler feature, the Database Resource Manager, and the Oracle Enterprise Manager management tool. This chapter provides an outline of the important Oracle automatic management features, as well as the sophisticated built-in performance tuning features, including the new Automatic Workload Repository, the Automatic Database Diagnostic Monitor, and the advisor-based Management Framework.

Before you delve deeply into the logical and physical structures that make up an Oracle database, however, you need to be clear about a fundamental concept-the difference between an Oracle instance and an Oracle database. It is very common for people to use the terms interchangeably, but they refer to different things altogether.

An Oracle database consists of files, both data files and Oracle system files. These files by themselves are useless unless you can interact with them somehow, and this requires the help of the operating system, which provides processing capabilities and resources, such as memory, to enable you to manipulate the data on the disk drives. When you combine the specific set of processes created by Oracle on the server with the memory allocated to it by the operating system, you get the Oracle instance.

You'll often hear people remarking that the "database is up," though what they really mean is that the "instance is up." The database itself, in the form of the set of physical files it's composed of, is of no use if the instance is not up and running. The instance performs all the necessary work for the database.

Oracle Database Structures

In discussing the Oracle database architecture, you can make a distinction between the physical and logical structures. You don't take all the data from the tables of an Oracle database and just put it on disk somewhere on the operating system storage system. Oracle uses a sophisticated logical view of the internal database structures that helps in storing and managing data properly in the physical data files. By organizing space into logical structures and assigning these logical entities to users of the database, Oracle databases logically separate the database users (who own the database objects, such as tables) from the physical manifestations of the database (data files and so forth). The following sections discuss the various logical and physical data structures.

The Logical Database Structures

Oracle databases use a set of logical database storage structures in order to manage the physical storage that is allocated in the form of operating system files. These logical structures, which primarily include tablespaces, segments, extents, and blocks, allow Oracle to control the use of the physical space allocated to the Oracle database.

Taken together, a set of related logical objects in a database is called a schema. Remember that Oracle database objects, such as tables, indexes, and packaged SQL code, are actually logical entities. Dividing a database's objects among various schemas promotes ease of management and a higher level of security.

Let's look at the logical composition of an Oracle database from the bottom up, starting with the smallest logical components and moving up to the largest entities:

• Data blocks: The Oracle data block is at the foundation of the database storage hierarchy and is the basis of all database storage in an Oracle database. A data block consists of a number of bytes of disk space in the operating system's storage system. All Oracle's space allocation and usage is in terms of Oracle data blocks.
• Extents: An extent is two or more contiguous Oracle data blocks, and this is the unit of space allocation.
• Segments: A segment is a set of extents that you allocate to a logical structure like a table or an index (or some other object).
• Tablespaces: A tablespace is a set of one or more data files, and usually consists of related segments. The data files contain the data of all the logical structures that are part of a tablespace, like tables and indexes.

The following sections explore each of these logical database structures in detail.

Data Blocks

The smallest logical component of an Oracle database is the data block.Data blocks are defined in terms of bytes. For example, you can size an Oracle data block in units of 2KB, 4KB, 8KB, 16KB, or 32KB (or even larger chunks), and it is common to refer to the data blocks as Oracle blocks. The storage disks on which the Oracle blocks reside are themselves divided into disk blocks, which are areas of contiguous storage containing a certain number of bytes-for example, 4,096 or 32,768 bytes (4KB or 32KB; each kilobyte has 1,024 bytes).

How Big Should the Oracle Block Size Be?

You, as the DBA, have to decide how big your Oracle blocks should be and set the DB_BLOCK_SIZE parameter in your Oracle initialization file (the init.ora file). Think of the block size as the minimum unit for conducting Oracle's business of updating, selecting, or inserting data. When a user selects data from a table, the select operation will "read," or fetch, data from the database files in units of Oracle blocks.

If you choose the common Oracle block size of 8KB, your data block will have exactly 8,192 bytes. If you use an Oracle block size of 64KB (65,536 bytes), even if you just want to retrieve a name that's only four characters long, you'll have to read in the entire block of 64KB that happens to contain the four characters you're interested in.