In this two-part article, you take a look at the Oracle schema and storage infrastructure because these are a large part of what you, as an Oracle DBA, will be required to manage.
Schemas
An Oracle database can have many schemas contained in it. The schema is a logical structure that contains objects such as segments, views, procedures, functions, packages, triggers, user-defined objects, collection types, sequences, synonyms, and database links. A segment is a data structure that can be a table, index, or temporary or undo segment. The schema name is the user that controls the schema. SYSTEM, SYS, SCOTT, and SH are examples of schemas. Figure 1 shows the relationship between these schema objects.
FIGURE 1. The database and user schemas
Segments, Extents, and Blocks
Oracle database allocates logical database space for the objects and data in the database. As you can see in Figure 1, a schema can have many segments and many segment types. Each segment is a single instance of a table, partition, cluster, index, or temporary or undo segment. So, for example, a table with two indexes is implemented as three segments in the schema. A segment is broken down further into extents, which are a collection of contiguous data blocks. As data is added to Oracle, it will first fill the blocks in the allocated extents and once those extents are full, new extents can be added to the segment as long as space allows. Oracle automatically allocates extents and can manage the sizing of the extents, or a uniform size can be defined. Oracle segment types are listed here:
- Tables The data is kept in rows and columns. This is the heart of your database with tables implemented in one schema and one tablespace. The exception to this is a special type of table called a partitioned table, where the table can be split into different ranges or sets of values called a partition, with each partition implemented in a different tablespace. Remember, however, that each partition is itself a segment and each segment can only reside in one tablespace. Clustered tables are another special case where two tables with a close link between them can have their data stored together in a single block to improve join operations.
- Indexes These are optionally built on tables for performance reasons and to help implement integrity constraints such as primary keys and uniqueness. In previous releases, indexes and tables were kept in separate tablespaces to keep the segments in different areas, but with Automatic Segment Space Management and disk systems, this type of separation is no longer needed. Index segments are like table segments.
- Temporary segments Oracle uses these as a temporary storage area to run a SQL statement. For example, they may be used for sorting data and then discarded once a query or transaction is complete.
- Undo segments These are used to manage the before image of changes, which allows data to roll back if needed and helps provide data consistency for users querying data that is being changed.
Segments can be thought of as physical structures since they actually are used to store data that is kept in a tablespace (although some of this is temporary in nature). There are other structures stored in the schema that are more logical in nature.
Pluggable Databases
The pluggable database is a new feature in Oracle 12c and is another way to organize the storage and objects of the database. The pluggable database (PDB) is a collection of schemas and schema objects that is portable from one container database (CDB) to another. The pluggable database is created in a container database, and has its own storage and the data files that are allocated to the pluggable database (see Figure 2). The storage for the PDB is used for the tablespaces and the objects that are in the PDB.
FIGURE 2. The pluggable databases
The PDB can be created with the database creation assistant or in sqlplus with or without the storage clause.
The PDB is a way to organize the schemas and objects together; similar to objects in a schema, the objects still have segments, extents, and blocks (refer back to Figure 1 for the segments).
Read more about Oracle 12 architecture in Part 2 of this article, including source modules and supporting structures (such as sequences).
Colour is an excellent way to create these different areas.