Oracle Database XE Administration

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.

Oracle Storage StructuresIt’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.

Oracle Database XE Administration - Logical Storage Structures

The Oracle database is divided into increasingly smaller logical units to manage, store, and retrieve data efficiently and quickly. Figure shows the relationships between the logical structures in an Oracle database: tablespaces, segments, extents, and blocks.
Figure   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.
 
Tablespaces 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,  you can see the five default tablespaces:SYSTEM,SYSAUX,TEMP,USERS, andUNDO.
 
Segments 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 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.”

Extents
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,  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 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.
 
Schemas 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, 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.