Oracle Database XE Administration

If you’re looking for an overview of the Oracle database architecture, keep reading. This article series focuses on what you need to know to use the Oracle Database XE home page. It is excerpted from chapter 28 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

In Chapter 27, you installed the Oracle database component and performed the basic configuration steps to round out your PHP environment. In this chapter, we’ll cover an overview of the Oracle architecture and how to use several Oracle-supplied utilities, and give you a whirlwind tour of the other tools available at the Oracle Database XE home page.

While your focus as a PHP/Oracle developer is primarily as, well, a developer, your DBA duties will most likely take a small fraction of the time you spend with Oracle Database XE. However, in an environment where you are using Oracle Database XE, you are most likely wearing many different hats, and one of them is the DBA hat. As a result, you need to know the basics of being an Oracle DBA when the need arises.

You’ll see some of the tools we introduce in this chapter covered in more depth in Chapter 29. These tools, such as SQL Commands in the Oracle Database XE Web interface or SQL*Plus on the command line are useful regardless of whether you’re a developer, a database administrator, or a casual user who needs an occasional ad hoc query against the company’s sales history database.

Understanding the Oracle Architecture

As they say, you don’t have to know how a car’s antilock brakes work to drive a car, and you don’t need to be able to design a cell phone to call someone on a cell phone. The same could be said about the architecture of Oracle Database XE: you don’t necessarily need to know how Oracle stores data blocks on disk, but knowing the general disk and memory architecture model goes a long way to help you design and use the database efficiently. You also need to know the terminology surrounding Oracle components: tablespaces, datafiles, segments, and extents. Even if you’re only an occasional database administrator for your Oracle Database XE installation, the architectural overview in the next few sections will help you be an effective application developer as well.

An Oracle server contains both a database and an instance. The database consists of the files on disk. These files store the data itself; the state of the database, in a small, most likely replicated file called the control file; and changes to the database’s data, in files called redo log files. The instance refers to the Oracle memory processes and memory structures that reside in your server’s memory and access the database in the disk files. This distinction becomes more obvious when you are using Real Application Clusters (RAC), which is two or more Oracle instances sharing one database for performance, scalability, or availability.

If you are still convinced that the database will never need any manual tuning (which may very well be the case with Oracle Database XE!), or your database storage needs will be relatively static over time, feel free to skip ahead to the section “Connecting to the Database.”

Oracle Storage Structures

It’s important to distinguish the logical database storage structure from the physical database structure. As with most computing paradigms, the logical version hides the implementation of the paradigm in the physical implementation, either to make application development easier or to help communicate the architectural details to managers who are not involved in the technical aspects of a database computing environment on a daily basis.

From a database perspective, then, the logical database structures represent components such as tables, indexes, and views—what you see from a user’s or a developer’s point of view. The physical database structures, on the other hand, are the underlying storage methods on the disk file system including the physical files that compose the database.

Don’t worry if the next few sections seem a bit dry; you won’t need them to get into the trenches with Oracle. Most, if not all, of the storage and memory structures are tuned quite well by Oracle anyway; see Chapter 38 for more details. Skip to the section titled “Connecting to the Database” and come back here if and when you’re curious as to what’s going on under the hood.

{mospagebreak title=Logical Storage Structures}

The Oracle database is divided into increasingly smaller logical units to manage, store, and retrieve data efficiently and quickly. Figure 28-1 shows the relationships between the logical structures in an Oracle database: tablespaces, segments, extents, and blocks.

Figure 28-1.  Oracle Database XE logical storage structures

The logical storage management of the database’s data is independent of the physical storage of the database’s physical files on disk. This makes it possible for changes you make to the physical structures to be transparent to the database user or developer at the logical level.


A tablespace is the highest-level logical object in the database. A database consists of one or more tablespaces. A tablespace will frequently group together similar objects, such as tables, for a specific business area, a specific function, or a specific application. You can reorganize a particular tablespace or back it up with minimal impact to other users whose data may be changing at the same moment in other tablespaces in the database.

All Oracle databases require at least two tablespaces: the SYSTEM tablespace and the SYSAUX tablespace. Having more than just the SYSTEM and SYSAUX tablespaces is highly recommended when creating a database; a default installation of Oracle Database XE includes five tablespaces. In the illustration of logical structures in Figure 28-1, you can see the five default tablespaces: SYSTEM , SYSAUX , TEMP , USERS , and UNDO .


A tablespace is further broken down into segments. A database segment is a type of object that a user typically works with, such as a table or an index. The USERS tablespace in Figure 28-1 consists of five segments, which could be tables, indexes, and so forth. It’s important to note that this is the logical representation of these objects; the physical representation of these objects in the operating system files will most likely not match the logical representation. For example, extents 1 and 2 in segment 3 will most likely not be adjacent on disk and may even be in separate datafiles. We discuss datafiles in the section titled “Physical Storage Structures.”


The next-lowest logical grouping in a database is the extent. A segment groups one or more extents allocated for a specific type of object in the database. Segment 3 in Figure 28-1 consists of four extents. Note that an extent cannot cross segment boundaries. Also, a segment, and subsequently an extent, cannot cross tablespace boundaries.

Database Blocks

The most granular logical object in a database is the database block (also known as an Oracle block), the smallest unit of storage in an Oracle database. Every database block in a tablespace has the same number of bytes. Starting with Oracle9i, different tablespaces within a database can have database blocks with different sizes. Typically, one or more rows of a table will reside in a database block, although very long rows may span several database blocks.

A database block can have a size of 2KB, 4KB, 8KB, 16KB, or 32KB. Once any tablespace, including the SYSTEM and SYSAUX tablespaces, is created with a given block size, it cannot be changed. If you want the tablespace to have a larger or smaller block size, you need to create a new tablespace with the new block size, move the objects from the old tablespace to the new tablespace, and then drop the old tablespace.


A schema is another logical structure that can classify or group database objects. A schema has a one-to-one correspondence with a user account in the Oracle database, although you may create a schema to hold only objects that other database users reference. For example, in Figure 28-1, the HR schema may own segments 1 and 3, while the RJB schema may own segment 2. HR and RJB are both user accounts and schemas. Segments 1 and 3 may be the tables HR.EMPLOYEES and HR.DEPARTMENTS, while segment 2 may be the index RJB.PK_ACCT_INFO_IX.

A schema is not directly related to a tablespace or any other logical storage structure; the objects that belong to a schema may reside in many different tablespaces. Conversely, a tablespace may hold objects for many different schemas. A schema is a good way to group objects in the database for purposes of security, manageability, and access control.

Please check back next week for the second part of this article.

Google+ Comments

Google+ Comments