Understand the Oracle Database 10g Infrastructure - Oracle
While learning the job of DBA cannot be done entirely in a few short months, a great place to start learning is this chapter. It provides an overview of the main tasks of a DBA such as the daily operations and how to manage database objects, users and space. (From the book Oracle Database 10g: A Beginner's Guide, by Ian Abramson, Michael Abbey, and Michael Corey, McGraw-Hill/Osborne, 0072230789.)
CRITICAL SKILL 3.4 -- Understand the Oracle Database 10g Infrastructure
Oracle’s memory and process infrastructure have already been discussed in Chapter 1. In this section, we will take a look at the Oracle schema and storage infrastructure since these are a large part of what you will be required to manage.
An Oracle database has many schemas contained in it. The schema is a logical structure that contains objects like 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. Examples of schemas are the System, Sys, Scott, and SH schemas. Figure 3-1 shows the relationship between these schema objects.
Segments, Extents, and Blocks
As you can see in Figure 3-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 segment types are listed here:
Tables are where the data is kept in rows and columns. This is the heart of your database and a table is 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 and each partition can be implemented in a different tablespace.
Figure 3-1: The database and user schemas
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 are optionally built on tables for performance reasons and to help implement integrity constraints such as primary keys and uniqueness.
Temporary segments are used as a temporary storage area by Oracle to run an SQL statement. For example, they may be used for sorting data and then discarded once a query or transaction is complete.
Undo segments are used to manage the before image of changes to allow data to roll back if needed and to help provide data consistency for users querying data that is being changed. There will be more on this in Chapter 5.
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.
Logical Schema Structures
Not everything stored in a database and schema is data. Oracle also manages source modules as well as supporting structures such as sequences that are used to populate new unique and primary key values when inserting data into the database. These objects belong to a schema and are stored in the Oracle Catalog. These can all be easily managed through OEM, as shown in Figure 3-2. Take a look at the following for a brief description of these logical structures:
Views give you the capability of subsetting a table and combining multiple tables through a single named object. They can be thought of as a stored query. With the exception of a special type of view called a materialized view that is used for data warehousing, data is not stored in views. They are simply a new way of defining access to the underlying tables. These can be used for security, performance, and ease-of-use.
Synonyms are used to create a new name or alias for another database object such as a table, view, another synonym, and sources such as a procedure, package, function, java class, and so on. They can be used to simplify access. As with views, data is not stored in a synonym.
Sequences are used to generate new unique numbers that can be used by applications when inserting data into tables.
Source programs can be stored in the catalog and written in Oracle’s proprietary PL/SQL or Java. PL/SQL source types include business logic that can be written as Packages, Procedures, and Functions. Triggers can also be used to implement business logic, but are often used to implement data integrity since they are not executed directly by a user or source program, but rather are automatically executed when an action is performed on the database. Java Sources and Java Classes are also implemented directly in Oracle. This server-side support of application logic improves performance since the logic resides with the data and it also improves performance.
User types can be created by you to support object-oriented development. Array types, Object types, and Table types can all be created by you. As well, the Oracle XML Schema Processor supports XML processing by adding data types to XML documents that can be used to ensure the integrity of data in XML documents.
So now that you have seen all of our schema objects, it’s time to tie these together to our storage architecture.
As shown in Figure 3-1, the physical schema objects are stored as segments in the database. Each segment can only be stored in a single tablespace and a tablespace can be made up of one or more datafiles. If a tablespace is running out of space, you can expand the datafiles it is made up of, or you can also add a new datafile to the tablespace. A datafile can only store data for a single tablespace.
A single tablespace can store data for multiple segments and in fact for several segment types. Segments from multiple schemas can also exist in the same tablespace. So, for example, table_a from schema1 and index_b from schema2 can both be implemented in the same tablespace. Oh and by the way, a tablespace can only store data for a single database.
The logical structures such as views and source code are stored in the Oracle catalog but are part of a schema. So, this means that the Oracle-supplied SH schema can contain all of the objects that it needs to run the entire application under its own schema name. This provides strong security and management benefits.
Name five areas that you will need to address as a DBA.
What is a schema and what does it contain?
Can a tablespace store more than one segment type?
Under which circumstances would you bother to start up the database in nomount mode?
Progress Check Answers
As a DBA, you will need to address architecture, capacity planning, backup and recovery, and security and performance, among others.
A schema is a logical structure that contains objects like segments, views, procedures, functions, packages, triggers, user-defined objects, collection types, sequences, synonyms, and database links.
A single tablespace can store data for multiple segments and different segment types. Segments from multiple schemas can also exist in the same tablespace. So, for example, table_a from schema1 and index_b from schema2 can be implemented as two segments in the same tablespace.
When started in nomount mode, the parameter file is read and memory structures and processes are started for the instance. The database is not yet associated with the instance. You will use this in cases where you need to re-create the controlfile.
This chapter is from Oracle Database 10g: A Beginner's Guide, by Abramson, Abbey and Corey. (McGraw-Hill/Osborne, 2004, ISBN: 0072230789). Check it out at your favorite bookstore today. Buy this book now.