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).
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.
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: theSYSTEMtablespace and theSYSAUXtablespace. Having more than just theSYSTEMandSYSAUX 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, andUNDO.
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 USERStablespace 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.
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 theSYSTEMandSYSAUX 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 HRschema may own segments 1 and 3, while theRJBschema may own segment 2.HR andRJBare 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.