Oracle in a Nutshell | O'Reilly Media

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The architecture of the Oracle relational database management system is a unique one. Features such as rollback buffers and Real Application Clusters are part of the innate Oracle architecture and make it possible for the Oracle database to provide a wide range of features not found in any other database.

This chapter provides a brief overview of the architecture and underlying components of the Oracle database, as well as a description of the different "flavors" of the database currently offered by Oracle Corporation: its various editions, versions, and major features. By understanding how Oracle accomplishes its tasks, you'll be better equipped to understand the rest of the information in this book—the Oracle initialization parameters and data dictionary views, its various language statements, the details of the various Oracle tools and utilities, and the major aspects of tuning and optimization.

Two entities are sometimes referred to as an Oracle database—the instance and the database—and people often confuse them.

In the Oracle world, the term database refers to the physical storage of information, while instance refers to the software executing on the server that provides access to the information in the database. The instance runs on the computer or server; the database is stored on the disks attached to the server, as shown in Figure 1-1.

Figure 1-1: An instance and a database

The database is physical: it consists of files stored on disks. The instance is logical: it consists of in-memory structures and processes on the server. An instance can connect to one and only one database. Instances are temporal, but databases, with proper maintenance, last forever.

Users do not directly access the information in an Oracle database. Instead, they pass requests for information to an Oracle instance.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Two entities are sometimes referred to as an Oracle database—the instance and the database—and people often confuse them.

In the Oracle world, the term database refers to the physical storage of information, while instance refers to the software executing on the server that provides access to the information in the database. The instance runs on the computer or server; the database is stored on the disks attached to the server, as shown in Figure 1-1.

Figure 1-1: An instance and a database

The database is physical: it consists of files stored on disks. The instance is logical: it consists of in-memory structures and processes on the server. An instance can connect to one and only one database. Instances are temporal, but databases, with proper maintenance, last forever.

Users do not directly access the information in an Oracle database. Instead, they pass requests for information to an Oracle instance.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A database consists of a collection of physical files and logical structures, described in the following sections.

A database has a specific name, assigned when you create it. You cannot change the name of a database once you have created it, although you can change the name of the instance that accesses the database.

A tablespace is a logical structure, which exists only within the context of an Oracle database. Each tablespace is composed of physical structures called datafiles; each tablespace must consist of one or more datafiles, and each datafile can belong to only one tablespace. When you create a table, you can specify the tablespace in which you want to create it. Oracle will then find space for it in one of the datafiles that make up the tablespace.

Figure 1-2 shows the relationship of tablespaces to datafiles for a database. This figure shows two tablespaces within an Oracle database. When you create a new table in this Oracle database, you may place it in the DATA1 tablespace or the DATA2 tablespace. It will physically reside in one of the datafiles that make up the specified tablespace.

Figure 1-2: Tablespaces and datafiles

A tablespace is a logical view of the physical storage of information in an Oracle database. Three fundamental types of physical files make up an Oracle database:

Control files
Datafiles
Redo log files

Other files, such as password files and instance initialization files, are used within a database environment, but the three fundamental types listed represent the physical database itself. Figure 1-3 illustrates the three types of files and their interrelationships.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

An Oracle instance can be defined as an area of shared memory and a collection of background processes.

The area of shared memory for an instance is called the System Global Area, or SGA. The SGA is not really one large undifferentiated section of memory—it's made up of various components described in the next section, "Memory Structures for an Instance." All the processes of an instance—system processes and user processes—share the SGA.

Prior to Oracle9i, the size of the SGA was set when the Oracle instance started. The only way you could change the size of the SGA or any of its components was to change the appropriate initialization parameters and stop and restart the instance. With Oracle9i, you can now change the size of the SGA or its components while the Oracle instance is still running.

The background processes interact with the operating system and each other to manage the memory structures for the instance. These processes also manage the actual database on disk and perform general housekeeping for the instance.

Other physical files can be considered as part of the instance as well:

Instance initialization file: The initialization file contains a variety of parameters that configure how the instance will operate: how much memory it will use, how many users it will allow to connect, what database the instance actually provides access to, and so on. You can alter many of these parameters dynamically at either the systemwide or session-specific level. Up until Oracle9i, the initialization file was called INIT.ORA. Oracle9i introduced the SPFILE, which performs the same function as the INIT.ORA file but can also persistently store changes to initialization parameters that are made while Oracle9i is running. Refer to your operating system-specific documentation for the default location of the INIT.ORA file on your system.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle version numbers, like the product packaging we discuss in the next section, have more to do with the marketplace than the technology. The Oracle database, like all software products, has a periodic release cycle, but the exact timing and contents of those releases is shaped, to some degree, by sales requirements as well as the development cycle. Because of nontechnical factors, the naming conventions for versions can change at any time.

As of this writing, Oracle seems to have settled on a consistent version naming scheme. Up until Oracle8, each major version was named with an increasing version number (e.g., Oracle6, Oracle7, Oracle8). However, the release after Oracle8 was named Oracle8i, connecting it with the Internet (and causing formatting problems for authors ever since!). The next version was named Oracle9i, and the upcoming version is slated to be called Oracle10i—although that may change by the time the version is released.

Typically, the OracleNi versions have had an intermediate release, also typically identified as Release 2. Release 2s generally are the first major maintenance release; they follow the main release and frequently contain enhancements that were planned for the main release but that could not make the deadline.

This book assumes that Oracle8 was the beginning of time. Any features that were not present in that version are not discussed. Any features that were added or dropped since then are duly noted.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Even more so than versions, the actual packaging of Oracle releases is controlled primarily by sales forces, rather than by technical forces. The decision to include a feature in one or more versions of the database, as well as the decision to segregate functionality in an extra-cost option, is, for the most part, arbitrary.

There are four basic editions of the Oracle database:

Standard Edition: The lowest cost server edition of Oracle, which does not include all the capabilities of the Oracle database. The extra options described in the next section cannot be used with Standard Edition.
Enterprise Edition: The complete Oracle database, which is required for the use of the extra options described in the next section.
Personal Edition: A single-user version of the Oracle database, available only for Windows. This edition includes all the functionality of all the options for Enterprise Edition, where appropriate.
Lite: A trimmed-down version of the Oracle database designed for mobile use.

Although Oracle sometimes decides to fold formerly extra-cost options into one or more editions of the standard product, the company has never yet decided to take functionality out of a lesser edition in order to force upgrades.

Most of the functionality described in this book is included in all versions of the database. The following functionality is available in Enterprise Edition, but not Standard Edition, as of the time of this writing:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle is designed to be a very flexible and configurable system. These qualities are absolute necessities for a database that can be run on dozens of different hardware platforms in a multitude of configurations, supporting an almost infinite variety of applications and users. In order to achieve the needed flexibility, Oracle must provide the DBA with a simple method of specifying certain operational characteristics of the database in a clear and consistent manner. DBAs specify most of these characteristics by setting and resetting values for the database initialization parameters , commonly referred to as INIT.ORA parameters . The fundamental goal is to set or reset these parameters in a way that gets the database running at its peak performance level.

Each initialization parameter controls a specific aspect of the Oracle server. Together, all of the parameters combine to shape the generic Oracle database technology to fit your particular needs.

The following is a typical initialization file for a general-purpose installation of Oracle9i:

DB_NAME = "ORA9"
DB_DOMAIN = homeserver
INSTANCE_NAME = ORA9
SERVICE_NAMES = ORA9.homeserver

DB_FILES = 1024
DB_BLOCK_SIZE = 8192

COMPATIBLE = 9.0.0
SORT_AREA_SIZE = 65536
SORT_AREA_RETAINED_SIZE = 65536

CONTROL_FILES = ("C:\Oracle\oradata\ORA9\control01.ctl",
"D:\Oracle\oradata\ORA9\control02.ctl",
"E:\Oracle\oradata\ORA9\control03.ctl")

OPEN_CURSORS = 100
CURSOR_SHARING = similar

MAX_ENABLED_ROLES = 30
DB_FILE_MULTIBLOCK_READ_COUNT = 8
DB_BLOCK_BUFFERS = 2048

SHARED_POOL_SIZE = 19728640
LARGE_POOL_SIZE = 614400
JAVA_POOL_SIZE = 25971520

LOG_CHECKPOINT_INTERVAL = 10000
LOG_CHECKPOINT_TIMEOUT = 1800

PROCESSES = 200
PARALLEL_MAX_SERVERS = 5
LOG_BUFFER = 32768
MAX_DUMP_FILE_SIZE = 10240  # limit trace file size to 5M each
GLOBAL_NAMES = true

ORACLE_TRACE_COLLECTION_NAME = ""
BACKGROUND_DUMP_DEST = D:\Oracle\admin\ORA9\bdump
RESOURCE_MANAGER_PLAN = system_plan
USER_DUMP_DEST = D:\Oracle\admin\ORA9\udump
TRACEFILE_IDENTIFIER = ORA9

REMOTE_LOGIN_PASSWORDFILE = exclusive
OS_AUTHENT_PREFIX = ""

PLSQL_COMPILER_FLAGS = debug
UNDO_MANAGEMENT = auto

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The name and location of the database initialization parameter file on your system depends upon the Oracle version and the operating system you are running, as described in the following sections.

In Oracle releases prior to Oracle9i, initialization parameters are specified in the INIT.ORA file. This file is usually named in the form INITsid.ORA, where sid is the SID, or system identifier, for your particular Oracle instance. The SID is a unique name used to identify a particular instance across your entire environment.

Refer to the Oracle documentation for the default location of this file for your particular operating system.

Beginning with Oracle9i, Oracle introduced the concept of the server parameter file. This file, known as SPFILE , differs from the standard INIT.ORA file in a number of ways:

It is a binary file, rather than a text-based file.
It is stored on the server, rather than on a client machine.
It can maintain changes to parameter values over the shutdown and startup of an instance.

This last point is the important part of SPFILE. If your database is running under Oracle9i, any changes you make to configuration parameters via the ALTER SYSTEM statement will be saved as part of the permanent configuration file. That means that if you change any of your database parameter values for tuning purposes, you won't also have to change one or more INIT.ORA files so that the new values will persist. You also have the option of making dynamic changes to parameters without making them a part of the SPFILE; you do this by including the SCOPE clause in the ALTER SYSTEM statement, using the following syntax:

ALTER SYSTEM SET parameter_name = parameter_value

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The remainder of this chapter describes the Oracle initialization parameters. We show the parameter names here in uppercase for readability, but you can specify parameters in upper, lower, or mixed case in the parameter file.

In addition to the initialization parameters listed in this chapter, which apply to most Oracle systems, some additional parameters are specific to a particular hardware platform or operating system. These parameters are documented in the Installation Guide, User Guide, and/or Release Notes for your release of Oracle.

We've grouped the parameters presented in the following sections in a number of functional categories; within each category, they are listed alphabetically. We've arranged the parameters this way because you will frequently use a group of parameters to control a particular area of operation, such as auditing or job management. If you need to find a particular parameter, however, check the index; you'll find an alphabetical listing of all parameters under "initialization parameters."

These functional categories are:

Auditing                           National Language Support (NLS)
Backup and recovery                Optimization and performance
Clustered databases                Oracle Trace
Cursors                            Parallel execution
Database links                     Parameters
Distributed operations and         PL/SQL
  Heterogeneous Services           Remote sites
I/O and space management           Rollback (undo/redo) management
Java                               Security
Jobs                               Shared Server/Multi-Threaded 
Licenses                             Server (MTS)
Locking and transactions           Sorts
Logging and archiving              Standby databases
Memory management                  System operations
Names                              Miscellaneous parameters

For each parameter, we include the following information, where appropriate:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

One of the most important features of a database is the way that it supports multiple users. The Oracle database has a concurrency scheme to support concurrent access by multiple users called multiversion read consistency (MVRC) that is unique among major databases. This feature is a great benefit to application developers, because it removes the need for them to worry about handling most locking issues in their own code. The ability to support MVRC is built deeply into the architecture of the Oracle database.

This chapter summarizes the concepts involved in implementing a concurrency scheme and describes briefly how Oracle's MVRC features work.

Several important concepts are necessary to understanding concurrency and how it works in the Oracle system.

The transaction is the bedrock of data integrity in multiuser databases and the foundation of all concurrency schemes. A transaction is defined as a single indivisible piece of work that affects some data. All of the modifications made to data within a transaction are either uniformly applied to a relational database with a COMMIT statement, or the data affected by the changes is uniformly returned to its initial state with a ROLLBACK statement. Once a transaction is committed, the changes made by that transaction become permanent and are made visible to other transactions and other users.

Transactions always occur over time, although most transactions occur over a very short period of time. Because the changes made by a transaction are not official until the transaction is committed, this means that each individual transaction must be isolated from the effects of other transactions. The mechanism used to enforce transaction isolation is the lock.

A database uses a system of locks to prevent transactions from interfering with each other. One transaction could interfere with another by attempting to change a piece of data that another transaction is in the process of also changing. Figure 3-1 illustrates this potential problem. Transaction A reads a piece of data. Transaction B reads the same piece of data and commits a change to the data. When Transaction A goes to commit the data, its change unwittingly overwrites the changes made by Transaction B, resulting in a loss of data integrity.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Several important concepts are necessary to understanding concurrency and how it works in the Oracle system.

The transaction is the bedrock of data integrity in multiuser databases and the foundation of all concurrency schemes. A transaction is defined as a single indivisible piece of work that affects some data. All of the modifications made to data within a transaction are either uniformly applied to a relational database with a COMMIT statement, or the data affected by the changes is uniformly returned to its initial state with a ROLLBACK statement. Once a transaction is committed, the changes made by that transaction become permanent and are made visible to other transactions and other users.

Transactions always occur over time, although most transactions occur over a very short period of time. Because the changes made by a transaction are not official until the transaction is committed, this means that each individual transaction must be isolated from the effects of other transactions. The mechanism used to enforce transaction isolation is the lock.

A database uses a system of locks to prevent transactions from interfering with each other. One transaction could interfere with another by attempting to change a piece of data that another transaction is in the process of also changing. Figure 3-1 illustrates this potential problem. Transaction A reads a piece of data. Transaction B reads the same piece of data and commits a change to the data. When Transaction A goes to commit the data, its change unwittingly overwrites the changes made by Transaction B, resulting in a loss of data integrity.

Figure 3-1: Transactions over time

Normally, two types of locks are used to avoid this problem. The first type of lock is called a

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle's concurrency scheme, multiversion read consistency (MVRC), guarantees that a user sees a consistent view of the data he requests. If another user changes the underlying data during the query execution, Oracle maintains a version of the data as it existed at the time the query began. If transactions were underway but uncommitted at the time the query began, Oracle will ensure that the query does not see the changes made by those transactions. The data returned to the query will reflect all committed transactions at the time the query started.

This feature has several effects on the way that queries impact the database and its performance:

Oracle does not place any locks on data for read operations. This means that a read operation will never block a write operation. Even if a database places a single lock on a single row as part of a read operation, it can still cause contention in the database, especially because most database tables tend to concentrate update operations around a few hot spots of active data.
A write operation will never block a read operation, because MVRC will simply provide a version of the data that existed before the write operation began.
A user gets a complete snapshot view of the data, accurate at the point in time that the query began. A row that is retrieved at the end of a result set may have been changed since the time the result set retrieval began. But because Oracle keeps a version of the row as it existed at the start of the query, you always get a consistent view of the data at a single point in time.
A write operation will block another write operation only if it attempts to write the same row.

Three data structures are used by Oracle to implement multiversion read consistency:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle provides a variety of features that can help secure your database from unauthorized access and can help protect your data from being seen or manipulated by unauthorized users. This chapter focuses on the major security-related concepts for Oracle—authentication, profiles, privileges, roles, and auditing—and specifies the syntax for controlling security in an Oracle database.

In addition, we include brief discussions of some of the more advanced security options for Oracle. Detailed information on these more specialized and/or extra-cost options is outside the scope of this book. If your site has purchased the Advanced Security or Label Security options, consult Oracle documentation for information.

Authentication is the process of recognizing authorized users. Basically, authentication is the system's way of confirming that you are who you say you are. Oracle security is primarily based on the concept of individual authorized users.

The Oracle database creates two users when you install Oracle:

SYS: The schema for SYS contains the base tables and views used for the data dictionary. You should never change any of these tables. The SYS user has the DBA role. (Roles are described in the "Roles" section later in this chapter.) The default password for SYS is CHANGE_ON_INSTALL.
SYSTEM: The SYSTEM username is used to create additional tables and views for administrative information. The SYSTEM user has the DBA role. The default password for SYSTEM is MANAGER.

When you create an Oracle database using the CREATE DATABASE statement, you can use the USER SYS IDENTIFIED BY password and USER SYSTEM IDENTIFIED BY

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Authentication is the process of recognizing authorized users. Basically, authentication is the system's way of confirming that you are who you say you are. Oracle security is primarily based on the concept of individual authorized users.

The Oracle database creates two users when you install Oracle:

SYS: The schema for SYS contains the base tables and views used for the data dictionary. You should never change any of these tables. The SYS user has the DBA role. (Roles are described in the "Roles" section later in this chapter.) The default password for SYS is CHANGE_ON_INSTALL.
SYSTEM: The SYSTEM username is used to create additional tables and views for administrative information. The SYSTEM user has the DBA role. The default password for SYSTEM is MANAGER.

When you create an Oracle database using the CREATE DATABASE statement, you can use the USER SYS IDENTIFIED BY password and USER SYSTEM IDENTIFIED BY password clauses to protect access by these powerful precreated users.

You can create your own users with the CREATE USER statement. You can modify user characteristics with the ALTER USER statement.

CREATE USER

CREATE USER username 
        IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS 'external_name'}
        [DEFAULT TABLESPACE tablespace_name]
        [TEMPORARY TABLESPACE tablespace_name]
        [QUOTA {integer (K | M) | UNLIMITED} ON tablespace_name]
                  [QUOTA {

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

A profile can be associated with a user to limit the resources available to that user or to specify a condition on how passwords are administered. By limiting the computing resources that any one individual can use, you can prevent any one user from exhausting too many resources and affecting other users. (This is known as denial of service.) By placing limits on how passwords are administered, you can help to safeguard the authentication process for your Oracle database.

Profiles can be used if you enable dynamic resource limits with either the RESOURCE_LIMIT initialization parameter or the ALTER SYSTEM SET statement. Once a profile has been defined with the CREATE PROFILE command, you can assign a user to a profile with either the CREATE USER or ALTER USER statement.

CREATE PROFILE

CREATE PROFILE profile_name LIMIT {resource_parameter | password_parameter}

Allows you to create a profile and assign different types of resource limits to the profile. You can specify multiple parameters for a role.

Keywords—all parameters

The following values can be used for both resource and password parameters, unless specified otherwise in the parameter descriptions:

UNLIMITED: Specifies no limit for the particular parameter.
DEFAULT: Specifies that the parameter assumes the value specified for the DEFAULT profile. Initially, all values for the DEFAULT profile are specified as UNLIMITED. You can change the values for the DEFAULT profile with the ALTER PROFILE statement.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Privileges are rights that are assigned to individual users or roles. There are two basic types of privileges:

System privileges: Give the user or role the ability to perform certain system operations
Schema object privileges: Give the user or role access privileges on individual schema objects

System privileges relate to the Oracle instance as a whole—for example, a privilege on all of a type of object (such as all tables), while schema privileges relate to a specific schema object within an Oracle database (for example, a particular accounting table).

This section provides summary information for all of the Oracle system privileges.Several varieties of system privileges apply to more than one type of privilege:

ANY: Gives the privilege to perform the action on objects in any schema. Without this keyword, a privilege is granted only on objects within the user's schema. By default, the ANY keyword gives the user privilege on all objects in all schemas, including the SYS schema. To prevent access to the SYS schema with the ANY privilege, you can set the O7_DICTIONARY_ACCESSIBILITY initialization parameter to FALSE.
ALTER: Gives the privilege to alter the type of object.
CREATE: Gives the privilege to create the type of object.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To assign privileges to a user or a role, you use the GRANT command. To remove a privilege from a user or a role, you use the REVOKE command.

The following keywords and clauses are valid for both the GRANT and REVOKE commands:

system_privilege: A system privilege, as described in the earlier "System Privileges" section.
role: An existing role.
ALL PRIVILEGES: Grants or revokes all system privileges except SELECT ANY DICTIONARY. For objects, grants all privileges that you have for the object, and the keyword PRIVILEGES is optional.
grantee: Can be one or more users, roles, or the keyword PUBLIC, which grants or revokes the privilege to all database users. If multiple grantees are specified, they should be separated by commas.
object_privilege: An object privilege, as described earlier in this chapter.
column_name: One or more columns on which to grant or revoke the INSERT, REFERENCES, or UPDATE object privileges. If you do not specify a column, the grantee has privilege on all columns in the table or view.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Granting individual privileges to individual users can incur a substantial amount of overhead, especially for enterprise systems with large numbers of users. Roles are designed to simplify the management of privileges.

Privileges can be granted to roles and then users assigned to roles. Privilege maintenance is performed on roles and affects users with those roles. In addition, roles can be selectively enabled and disabled for users, depending on context. In this way, you can use roles to combine together sets of privileges that will be granted as a whole. For instance, you could have an ADMIN role that would give the appropriate permissions to an administrator.

A role can be granted to another role. If you give a user the parent role, by default that user will also be granted all of the roles granted to that parent role.

A user can be granted multiple roles. The number of roles that can be enabled at one time is limited by the initialization parameter MAX_ENABLED_ROLES. Multiple roles allow a single user to assume different sets of privileges at different times. If a role has other roles granted to it, using the parent role will have the effect of using all the child roles.

You can set one or more default roles using the ALTER USER statement. Default roles take effect when a user logs in to the Oracle database.

Oracle comes with a number of predefined system roles:

CONNECT: Includes system privileges ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, and CREATE VIEW. According to Oracle Corporation, this role is provided to provide compatability with earlier versions of Oracle and may not be supported in releases past Oracle9i.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Just as monitoring resource usage is an important way to understand performance issues, auditing is a way to track usage in the database and to become aware of potential security issues.

For some time, Oracle has allowed three different types of auditing:

Statement auditing: Audits the statements issued on the database, for specific users or for all users.
Privilege auditing: Audits the use of system privileges for specific users or for all users.
Schema object auditing: Audits a specific set of SQL statements on a particular schema object.

Oracle9i also allows a fourth type of auditing, called fine-grained auditing, which is explained in the final section of this chapter.

For all types of auditing, Oracle writes audit records to a database audit trail or the SYS.FGA_LOG$ table or in binary format to an operating system file. The audit trail records contain different information, depending on the type of auditing and the options set for the auditing.

Whether auditing is enabled for your Oracle database or not, the following actions always generate records for the operating system audit trail:

Instance startup
Instance shutdown
Access by users with administrator privileges

You enable and disable auditing with the AUDIT and NOAUDIT commands.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The security features described in the following sections are most relevant to Oracle system administrators or security administrators. We provide only a brief overview of these features; see the Oracle documentation for complete details.

In addition to the security features described in previous sections are other ways to provide security for data in your Oracle database. Until Oracle8i, the most common approaches to limiting access to data based on the value of the data were through the use of views or stored procedures:

Views: With views, you define a subset of data in a table and then grant access to the view only to a user. Starting with Oracle8i, you can achieve the same security by selection with the use of fine-grained access control, described in the next section.
Stored procedures: You can impose a similar limitation by accessing a table only through a stored procedure or package and then granting access to the stored procedure or package to users. The stored procedure could have its own validation rules within its code.

We'll look at some additional access controls in the following sections.

Fine-grained access control provides the type of context-sensitive security that used to be implemented in application code or views. But because fine-grained access control is enforced by the database, it is consistently applied across all applications.

Fine-grained access control, introduced in Oracle8i, is implemented by having a security policy associated with a table implement security on that table. The security policy

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Oracle database is more than just a server. Clients have to be able to connect to an Oracle instance, and Oracle instances have to be able to communicate with other instances, as well as with application servers, foreign databases, and external procedures.

This chapter provides a quick introduction to Oracle networking and then summarizes the syntax of the configuration files you must define in order to use a network as part of your Oracle environment. These files include TNSNAMES.ORA, SQLNET.ORA, LISTENER.ORA, LDAP.ORA, NAMES.ORA, and CMAN.ORA.

Oracle networking software has been known by a few different names over the years. Prior to Oracle8, the name was SQL*Net; with Oracle8, the name of the software was changed to Net8; and with Oracle9i, the name was changed to the more generic Oracle Net Services. For the remainder of this chapter, we use the term Oracle Net Services to refer to all versions of Oracle networking software, with version-specific differences noted.

Regardless of name, the purpose of the networking software has remained the same. If a client of an Oracle instance needs to connect to an Oracle database—whether that client is a traditional client, an application server, or some other type of server—that client must connect to one end of a network connection as implemented by the Oracle network software. The network software transparently implements the communication to the Oracle instance.

Oracle Net Services supports many different client and server platforms and many different network protocols. The underlying architecture and complexity of the network infrastructure is all handled by Oracle Net Services. However, you have to manage Oracle Net Services. This chapter focuses on the configuration files used to accomplish this management task.

Oracle Net Services provides the implementation of the network transport of data and communications, but more as well. Certain features, such as Shared Server (known as Multi-Threaded Server prior to Oracle9

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Oracle networking software has been known by a few different names over the years. Prior to Oracle8, the name was SQL*Net; with Oracle8, the name of the software was changed to Net8; and with Oracle9i, the name was changed to the more generic Oracle Net Services. For the remainder of this chapter, we use the term Oracle Net Services to refer to all versions of Oracle networking software, with version-specific differences noted.

Regardless of name, the purpose of the networking software has remained the same. If a client of an Oracle instance needs to connect to an Oracle database—whether that client is a traditional client, an application server, or some other type of server—that client must connect to one end of a network connection as implemented by the Oracle network software. The network software transparently implements the communication to the Oracle instance.

Oracle Net Services supports many different client and server platforms and many different network protocols. The underlying architecture and complexity of the network infrastructure is all handled by Oracle Net Services. However, you have to manage Oracle Net Services. This chapter focuses on the configuration files used to accomplish this management task.

Oracle Net Services provides the implementation of the network transport of data and communications, but more as well. Certain features, such as Shared Server (known as Multi-Threaded Server prior to Oracle9i), which can help to improve performance by reducing network resource requirements, failover, or client load balancing, are also implemented with Oracle Net Services.

Oracle Net Services provides the transport of information to and from a server. Figure 5-1 illustrates this connection, with the components of Oracle Net Services and their associated configuration files.

Figure 5-1: Network communication and configuration

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The operation of Oracle Net Services is shaped by the information stored in its configuration files. These files are:

TNSNAMES.ORA

This client file contains text definitions for Oracle service names. It provides all the information needed to translate local service names into network addresses that can be used by Oracle Net Services, as well as information needed for some of the special features supported by Oracle Net Services. You don't need to use this file if you are using an external naming service or host naming or if you are using Oracle Names or LDAP (directory naming).

SQLNET.ORA

This file contains configuration information for Oracle Net Services. It includes information such as:

The list of naming methods to use and the order in which to try them
The default domain
The directory and filename to use for network trace files
The directory and filename to use for network log files

LISTENER.ORA

This server file contails configuration information about the Oracle listener(s) on the server.

LDAP.ORA

This parameter file contains information about the LDAP server that will be used. It is new with Oracle8i.

NAMES.ORA

This parameter file is used only if you are running Oracle Net Services with Oracle Names. It contains configuration details used to control the operation of the Oracle Names server. Oracle is phasing out Oracle Names, and only a small subset of Oracle users currently implement this product, so this book does not include detailed information on

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section describes a variety of utilities used to manage Oracle Net Services. The general goal of these utilities is to make it easier to implement and manage network communication. We provide descriptions for the network management utilities most commonly used for Oracle9i. For command-line utilities, the executable names are shown in the list:

Listener Control utility (lsnrctl): A command-line utility for managing the Oracle listener. You can also start the listener automatically as a service on some operating system platforms, like Windows, and you can embed your lsnrctl commands in a startup script on Unix and Linux systems.
Oracle Names Control utility (namesctl): A command-line utility for managing Oracle Names. For more information on the commands used by the Oracle Names Control utility, see the Oracle documentation.
Oracle Connection Manager Control utility (cmctl): A command-line utility for managing the Oracle Connection Manager. For more information on the commands used by the Oracle Connection Manager utility, see the Oracle documentation.
TNSPing utility (tnsping): A command-line utility used to check the connection between an Oracle Net Services client and server.
Oracle Net Manager: A GUI management tool that allows you to create, discover, and test configurations for the listener, connection, and Oracle Names.
Configuration Assistant

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Oracle data dictionary is a collection of tables and related views that enable you to see the inner workings and structure of the Oracle database. By querying these views, you can obtain information about every object and every user of the database. All of the Oracle monitoring tools look at the information available in the data dictionary and present it in an easy-to-use format.

Traditionally, the data dictionary has consisted of a series of views owned by the SYS user. These views, known as static data dictionary views, present information contained in tables that are updated when Oracle processes a DDL statement. The SYS tables and views, as well as a set of public synonyms for the views, are all created by the catalog.sql script. In addition, the installation of some Oracle features creates tables and views in the SYSTEM schema. In general, tables and views owned by SYSTE

Table of Contents