|Oracle® Database Concepts
11g Release 1 (11.1)
|PDF · Mobi · ePub|
This chapter provides an overview of the Oracle database server. The topics include:
A database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to information management. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost-effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage and servers. With this architecture, each new system can be rapidly provisioned from the pool of components. There is no need to provide extra hardware to support peak workloads, because capacity can be easily added or reallocated from the resource pools as needed.
The database has physical structures and logical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting access to logical storage structures.
The section contains the following topics:
Grid computing is an information technology (IT) architecture that produces more resilient and lower cost enterprise information systems. With grid computing, groups of independent, modular hardware and software components can be connected and rejoined on demand to meet the changing needs of businesses.
The grid style of computing solves some common problems with enterprise IT:
Application silos that lead to underutilized, dedicated hardware resources
Monolithic, unwieldy systems that are expensive to maintain and difficult to change
Fragmented and disintegrated information that cannot be fully exploited by the enterprise as a whole.
Compared with other models of computing, IT systems designed and implemented in the grid style deliver higher quality of service, lower cost, and greater flexibility. Higher quality of service is achieved because there is no single point of failure, there is a robust security infrastructure, and management is centralized and policy-driven. Lower costs derive from increasing the utilization of resources and dramatically reducing management and maintenance costs. Rather than dedicating a stack of software and hardware to a specific task, all resources are pooled and allocated on demand, thus eliminating underutilized capacity and redundant capabilities. Greater flexibility is achieved because grid computing also enables the use of smaller individual hardware components, thus reducing the cost of each individual component and enabling the enterprise to devote resources in accordance with changing needs.
The two most common database architectures are client/server and multitier. As internet computing becomes more prevalent in computing environments, many database management systems are moving to a multitier environment.
This section includes the following topics:
An Oracle database system can easily take advantage of distributed processing by using its client/server architecture. In this architecture, the database system has two parts: a front-end or a client, and a back-end or a server.
The client is a database application that initiates a request for an operation to be performed on the database server. It requests, processes, and presents data managed by the server. The client workstation can be optimized for its job. For example, the client might not need large disk capacity, or it might benefit from graphic capabilities. Often, the client runs on a different computer than the database server. Many clients can simultaneously run against one server.
The server runs Oracle Database software and handles the functions required for concurrent, shared data access. The server receives and processes requests that originate from client applications. The computer that manages the server can be optimized for its duties. For example, the server computer can have large disk capacity and fast processors.
A traditional multitier architecture has the following components:
A client or initiator process that starts an operation
One or more application servers that perform parts of the operation. An application server contains a large part of the application logic, provides access to the data for the client, and performs some query processing, thus removing some of the load from the database server. The application server can serve as an interface between clients and multiple database servers and can provide an additional level of security.
An end server or database server that stores most of the data used in the operation
This architecture enables use of an application server to do the following:
Validate the credentials of a client, such as a Web browser
Connect to an Oracle Database server
Perform the requested operation on behalf of the client
If proxy authentication is being used, then the identity of the client is maintained throughout all tiers of the connection.
Service-oriented architecture (SOA) is a multitier architecture in which application functionality is encapsulated in services. SOA services are usually implemented as Web services. Web services can be accessed with the HTTP protocol and are based on a set of XML-based open standards, such as WSDL and SOAP.
Beginning with Oracle Database 11g, Oracle Database can act as a Web service provider in a traditional multitier or SOA environment.
The following sections explain the physical database structures of an Oracle database, including datafiles, control files, redo log files, archived redo log files, parameter files, alert and trace log files, and backup files.
This section includes the following topics:
Every Oracle database has one or more physical datafiles, which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.
Datafiles have the following characteristics:
One or more datafiles form a logical unit of database storage called a tablespace.
A datafile can be associated with only one tablespace.
Datafiles can be defined to extend automatically when they are full.
Data in a datafile is read, as needed, during normal database operation and stored in the memory cache of Oracle Database. For example, if a user wants to access some data in a table of a database, and if the requested information is not already in the memory cache for the database, then it is read from the appropriate datafiles and stored in memory.
Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and to increase performance, data is pooled in memory and written to the appropriate datafiles all at once, as determined by the background process database writer process (DBWn).
See Also:"Overview of the Oracle Database Instance" for more information about the Oracle Database memory and process structures
Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database, including the following information:
Names and locations of datafiles and redo log files
Timestamp of database creation
Oracle Database can multiplex the control file, that is, simultaneously maintain a number of identical control file copies, to protect against a failure involving the control file.
Every time an instance of an Oracle database is started, its control file identifies the datafiles, tempfiles, and redo log files that must be opened for database operation to proceed. If the physical makeup of the database is altered (for example, if a new datafile or redo log file is created), then the control file is automatically modified by Oracle Database to reflect the change. A control file is also used in database recovery.
Every Oracle Database has a set of two or more online redo log files. These online redo log files, together with archived copies of redo log files, are collectively known as the redo log for the database. A redo log is made up of redo entries (also called redo records), which record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.
Archived redo log files are database-generated offline copies of online redo log files. Oracle Database automatically archives redo log files when the database is in
ARCHIVELOG mode. Oracle recommends that you enable automatic archiving of the online redo log.
Parameter files contain a list of configuration parameters for that instance and database. Both parameter files (pfiles) and server parameter files (spfiles) let you store and manage your initialization parameters persistently in a server-side disk file. A server parameter file has these additional advantages:
The file is concurrently updated when some parameter values are changed in the active instance.
The file is centrally located for access by all instance in a Real Application Services database.
Oracle recommends that you create a server parameter file as a dynamic means of maintaining initialization parameters.
Oracle Database Administrator's Guide for information about creating and changing parameter files
Each server and background process can write to an associated trace file. When an internal error is detected by a process, the process dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle Support Services. Trace file information is also used to tune applications and instances. The alert file, or alert log, is a special trace file. The alert log of a database is a chronological log of messages and errors.
The following features provide automation and assistance in the collection and interpretation of trace and alert file information:
The Automatic Diagnostic Repository (ADR) is a system-managed repository for storing and organizing trace files and other error diagnostic data. ADR provides a comprehensive view of all the critical errors encountered by the database and maintains all relevant data needed for problem diagnosis and eventual resolution. When the same type of incident occurs too frequently, ADR performs flood control to avoid excessive dumping of diagnostic information.
The Incident Packaging Service (IPS) extracts diagnostic and test case data associated with critical errors from the ADR and packages the data for transport to Oracle.
See Also:Oracle Database Administrator's Guide for more information
To restore a file is to replace it with a backup file. Typically, you restore a file when a media failure or user error has damaged or deleted the original file.
User-managed backup and recovery requires you to actually restore backup files before you can perform a trial recovery of the backups.
Server-managed backup and recovery manages the backup process, such as scheduling of backups, as well as the recovery process, such as applying the correct backup file when recovery is needed.
This section discusses logical storage structures: data blocks, extents, segments, and tablespaces. These logical storage structures enable Oracle Database to have fine-grained control of disk space use.
This section includes the following topics:
At the finest level of granularity, Oracle Database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. The standard block size is specified by the
DB_BLOCK_SIZE initialization parameter. In addition, you can specify up to four other block sizes. A database uses and allocates free database space in Oracle Database data blocks.
The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.
Above extents, the level of logical database storage is a segment. A segment is a set of extents allocated for a table, index, rollback segment, or for temporary use by a session, transaction, or SQL parser. In relation to physical database structures, all extents belonging to a segment exist in the same tablespace, but they may be in different data files.
When the extents of a segment are full, Oracle Database dynamically allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.
A database is divided into logical storage units called tablespaces, which group related data blocks, extents, and segments. For example, tablespaces commonly group together all application objects to simplify some administrative operations.
Each database is logically divided into two or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.
Every Oracle database contains a
SYSTEM tablespace and a
SYSAUX tablespace. Oracle Database creates them automatically when the database is created. The system default is to create a smallfile tablespace, which is the traditional type of Oracle tablespace. The
SYSAUX tablespaces are created as smallfile tablespaces.
Oracle Database also lets you create bigfile tablespaces, which are made up of single large file rather than numerous smaller ones. Bigfile tablespaces let Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files. As a result, Oracle Database can scale up to 8 exabytes in size. With Oracle-Managed Files, bigfile tablespaces make datafiles completely transparent for users. In other words, you can perform operations on tablespaces, rather than the underlying datafiles.
A tablespace can be online or offline. A tablespace is generally online, so that users can access the information in the tablespace. However, to simplify administration, sometimes a tablespace is taken offline to make a portion of the database unavailable while allowing normal access to the remainder of the database.
A tablespace can be read only, which means that data in the tablespace cannot be modified. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Oracle Database never updates the files of a read-only tablespace, and therefore the files can reside on read-only media such as CD-ROMs or WORM drives.
A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly refer to the database's data. There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas. Schema objects include structures such as tables, views, and indexes. Some of the most common schema objects are defined in the sections that follow.
This section includes the following topics:
Tables are the basic unit of data storage in an Oracle database. Database tables hold all user-accessible data. Each table has columns and rows. A table that has employee information, for example, can have a column called
employee_number, and each row in that column is an employee number.
Indexes are optional structures associated with tables. You can create indexes to increase the performance of data retrieval. Just as the index in this manual helps you quickly locate specific information, an Oracle database index provides an access path to table data.
When processing a request, Oracle Database can use some or all of the available indexes to locate the requested rows efficiently. Indexes are useful when applications frequently query a table for a range of rows (for example, all employees with a salary greater than 1000) or a specific row (for example, the employee with the highest salary).
You create an index on one or more columns of a table. Thereafter, Oracle Database automatically uses and maintains the index. Changes to table data (such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes.
Views are customized presentations of data in one or more tables or other views. A view can also be considered a stored query. Views do not contain actual data. Rather, they derive their data from the tables on which they are based, referred to as the base tables of the views.
You can query, update, insert into, and delete views as you can with tables, with some restrictions. If the view is updatable, then all operations performed on the view actually affect the base tables of the view.
Views can provide table security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.
Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.
Like indexes, clusters do not affect application design. Whether a table is part of a cluster is transparent to users and to applications. SQL statements access data stored in a clustered table in the same way that they access data stored in a nonclustered table.
A synonym is an alias for any table, view, materialized view, sequence, operator, procedure, function, package, Java class schema object, user-defined object type, or another synonym. A synonym is simply an alias, so it requires no storage other than its definition in the data dictionary.
See Also:Chapter 5, "Schema Objects" for more information on these and other schema objects
Each Oracle database has a data dictionary, which is a set of tables and views that serve as a reference about the database. For example, a data dictionary stores information about both the logical and physical structure of the database. A data dictionary also stores the valid users of an Oracle database, information about integrity constraints defined for tables in the database, and the amount of space allocated for a schema object and how much of that space is in use, among much other information.
A data dictionary is created when a database is created. To accurately reflect the status of the database at all times, the data dictionary is automatically updated by Oracle Database in response to specific actions, such as when the structure of the database is altered. Database users cannot modify the data dictionary. Various database processes rely on the data dictionary to record, verify, and conduct ongoing work. For example, during database operation, Oracle Database reads the data dictionary to verify that schema objects exist and that users have proper access to them.
See Also:Chapter 7, "The Data Dictionary" for more information
An Oracle Database server consists of an Oracle Database and one or more Oracle Database instances. Every time a database is started, a shared memory area called the system global area (SGA) is allocated and Oracle Database background processes are started. The combination of the background processes and the SGA is called an Oracle Database instance.
Some hardware architectures (for example, shared disk systems) enable multiple computers to share access to data, software, or peripheral devices. Oracle Real Application Clusters (Oracle RAC) comprises two or more Oracle Database instances running on multiple clustered computers that communicate with each other by means of an interconnect. Oracle RAC uses Oracle Clusterware to access a shared database that resides on shared disks. Oracle RAC combines the processing power of these multiple interconnected computers to provide system redundancy, near linear scalability, and high availability. Oracle RAC also offers significant advantages for both OLTP and data warehouse systems, and all systems and applications can efficiently exploit clustered environments.
You can scale applications in Oracle RAC environments to meet increasing data processing demands without changing the application code. When you add resources such as nodes or storage, Oracle RAC extends the processing powers of these resources beyond the limits of the individual components.
When users connect to an Oracle Database server, they are connected to an Oracle Database instance. The database instance services those users by allocating other memory areas in addition to the SGA, and starting other processes in addition to the Oracle Database background processes. The following sections describe the various Oracle Database memory areas and processes:
An Oracle database uses memory structures and processes to manage and access the database. All memory structures exist in the main memory of the computers that constitute the database system. A process is a mechanism in an operating system that can run a series of steps. Some operating systems use the terms job or task. Oracle Database server uses three types of processes: Oracle processes—which include server processes and background processes—and user processes. On almost all systems, the Oracle processes and the user processes are on separate computers.
Oracle Database creates a set of background processes for each instance. The background processes consolidate functions that would otherwise be handled by multiple Oracle Database programs running for each user process. They asynchronously perform I/O and monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.
See Also:"Oracle Database Background Processes" for more information on some of the most common background processes
User processes—sometimes called client processes—are created and maintained to run the software code of an application program (such as an OCI or OCCI program) or an Oracle tool (such as Oracle Enterprise Manager). Most environments have separate machines (laptops, desktops, and so forth) for the client processes. User processes also manage communication with the server process through the program interface, which is described in a later section.
Oracle Database creates server processes to handle requests from connected user processes. A server process communicates with the user process and interacts with Oracle Database to carry out requests from the associated user process. For example, if a user queries some data not already in the database buffers of the SGA, then the associated server process reads the proper data blocks from the datafiles into the SGA.
Oracle Database can be configured to vary the number of user processes for each server process. In a dedicated server configuration, a server process handles requests for a single user process. A shared server configuration lets many user processes share a small number of server processes, minimizing the number of server processes and maximizing the use of available system resources.
See Also:Chapter 9, "Process Architecture"
Oracle Database creates and uses memory structures for various purposes. For example, memory stores program code being run, data shared among users, and private data areas for each connected user. Two basic memory structures are associated with an Oracle Database:
The System Global Area (SGA) is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
The Program Global Areas (PGA) are memory regions that contain data and control information for a server or background process. A PGA is nonshared memory created by Oracle Database when a server or background process is started. Access to the PGA is exclusive to the process. Each server process and background process has its own PGA.
See Also:Chapter 8, "Memory Architecture" for more information
This section describes Oracle Net Services, as well as how to start up the database, in the following sections:
Oracle Net Services is the interface between Oracle Database and the network communication protocols that facilitate distributed processing and distributed databases. Communication protocols define the way that data is transmitted and received on a network. Oracle Net Services supports communications on all major network protocols, including TCP/IP, HTTP, FTP, and WebDAV.
Using Oracle Net Services, application developers do not need to be concerned with supporting network communications in a database application. If a new protocol is used, then the database administrator makes some minor changes, and the application requires no modifications and continues to function.
Oracle Net, a component of Oracle Net Services, establishes and maintains a network session from a client application to an Oracle Database server. Once a network session is established, Oracle Net acts as the data courier for both the client application and the database server, exchanging messages between them. Oracle Net can perform these jobs because it is located on each computer in the network.
Oracle Database Net Services Administrator's Guide for more information about network connections
Oracle XML DB Developer's Guide for information about using WebDAV with the database
Start an instance.
Mount the database.
Open the database.
A database administrator can perform these steps using Oracle Enterprise Manager, the SQL*Plus
STARTUP statement, the
srvctl command-line tool, or the Express Edition
START command. When Oracle Database starts an instance, it reads the server parameter file (spfile) or initialization parameter file (pfile) to determine the values of initialization parameters. Then, it allocates an SGA and creates background processes.
The following example describes Oracle Database operations at the most basic level. This illustrates an Oracle Database configuration where the user and associated server process are on separate computers, connected through a network.
An instance has started on the computer running Oracle Database, often called the host or database server.
A computer running an application (a local computer or client workstation) runs an application in a user process. The client application attempts to establish a connection to the server using the proper Oracle Net Services driver.
The server is running the proper Oracle Net Services driver. The server detects the connection request from the application and creates a dedicated server process on behalf of the user process.
The user runs a SQL statement and commits the transaction. For example, the user changes a name in a row of a table.
The server process receives the statement and checks the shared pool (an SGA component) for any shared SQL area that contains a similar SQL statement. If a shared SQL area is found, then the server process checks the user's access privileges to the requested data, and the existing shared SQL area is used to process the statement. If not, then a new shared SQL area is allocated for the statement, so it can be parsed and processed.
The server process retrieves any necessary data values, either from the actual datafile (table) or those stored in the SGA.
The server process modifies data in the system global area. The database writer process (DBWn) writes modified blocks permanently to disk when doing so is efficient. Because the transaction is committed, the log writer process (LGWR) immediately records the transaction in the redo log file.
If the transaction is successful, then the server process sends a message across the network to the application. If it is not successful, then an error message is transmitted.
Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users' transactions and prevents contention between transactions that request the same data.
See Also:Chapter 9, "Process Architecture" for more information background processes
This section contains the following topics:
System changes, such as hardware and software upgrades and patch application, are essential for businesses for compliance and security purposes or to maintain their competitive edge. Oracle Real Application Testing helps you fully assess the effect of system changes on real-world applications in test environments before deploying them in production. Oracle Real Application Testing consists of two features:
Database Replay enables realistic testing of system changes by essentially re-creating the production workload environment on a test system. It does this by capturing a workload on the production system and then replaying it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This makes possible complete assessment of the impact of the change including undesired results, new contention points, and performance regressions. Extensive analysis and reporting is provided to help identify any potential problems, such as new errors encountered and performance divergences.
With Database Replay, businesses can rapidly test changes and adopt new technologies with a high degree of confidence in the overall success of the effort and at significantly lower risk.
Database Replay can be used to assess the impact of the following types of system changes:
Database upgrades, patches, parameter, and schema changes
Configuration changes, such as conversion from a single instance to Oracle Real Application Clusters and Automatic Storage Management
Storage, network, and interconnect changes
Operating system patches, upgrades, and parameter changes and hardware migrations
Changes that affect SQL execution plans can severely impact system performance and availability. As a result, DBAs spend considerable time in identifying and fixing SQL statements that have regressed due to a change.
SQL Performance Analyzer automates the process of assessing the overall effect of a change on the full SQL workload by identifying performance divergence for each statement. A report that shows the net impact on the workload performance due to the change is provided. For regressed SQL statements, appropriate execution plan details, along with recommendations to tune them, is also provided. As a result, DBAs can remedy any negative outcome before their end users are affected and can confirm, with significant time and cost savings, that the system change to the production environment will, in fact, result in net improvement.
You can use the SQL Performance Analyzer to analyze the SQL performance impact of any type of system change. Examples of common system changes include:
Configuration changes to the operating system, hardware, or database
Database initialization parameter changes
Schema changes, such as adding new indexes or materialized views
Gathering optimizer statistics
SQL tuning actions, such as creating SQL profiles
See Also:Oracle Database Performance Tuning Guide to learn how to use the SQL Performance Analyzer
All information management systems have these important requirements:
Data concurrency of a multiuser system must be maximized.
Data must be read and modified in a consistent fashion. The data a user is viewing or changing must not changed (by other users) until the first user is finished with the data.
High performance is required for maximum productivity from the many users of the database system.
Oracle Database contains several software mechanisms that satisfy these requirements. This contains the following sections:
A primary feature of a multiuser database management system is concurrency, which is the simultaneous access of the same data by many users. Without adequate concurrency controls, data could be updated or changed improperly, compromising data integrity.
One way to manage data concurrency is to make each user wait for a turn. The goal of a database management system is to reduce that wait so it is either nonexistent or not noticeable to users. Data manipulation language operations (inserts, updates, and deletes) should proceed with as little interference as possible, and destructive interactions between concurrent transactions must be prevented. A destructive interaction is one that incorrectly updates data or incorrectly alters underlying data structures. Neither performance nor data integrity can be sacrificed.
Oracle Database resolves these issues by using various types of locks and a multiversion consistency model. These features are based on the concept of a transaction.
The transaction is key to the Oracle Database strategy for providing read consistency. This unit of committed (or uncommitted) SQL statements:
Dictates the start point for read-consistent views generated on behalf of readers
Controls when modified data can be seen by other transactions of the database for reading or updating
It is the application designer's responsibility to ensure that transactions fully exploit these concurrency and consistency features.
See Also:Chapter 4, "Transaction Management"
Guarantees that the set of data seen by a statement is consistent with respect to a single point in time and does not change during statement execution (statement-level read consistency)
Ensures that readers of database data do not wait for writers or other readers of the same data
Ensures that writers of database data do not wait for readers of the same data
Ensures that writers only wait for other writers if they attempt to update identical rows in concurrent transactions
In the Oracle Database implementation of read consistency, it is as if each user operates a private copy of the database. This is sometimes called a multiversion consistency model.
To manage the multiversion consistency model, Oracle Database uses current information in the System Global Area and information in the undo records to construct a read-consistent view of a table's data for a query. When an update occurs, the original data values are recorded in the database undo records. As long as this update remains part of an uncommitted transaction, any user that later queries the modified data views the original data values. Only when a transaction is committed are the changes of the transaction made permanent. Queries that are initiated after the transaction is committed see the changes made by the committed transaction.
By default, Oracle Database guarantees statement-level read consistency. The set of data returned by a single query is consistent with respect to a single point in time. However, in some situations, you might also require transaction-level read consistency. This is the ability to run multiple queries within a single transaction, all of which are read-consistent with respect to the same point in time, so that queries in this transaction do not see the effects of intervening committed transactions. If you want to run a number of queries against multiple tables and if you are not doing any updating, you can initiate the transaction with commands that define it as a read-only transaction.
See Also:Oracle Database Concepts for more information on transaction-level read consistency
Oracle Database also caches query results, so that if a query is repeated, the database can return results from the cache instead of reprocessing the query and reading data from storage. The cached results are stored in a dedicated portion of the shared pool. Query retrieval from the query result cache is faster than rerunning the query. The query result cache enables explicit caching of results in database memory. Frequently executed queries especially see performance improvements when using the query result cache.
Oracle Database also uses locks to control concurrent access to data. When updating information, the data server holds that information with a lock until the update is submitted or committed. Until that happens, no one else can make changes to the locked information. This ensures the data integrity of the system.
Oracle Database provides unique nonescalating row-level locking. Unlike other data servers that escalate locks to cover entire groups of rows or even the entire table, Oracle Database always locks only the row of information being updated. Because the database includes the locking information with the actual rows themselves, it can lock an unlimited number of rows so users can work concurrently without unnecessary delays.
Oracle Database locking is performed automatically and requires no user action. Implicit locking occurs for SQL statements as necessary, depending on the action requested.
The Oracle Database lock manager maintains several different types of row locks, depending on what type of operation established the lock. The two general types of locks are exclusive locks and share locks. Only one exclusive lock can be placed on a resource (such as a row or a table); however, many share locks can be placed on a single resource. Both exclusive and share locks always permit queries on the locked resource but prohibit other activity on the resource (such as updates and deletes).
People who administer the operation of an Oracle database system, known as database administrators (DBAs), are responsible for creating Oracle databases, ensuring their smooth operation, and monitoring their use. In addition to the many alerts and advisors Oracle provides, Oracle Database also offers features described in the following sections:
Oracle Database provides a high degree of self-management by automating routine DBA tasks and reducing complexity of space, memory, and resource administration. Oracle Database self-managing features include the following: automatic undo management, automatic server memory management, Oracle-managed files, free space management, and Recovery Manager (RMAN).
Oracle Database automatically schedules periodic maintenance tasks such as statistics collection and space recovery. These tasks run in a set of Oracle Scheduler windows known as maintenance windows. You can control the start time and duration of these maintenance windows, and limit the amount of CPU and I/O resources that they consume.
Oracle Enterprise Manager is a system management tool that provides central management of your database environment. Combining a graphical console, Oracle Management Servers, Oracle Intelligent Agents, common services, and administrative tools, Oracle Enterprise Manager provides a comprehensive systems management platform for managing Oracle products.
From the client interface, the Oracle Enterprise Manager Console, you can perform the following tasks:
Administer the entire Oracle environment, including databases, Oracle Application Server servers, applications, and services
Diagnose, modify, and tune multiple databases
Schedule tasks on multiple systems at varying time intervals
Monitor database conditions throughout the network
Administer multiple network nodes and services from many locations
Share tasks with other administrators
Group related targets together to facilitate administration tasks
Launch integrated Oracle and third-party tools
Customize the display of an Oracle Enterprise Manager administrator
Oracle SQL Developer is a graphical development tool that provides a convenient way to perform these tasks:
Browse, create, edit, and delete (drop) database objects
Edit and debug PL/SQL code
Run SQL statements and scripts
Manipulate and export data
Create and view reports
With SQL Developer, you can connect to any target Oracle database schema using standard Oracle database authentication. Once connected, you can perform operations on objects in the database. You can also connect to schemas for selected third-party (non-Oracle) databases, such as MySQL, Microsoft SQL Server, and Microsoft Access, view metadata and data in these databases, and migrate these databases to Oracle.
SQL*Plus is a basic command-line tool for entering and running ad hoc database statements. It lets you run SQL statements and PL/SQL blocks, and perform many additional tasks as well.
Beginning with Oracle Database 11g, Release 1, Oracle Database can manage the System Global Area (SGA) memory and instance Program Global Area (PGA) memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. In this memory management mode, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.
See Also:Oracle Database 2 Day DBA for more information
Automatic Storage Management automates and simplifies the management of all types of database files. Database files are automatically distributed across all available disks, and database storage is rebalanced automatically whenever the storage configuration changes. Automatic Storage Management also provides redundancy through the mirroring of database files.
Oracle Database has built-in support for the network file system (NFS) and does not depend on OS support for NFS. This improves manageability and diagnosability of network attached storage accessed with NFS.
The Automatic Database Diagnostic Monitor (ADDM) lets you conduct performance analyzes over any time period defined by a pair of Automatic Workload Repository (AWR) snapshots taken on a particular instance. Analysis is performed top down, first identifying symptoms and then refining them to reach the root causes of performance problems. ADDM also documents non-problem areas of the system. For example, wait event classes that are not significantly affecting the performance of the system are identified and removed from the tuning consideration at an early stage, saving time and effort that would be spent on items with little or no impact on overall system performance.
In addition to problem diagnostics, ADDM recommends possible solutions. When appropriate, ADDM recommends multiple solutions for the DBA to choose from. ADDM considers a variety of changes to a system while generating its recommendations, which include hardware changes, database configuration changes, modification of schema objects, modification of applications, and referrals to other advisors.
Oracle Database provides a server utility called the SQL Tuning Advisor. The SQL Tuning Advisor takes one or more SQL statements as input and invokes the Automatic SQL Tuning Advisor to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of an advice or recommendation, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of SQL Profile. Users can choose whether or not to accept the recommendation to complete the tuning of the SQL statements.
See Also:Oracle Database Performance Tuning Guide for more information
The SQL Access Advisor makes schema modification recommendations. It can recommend that you create access structures such as indexes and materialized views to optimize SQL queries. It can also recommend that you partition tables, indexes, or materialized views to improve query performance.
The SQL Access Advisor takes a SQL workload as input. You can select your workload from various sources, including current and recent SQL activity, a SQL repository, or a user-defined workload such as from a development environment. The advisor then recommends changes to improve the performance of the workload as a whole.
See Also:Oracle Database 2 Day + Performance Tuning Guide for more information
A Streams topology is a representation of the databases in a Streams environment, the Streams components configured in these databases, and the flow of messages between these components. The Streams Performance Advisor reports performance measurements for a Streams topology, including throughput and latency measurements. The Streams Performance Advisor also identifies bottlenecks in a Streams topology so that they can be corrected. In addition, the Streams Performance advisor examines the Streams components in a Streams topology and recommends ways to improve their performance.
See Also:Oracle Streams Concepts and Administration for more information
To help simplify management tasks, as well as providing a rich set of functionality for complex scheduling needs, Oracle Database provides a collection of functions and procedures in the
DBMS_SCHEDULER package. Collectively, these functions are called the Scheduler, and they are callable from any PL/SQL program.
The Scheduler lets database administrators and application developers control when and where various tasks take place in the database environment. For example, database administrators can schedule and monitor database maintenance jobs such as backups or nightly data warehousing loads and extracts.
Traditionally, operating systems regulated resource management among various applications, including Oracle databases, that run on a system. The Database Resource Manager controls the distribution of resources among various sessions by controlling the execution schedule inside the database. By controlling which sessions run and for how long, the Database Resource Manager can ensure that resource distribution matches the plan directive and hence, the business objectives.
See Also:Chapter 14, "Manageability" for more information on Database Resource Manager
Beginning with Oracle Database 11g, Oracle Database includes an advanced fault diagnosability infrastructure for preventing, detecting, diagnosing, and resolving problems. The problems that are targeted are critical errors such as those caused by database code bugs, metadata corruption, and customer data corruption. For information on the goals of this infrastructure and the Oracle technologies that achieve these goals, see "Fault Diagnosability Infrastructure".
The possibility of a system or hardware failure exists in every database system. The purpose of a backup and recovery strategy is to protect the database against data loss caused by failures and reconstruct the database after data loss.
RMAN and User-Managed Backup and Recovery Database backups are the cornerstone of any backup and recovery strategy. A backup is a copy of data. This copy can include important parts of the database such as datafiles, the control file, and the server parameter file. Media recovery is the application of redo logs or incremental backups to a restored backup datafile or individual data block. By reapplying the lost changes, recovery rolls the backup forward in time.
When implementing a backup and recovery strategy, you have the following solutions available:
Recovery Manager (RMAN). This tool integrates with sessions running on an Oracle database to perform a range of backup and recovery activities, including maintaining an RMAN repository of historical data about backups. You can access RMAN through the command line or through Enterprise Manager.
User-managed backup and recovery. In this solution, you perform backup and recovery with a mixture of host operating system commands and SQL*Plus recovery commands.
Both of the preceding solutions are supported by Oracle and are fully documented, but RMAN is the preferred solution for database backup and recovery. RMAN provides access to several backup and recovery techniques and features not available with user-managed backup and recovery. The most noteworthy are the following:
Block media recovery
Unused block compression
Whether you use RMAN or user-managed methods, you can supplement physical backups with logical backups of schema objects made with Data Pump Export utility. You can later use Data Pump Import to re-create data after restore and recovery.
See Also:"RMAN and User-Managed Backups" for more information about these backup methods and Oracle Database Utilities for more information about Data Pump
Oracle Flashback Technology Most Oracle flashback features operate at the logical level, enabling you to view and manipulate database objects. The logical-level flashback features of Oracle do not depend on RMAN and are available whether or not RMAN is part of your backup strategy. With the exception of Flashback Drop, the logical flashback features rely on undo data, which are records of the effects of each database update and the values overwritten in the update. Oracle Database includes the following logical flashback features:
Oracle Flashback Query
Oracle Flashback Version Query
Oracle Flashback Transaction Query
Oracle Flashback Transaction
Oracle Flashback Table
Oracle Flashback Drop
Flashback Data Archive
See Also:"Oracle Flashback Technology" for more information about these features
Data Recovery Advisor Oracle Database includes a Data Recovery Advisor tool that automatically diagnoses persistent data failures, presents appropriate repair options, and executes repairs at your request. The Data Recovery Advisor provides a single point of entry for Oracle backup and recovery solutions. You can use Data Recovery Advisor through the Enterprise Manager Database Control or Grid Control console or through the RMAN command-line client.
Computing environments configured to provide nearly full-time availability are known as high availability systems. Such systems typically have redundant hardware and software that makes the system available despite failures. Well-designed high availability systems avoid having single points of failure.
Oracle Database includes a number of products and features that provide high availability in cases of unplanned downtime or planned downtime. These features, which are described in the sections that follow, can be used in various combinations to meet specific high availability needs.
Oracle Real Application Clusters Oracle Real Application Clusters (Oracle RAC) allows Oracle Database to run any packaged or custom application unchanged across a set of clustered servers. This capability provides the highest levels of availability and the most flexible scalability. If a clustered server fails, Oracle Database continues running on the surviving servers. When more processing power is needed, you can add another server without interrupting access to data.
Oracle Data Guard Oracle Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive failures, disasters, errors, and data corruption. Data Guard maintains these standby databases as transactionally consistent copies of the production database. If the production database becomes unavailable due to a planned or an unplanned outage, Data Guard can switch any standby database to the production role, thus greatly reducing the downtime caused by the outage.
Oracle Streams Oracle Streams enables the propagation and management of data, transactions, and events in a data stream, either within a database or from one database to another. Streams provides a set of elements that enables you to control what information is put into a data stream, how the stream is routed from node to node, what happens to events in the stream as they flow into each node, and how the stream terminates.
Oracle Flashback Technology Flashback technology provides a set of features that let you switch between views of the data as it existed at different points in time. Using flashback features you can query past versions of schema objects and historical data. You can also perform change analysis and self-service repair to recover from logical corruption while the database is online. Flashback technology is unique to Oracle Database and supports recovery at all levels including row, transaction, table, tablespace, and database.
Online Table Redefinition Oracle provides a Reorganize Objects wizard in Oracle Enterprise Manager that can automatically generate a script and perform online table reorganization. The entire redefinition process occurs while users have full access to the table.
Automatic Storage Management Automatic Storage Management (ASM) provides a vertically integrated file system and volume manager directly in the Oracle kernel. ASM spreads files across all available storage. To protect against data loss, ASM extends the concept of SAME (stripe and mirror everything) and adds more flexibility in that it can mirror at the database file level rather than the entire disk level. DBAs using ASM create and administer a large-grained object called a disk group. The disk group identifies the set of disks that are managed as a logical unit. Automation of file naming and placement of the underlying database files save DBAs time and ensures adherence to standard best practices.
Recovery Manager is an Oracle Database utility to manage the backup and recovery of the database. RMAN determines the most efficient method of executing the requested backup, restoration, or recovery operation and then submits these operations to the Oracle Database server for processing. RMAN and the server automatically identify modifications to the structure of the database and dynamically adjust the required operation to adapt to the changes.
Flash Recovery Area The flash recovery area is a unified storage location for all recovery-related files and activities in Oracle Database. When this feature is enabled, all RMAN backups, archive logs, control file autobackups, and datafile copies are automatically written to a specified file system or to an Automatic Storage Management disk group. The management of this disk space is handled by RMAN and the database server. The flash recovery area eliminates the bottleneck of writing to tape. Further, if database media recovery is required, then datafile backups are readily available.
See Also:Chapter 17, "High Availability"
This section describes the following business intelligence features:
A data warehouse is a relational database designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.
In addition to a relational database, a data warehouse environment includes an extraction, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.
Extraction, Transformation, and Loading (ETL) You must load your data warehouse regularly so that it can serve its purpose of facilitating business analysis. To perform this operation, data from one or more operational systems must be extracted and copied into the warehouse. The process of extracting data from source systems and bringing it into the data warehouse is commonly called ETL, which stands for extraction, transformation, and loading.
Bitmap Indexes in Data Warehousing The purpose of an index is to provide pointers to the rows in a table that contain a given key value. In a regular index, this is achieved by storing a list of rowids for each key corresponding to the rows with that key value. Oracle Database stores each key value repeatedly with each stored rowid. Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of space because the indexes can be several times larger than the table data.
In a bitmap index, the database stores a bitmap for each key value instead of a list of rowids. Bitmap indexes are typically only a fraction of the size of the indexed data in the table. Data warehousing environments typically have large amounts of data and ad hoc queries, but a low level of concurrent database manipulation language (DML) transactions. For such applications, bitmap indexing provides several advantages:
Reduced response time for large classes of ad hoc queries
Reduced storage requirements compared with other indexing techniques
Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory
Efficient maintenance during parallel DML and loads
In addition, bitmap join indexes improve query performance for typical data warehouse queries—which often include dimension/fact table joins—with about the same space usage as regular bitmap indexes.
A materialized view provides access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views. Query response time is improved because the query accesses the materialized view instead of executing against the base tables. A materialized view can be stored in the same database as its base tables or in a different database.
Materialized views stored in the same database as their base tables can further improve query performance through query rewrite. Query rewrite is a mechanism that automatically rewrites a SQL query to use a materialized view instead of its base tables. With query rewrite, developers need not rewrite applications to take advantage of materialized views. Query rewrite is particularly useful in a data warehouse environment.
Oracle provides comprehensive data compression capabilities to compress all types of data, backups, and network traffic in an application transparent manner. These capabilities include table compression targeted at OLTP workloads, resulting in reduced storage consumption and improved query performance while incurring minimal write performance overhead. Table compression can be used to compress any relational data. To compress unstructured content use SecureFiles compression. Deduplication provides the ability to automatically eliminate redundant copies of SecureFiles data. A new faster compression algorithm is included to speed up RMAN backups. Data Pump exports can now be compressed to reduce disk space requirements. Finally, Data Guard can compress redo data resulting in reduced network traffic and faster gap resolution.
See Also:"Table Compression"
When Oracle Database runs SQL statements in parallel, multiple processes work together simultaneously to run a single SQL statement. By dividing the work necessary to run a statement among multiple processes, Oracle Database can run the statement more quickly than if only a single process ran it. This is called parallel execution or parallel processing. Parallel execution dramatically reduces response time for data-intensive operations on large databases.
Oracle Database has many SQL operations for performing analytic operations in the database. These include ranking, moving averages, cumulative sums, ratio-to-reports, and period-over-period comparisons.
Oracle online analytical processing (OLAP) provides native multidimensional storage and speed-of-thought response times when analyzing data across multiple dimensions. The database provides rich support for analytics such as time series calculations, forecasting, advanced aggregation with additive and nonadditive operators, and allocation operators. These capabilities make the Oracle database a complete analytical platform, capable of supporting the entire spectrum of business intelligence and advanced analytical applications. Oracle OLAP is fully integrated in the database, so that you can use standard SQL administrative, querying, and reporting tools.
With Oracle Data Mining, data never leaves the database — the data, data preparation, model building, and model scoring results all remain in the database. This enables Oracle Database to provide an infrastructure for application developers to integrate data mining seamlessly with database applications. Typical applications of data mining include call centers, ATMs, E-business relational management (ERM), and business planning. Oracle Data mining supports a PL/SQL API, a Java API, SQL functions for model scoring, and a graphical user interface called Oracle Data Miner.
See Also:Chapter 16, "Business Intelligence" for more information about Oracle Data Mining
Partitioning is a critical feature for managing very large databases (VLDB). Growth is the basic challenge that partitioning addresses, and partitioning allows a database to scale for very large datasets while maintaining consistent performance, without unduly increasing administrative or hardware resources. Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces called partitions. No modifications to applications are necessary when accessing a partitioned table using SQL DML statements.
Partitioning can provide tremendous benefit to a wide variety of applications by improving availability, manageability, and performance.
Information Lifecycle Management (ILM) is a set of processes and policies for managing data throughout its useful life. One of the benefits of implementing an ILM solution is to reduce costs, by leveraging appropriate storage tiers, while maintaining all of the data required for business or regulatory purposes. Partitioning is the capability that enables an ILM solution to be implemented within the database.
See Also:Chapter 18, "Very Large Databases (VLDB)" for more information about VLDB topics
Oracle Database includes datatypes to handle all the types of rich content such as XML, text, audio, video, image, medical image, and spatial. These datatypes appear as native types in the database. They can all be queried using SQL. A single SQL statement can include data belonging to any or all of these datatypes.
This section includes the following topics:
Oracle XML DB is a set of Oracle Database technologies related to high-performance XML storage and retrieval. It provides native XML support by encompassing both SQL and XML data models in an interoperable manner. Oracle XML DB includes the following features:
Support for the World Wide Web Consortium (W3C) XML and XML Schema data models and standard access methods for navigating and querying XML. The data models are incorporated into Oracle Database.
The ability to store, query, update, and transform XML data while accessing it using SQL.
The ability to perform XML operations on SQL data.
A simple, lightweight XML repository where you can organize and manage database content, including XML, using a file/folder/URL metaphor.
An infrastructure independent of storage format, content, and programming language for storing and managing XML data. This infrastructure provides new ways of navigating and querying XML content stored in the database. For example, Oracle XML DB Repository facilitates this by managing XML document hierarchies.
Industry-standard access to and update of XML. The standards include the W3C XPath recommendation and the ISO-ANSI SQL/XML standard. FTP, HTTP(S), and WebDAV can be used to move XML content into and out of Oracle Database. Industry-standard APIs provide programmatic access and manipulation of XML content using Java, C, and PL/SQL.
XML-specific memory management and optimizations.
Enterprise-level Oracle Database features for XML content: reliability, availability, scalability, and security.
Oracle XML DB can be used in conjunction with Oracle XML Developer's Kit (XDK) to build applications that run in the middle tier in either Oracle Application Server or Oracle Database.
The LOB datatypes
BFILE enable you to store and manipulate large blocks of unstructured data (such as text, graphic images, video clips, and sound waveforms) in binary or character format. They provide efficient, random, piece-wise access to the data.
See Also:Oracle Database SecureFiles and Large Objects Developer's Guide for more information about SecureFiles LOBs
SecureFiles is a new feature in Oracle Database 11g that offers the best solution for storing file content, such as images, audio, video, PDFs, and spreadsheets. Traditionally, relational data is stored in a database, while unstructured content—both semi-structured and unstructured—is stored as files in file systems. SecureFiles is a major paradigm shift in the choice of files storage. SecureFiles is specifically engineered to deliver high performance for file data comparable to that of traditional file systems, while retaining the advantages of Oracle Database. SecureFiles offers the best database and file system architecture attributes for storing unstructured content.
Deduplication: Oracle Database automatically detects multiple, identical SecureFiles data and stores only one copy, thereby saving storage space. In addition to storing only one copy, SecureFiles maintains references to other duplicates. Deduplication is completely transparent to applications and, in addition to simplifying storage management, it also results in significantly better performance, especially for copy operations. Duplicate detection happens within a LOB segment. The
lob_storage_clause allows for specifying deduplication at a partition level so that duplicate detection does not span across partitions or subpartitions for partitioned SecureFiles columns.
See Also:Oracle Database SecureFiles and Large Objects Developer's Guide for more information about deduplication
SecureFiles deduplication is part of the Advanced Compression option.
Compression: SecureFiles data is compressed using industry standard compression algorithms. Compression not only results in significant savings in storage but also improved performance by reducing I/O, buffer cache requirements, redo generation, and encryption overhead. If the compression does not yield any savings or if the data is already compressed, SecureFiles automatically turns off compression for such columns. Compression is performed on the server side and allows for random reads and writes to SecureFiles data. SecureFiles provides for varying degrees of compression: MEDIUM (default) and HIGH, which represent a trade-off between storage savings and latency.
See Also:Oracle Database SecureFiles and Large Objects Developer's Guide for more information about compression
SecureFiles compression is part of the Advanced Compression option.
Encryption: In Oracle Database 11g, Oracle has extended the encryption capability to SecureFiles and uses the Transparent Data Encryption (TDE) syntax. Oracle Database supports automatic key management for all SecureFiles columns within a table and transparently encrypts and decrypts data, backups, and redo log files. Applications require no changes and can take advantage of Oracle Database 11g SecureFiles using TDE semantics. SecureFiles supports the following encryption algorithms:
3DES168: Triple Data Encryption Standard with a 168-bit key size
AES128: Advanced Encryption Standard with a 128 bit key size
AES192: Advanced Encryption Standard with a 192-bit key size (default)
AES256: Advanced Encryption Standard with a 256-bit key size
See Also:Oracle Database SecureFiles and Large Objects Developer's Guide for more information about encryption
SecureFiles encryption is part of the Advanced Security option.
File System-like Logging: Modern file systems have the ability to keep a running log of the file system metadata. Putting this metadata into a running log (called a journal) that is flushed in a lazy fashion increases performance and removes the need for file system checking operations like
fsck. SecureFiles' file system-like logging provides this same high performance journaling. File system-like logging also allows for soft corruptions, so that if an error is found on a block, SecureFiles returns a block with the LOB fill character. This allows the application to detect the error by seeing known invalid data and to recover either through deletion of the LOB (something that is not possible with the original implementation of LOBs) or by other means.
In addition to the aforementioned advanced file system features, SecureFiles can take advantage of several advanced Oracle Database capabilities, including:
Transactions, read consistency, and flashback
100% backward compatibility with LOB interfaces
Readable standby, consistent backup, and point-in-time recovery
Fine-grained auditing and label security
XML indexing, XML queries, and XPath
Oracle Real Application Clusters
Automatic Storage Management
Partitioning and ILM
Search across metadata and file content
High Performance SecureFiles is designed from the ground up for high performance and scalability. SecureFiles delivers comparable file system-like performance for basic read and write operations. The optimized algorithms with SecureFiles make it up to 10 times faster than LOBs. The scalability associated with SecureFiles goes far beyond what is offered in file systems. Organizations can scale-up using large SMP systems, or scale-out using Oracle Real Application Clusters to hundreds of computers while still preserving a single system image. Scaling of CPUs and disks can be done independently and transparently. With Oracle Database 11g, organizations can store all types of content and scale to store petabytes or exabytes of data.
Oracle Text indexes any document or textual content to add fast, accurate retrieval of information. Oracle Text lets you combine text searches with regular database searches in a single SQL statement. The ability to find documents based on their textual content, metadata, or attributes, makes the Oracle Database the single point of integration for all data management.
The Oracle Text SQL API makes it simple and intuitive for application developers and DBAs to create and maintain Text indexes and run Text searches.
Oracle Ultra Search lets you index and search Web sites, database tables, files, mailing lists, Oracle Application Server Portals, and user-defined data sources. This search capability lets you use Oracle Ultra Search to build different kinds of search applications.
Oracle Multimedia provides an array of services to simplify the development of applications that include images, audio, and video. Oracle Multimedia objects are accessed as columns in tables, like other more typical relational data. Multimedia content can be stored and managed internally in the database, or externally by storing references to the content in the database. Java and PL/SQL APIs provide metadata extraction, image format conversion, and thumbnail image generation to greatly reduce application development and maintenance costs. Excellent integration with application development tools such as Oracle JDeveloper, Application Express, and Oracle Application Server Portal enable application developers to create and maintain media-rich applications with ease. In addition, Oracle Multimedia provides similar support for Digital Imaging and Communications in Medicine (DICOM) content such as single-frame and multiframe images, waveforms, slices of 3-D volumes, video segments, and structured reports.
See Also:Chapter 19, "Content Management" for more information about Oracle Multimedia
Oracle Database includes built-in spatial features that let you store, index, and manage location content—assets, buildings, roads, land parcels, sales regions, and so on—and query location relationships using the power of the database. The Oracle Spatial option adds advanced spatial features such as linear reference support and coordinate systems.
See Also:Chapter 19, "Content Management" for more information about Oracle Spatial
Oracle Database includes security features that control how a database is accessed and used. Security mechanisms are needed for several purposes:
To prevent unauthorized database access
To prevent unauthorized access to schema objects
To audit user actions
Associated with each database user is a schema by the same name. By default, each database user creates and has access to all objects in the corresponding schema.
Database security can be classified into two categories: system security and data security.
System security lets you control access to and use of the database at the system level. System security mechanisms check whether a user is authorized to connect to the database, whether database auditing is active, and which system operations a user can perform. For example, system security includes:
Valid user name/password combinations
The amount of disk space available to a user's schema objects
The resource limits for a user
Which users have access to a specific schema object and the specific types of actions allowed for each user on the schema object (for example, user
SCOTT can issue
INSERT statements but not
DELETE statements using the
The actions, if any, that are audited for each schema object
Data encryption to prevent unauthorized users from bypassing Oracle Database and accessing data
Oracle Database provides discretionary access control, which is a means of restricting access to information based on privileges. The appropriate privilege must be assigned to a user in order for that user to access a schema object. Appropriately privileged users can grant other users privileges at their discretion.
Oracle Database manages database security using several different facilities:
Authentication to validate the identity of the entities using your networks, databases, and applications
Authorization processes to limit access and actions, limits that are linked to user's identities and roles
Access restrictions on objects such as tables or rows
See Also:Chapter 20, "Database Security" for more information on security mechanisms
Data must adhere to certain business rules, as determined by the database administrator or application developer. For example, assume that a business rule says that no row in the
inventory table can contain a numeric value greater than nine in the
sale_discount column. If an
UPDATE statement attempts to violate this integrity rule, Oracle Database must undo the invalid statement and return an error to the application. Oracle Database provides integrity constraints and database triggers to manage data integrity rules.
Note:Database triggers let you define and enforce integrity rules, but a database trigger is not the same as an integrity constraint. Among other things, a database trigger does not check data already loaded into a table. Therefore, Oracle strongly recommends that you use database triggers only when the integrity rule cannot be enforced by integrity constraints.
This section includes the following topics:
An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about table data that is always true and that follows these rules:
If an integrity constraint is created for a table and some existing table data does not satisfy the constraint, then the constraint cannot be enforced.
After a constraint is defined, if any of the results of a DML statement violate the integrity constraint, then the statement is rolled back, and an error is returned.
Integrity constraints are stored as part of the table's definition in the data dictionary, so that all database applications adhere to the same set of rules. When a rule changes, you define it only once at the database level and not once for each application. A key is the column or set of columns included in the definition of certain types of integrity constraints. Keys describe the relationships between the different tables and columns of a relational database. Individual values in a key are called key values.
A not null constraint disallows nulls (empty entries) in a table's column.
A unique constraint disallows duplicate values in a column or set of columns. The unique key is the column or set of columns included in the definition of a unique constraint.
A primary key constraint disallows duplicate values and nulls in a column or set of columns. The primary key is the column or set of columns included in the definition of a table's primary key constraint. The primary key values uniquely identify the rows in a table. You can define only one primary key for each table.
A foreign key constraint—sometimes called a referential integrity constraint—requires each value in a column or set of columns to match a value in another table's unique key or primary key. Foreign key constraints also define referential integrity actions that dictate what Oracle Database should do with dependent data if the data it references is altered. The foreign key is the column or set of columns included in the definition of the foreign key constraint. The referenced key is the unique key or primary key of the same or a different table referenced by a foreign key.
A check constraint disallows values that do not satisfy the logical expression of the constraint.
See Also:Chapter 21, "Data Integrity" for more information about integrity constraints
Triggers are procedures written in PL/SQL, Java, or C that run (fire) implicitly whenever a table or view is modified or when some user actions or database system actions occur.
Triggers supplement the standard capabilities of Oracle Database to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours.
See Also:Chapter 22, "Triggers" for more information about triggers
A distributed environment is a network of disparate systems that seamlessly communicate with each other. Each system in the distributed environment is called a node. The system to which a user is directly connected is called the local system. Any additional systems accessed by this user are called remote systems. A distributed environment lets applications access and exchange data from the local and remote systems. All the data can be simultaneously accessed and modified.
This section includes the following topics:
A homogeneous distributed database system is a network of two or more Oracle databases that reside on one or more computers. Distributed SQL enables applications and users to simultaneously access or modify the data in several databases as easily as they access or modify a single database.
A distributed Oracle database system can appear as though it is a single Oracle database. Companies can use this distributed SQL feature to make all its Oracle databases look like one and thus reduce some of the complexity of the distributed system.
Oracle Database uses database links to enable users on one database to access objects in a remote database. A local user can access a link to a remote database without having to be a user on the remote database.
Location transparency occurs when the physical location of data is transparent to applications and users. For example, a view that joins table data from several databases provides location transparency because the user of the view does not need to know from where the data originates.
Oracle Database provides query, update, and transaction transparency. For example, standard SQL statements like
DELETE manipulate data just as they do in a nondistributed database environment. Applications can control transactions using the standard SQL statements
ROLLBACK. Oracle Database ensures the integrity of data in a distributed transaction using the two-phase commit mechanism, whereby all nodes in a distributed system are instructed to commit the transaction. If this is not possible, then all nodes roll back the transaction.
See Also:Oracle Database Administrator's Guide for more information on the two-phase commit mechanism
Distributed query optimization uses cost-based optimization to find or generate SQL expressions that extract only the necessary data from remote tables, process that data at a remote site or sometimes at the local site, and send the results to the local site for final processing. This operation reduces the amount of required data transfer when compared to the time it takes to transfer all the table data to the local site for processing.
Oracle Streams enables the propagation and management of data, transactions, and events in a data stream either within a database or from one database to another. The stream conveys published information to subscribed destinations.
Oracle Streams lets users control what information is put into a stream, how the stream flows or is routed from node to node, what happens to events in the stream as they flow into each node, and how the stream terminates. By specifying the configuration of the elements acting on the stream, a user can address specific requirements, such as message queuing or data replication.
Oracle Streams implicitly and explicitly captures events and places them in the staging area. Database events, such as DML and DDL operations, are implicitly captured by mining the redo log files. Sophisticated subscription rules can determine what events should be captured.
The staging area is a queue that stores and manages captured events. Changes to database tables are formatted as logical change records (LCRs), and stored in a staging area until subscribers consume them. LCR staging provides a secure holding area and supports auditing and tracking of LCR data.
Messages in a staging area are consumed by the apply engine, where changes are applied to a database or consumed by an application. A flexible apply engine lets you use a standard or custom apply function. Support for explicit dequeue lets application developers use Oracle Streams to reliably exchange messages. They can also notify applications of changes to data.
Oracle Streams Advanced Queuing is built on the flexible Oracle Streams infrastructure. It provides a unified framework for processing events. Events generated in applications, in workflow, or implicitly captured from redo logs or database triggers can be captured in a queue. These events can be consumed in a variety of ways. They can be automatically applied with a user-defined function or database table operation, can be explicitly dequeued, or a notification can be sent to the consuming application. These events can be transformed at any stage. If the consuming application is on a different database, then the events are automatically propagated to the appropriate database. Operations on these events can be automatically audited, and the history can be retained for a user-specified duration.
Replication is the maintenance of database objects in two or more databases. Oracle Streams provides powerful replication features that can be used to synchronize multiple copies of distributed objects.
Oracle Streams automatically determines what information is relevant and shares that information with those who need it. This active sharing of information includes capturing and managing events in the database, including data changed with DML operations, and propagating those events to other databases and applications. Data changes can be applied directly to the replica database, or can call a user-defined procedure to perform alternative work at the destination database, for example, populate a staging table used to load a data warehouse.
Oracle Streams is an open information sharing solution, supporting heterogeneous replication between Oracle and non-Oracle systems. Using a transparent gateway, DML changes initiated at Oracle databases can be applied on non-Oracle platforms.
Oracle Streams is fully interoperational with materialized views, which can maintain updatable or read-only, point-in-time copies of data. They can contain a full copy of a table or a defined subset of the rows in the master table that satisfy a value-based selection criterion. Materialized views can be multitier, where one materialized view is a subset of another materialized view. Materialized views are periodically updated, or refreshed, from their associated master tables through transactionally consistent batch updates.
Oracle Database Gateways and Generic Connectivity extend distributed Oracle database features to non-Oracle systems. Generic Connectivity is a generic solution. Oracle Database Gateways are tailored solutions, specifically coded for a particular non-Oracle system. Oracle Database can work with non-Oracle data sources, non-Oracle message queuing systems, and non-SQL applications, ensuring interoperability with other vendors' products and technologies.
Oracle Database Gateways and Generic Connectivity can be used for synchronous access, using distributed SQL, and for asynchronous access, using Oracle Streams. Introducing a Transparent Gateway into an Oracle Streams environment enables replication of data from an Oracle database to a non-Oracle database.
Oracle Database Gateways and Generic Connectivity translate third-party SQL dialects, data dictionaries, and datatypes into Oracle Database formats, thus making the non-Oracle data store appear as a remote Oracle database. These features enable companies to seamlessly integrate the different systems and provide a consolidated view of the company as a whole.
See Also:Chapter 23, "Information Integration"
SQL and PL/SQL form the core of the Oracle Database application development stack:
Most enterprise back-ends run SQL
Web applications accessing databases do so using SQL (wrapped by Java classes as JDBC)
Enterprise Application Integration applications generate XML from SQL queries
Content-repositories are built on top of SQL tables
SQL and PL/SQL provide a simple, widely understood, unified data model. They are used standalone in many applications, but are also invoked directly from Java (JDBC), Oracle Call Interface (OCI), Oracle C++ Call Interface (OCCI), or XSU (XML SQL Utility). Stored packages, procedures, and triggers can all be written in PL/SQL or in Java.
This section includes the following topics:
Structured query language (SQL—pronounced "sequel") is the programming language that defines and manipulates the database. SQL databases are relational databases, which means that data is stored in a set of simple relations.
All operations on the information in an Oracle database are performed using SQL statements. A SQL statement is a string of SQL text. A statement must be the equivalent of a complete SQL sentence, as in:
SELECT last_name, department_id FROM employees;
Note:The end of a SQL statement is indicated differently in different programming environments. This documentation set uses the default SQL*Plus character, the semicolon (;).
Only a complete SQL statement can run successfully. A sentence fragment like the following one generates an error indicating that more text is required:
A SQL statement can be thought of as a very simple but powerful computer program or instruction. SQL statements are divided into the following categories:
Data definition language (DDL) statements create, alter, maintain, and drop schema objects. DDL statements also include statements that permit a user to grant other users the privileges to access the database and specific objects within the database.
Data manipulation language (DML) statements manipulate data. Querying, inserting, updating, and deleting rows of a table are all DML operations. The most common SQL statement is the
SELECT statement, which retrieves data from the database. Locking a table or view and examining the execution plan of a SQL statement are also DML operations.
Session control statements let a user control the properties of the current session, including enabling and disabling roles and changing language settings. The two session control statements are
System control statements changes the properties of the Oracle database instance.
SYSTEM is the only system control statement. It lets you change settings, such as the minimum number of shared servers. It also lets you terminate a session and perform other systemwide tasks.
Embedded SQL statements incorporate DDL, DML, and transaction control statements in a procedural language program, such as those used with the Oracle precompilers. Examples include
See Also:Chapter 24, "SQL" for more information about SQL
PL/SQL is the Oracle procedural language extension to SQL. PL/SQL combines the ease and flexibility of SQL with the procedural functionality of a structured programming language, including such routines as
When designing a database application, consider the following advantages of using stored PL/SQL:
PL/SQL code can be stored in a database. Network traffic between applications and the database is reduced, so application and system performance increases. Even when PL/SQL is not stored in the database, applications can send to the database blocks of PL/SQL rather than individual SQL statements, thereby reducing network traffic.
Native compilation of PL/SQL code is very easy and offers significant performance advantages.
Data access can be controlled by stored PL/SQL code. PL/SQL users can access data only as intended by application developers, unless another access route is granted.
Oracle supports PL/SQL Server Pages, so your application logic can be invoked directly from your Web pages.
The following sections describe some of the PL/SQL program units that can be defined and stored centrally in a database.
Procedures and functions are sets of SQL and PL/SQL statements grouped together as a unit to solve a specific problem or to perform a set of related tasks. They are created and stored in compiled form in the database and can be run by a user or a database application. Procedures and functions are identical, except that functions always return a single value to the user. Procedures do not return values.
Packages encapsulate and store related procedures, functions, variables, and other constructs together as a unit in the database. They offer increased functionality. For example, global package variables can be declared and used by any procedure in the package. Packages also improve performance, because all objects of the package are parsed, compiled, and loaded into memory once.
See Also:Chapter 24, "SQL" for more information about PL/SQL
Java is an object-oriented programming language efficient for application-level programs. Oracle Database provides all types of JDBC drivers and enhances database access from Java applications. Java Stored Procedures are portable and secure in terms of access control, and they let non-Java and legacy applications transparently invoke Java. In addition, native compilation of Java code is very easy and offers significant performance advantages.
See Also:"Overview of Java" for more information about Java
Oracle Database developers have a choice of languages for developing applications—C, C++, Java, COBOL, PL/SQL, PHP, and Visual Basic. The entire functionality of the database is available in all of the languages. All language-specific standards are supported. Developers can choose the languages in which they are most proficient or one that is most suitable for a specific task. For example, an application might use Java on the server side to create dynamic Web pages, PL/SQL to implement stored procedures in the database, and C++ to implement computationally intensive logic in the middle tier.
See Also:The following books describe the various Oracle APIs:
Pro*C/C++ Programmer's Guide
Oracle Call Interface Programmer's Guide
Pro*COBOL Programmer's Guide
Oracle Database PL/SQL Language Reference
Oracle Database Data Cartridge Java API Reference
Also refer to Chapter 25, "Supported Application Development Languages" for more information.
Oracle provides different application development environments for different application developer needs.
Oracle Application Express is a hosted declarative development environment for developing and deploying database-centric Web applications. Using only a Web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. The Application Express engine lives completely within your Oracle database and is written in PL/SQL. It renders applications in real time from data stored in database tables. When you create or extend an application, Oracle Application Express creates or modifies metadata stored in database tables. When the application is run, the Application Express engine then reads the metadata and displays the application. Oracle Application Express also transparently manages session state in the database. Application developers can get and set session state using simple substitutions as well as standard SQL bind variable syntax. Application Express is a tool to build Web-based applications and the application development environment is also conveniently Web based itself.
See Also:Oracle Database Express Edition 2 Day Developer Guide for more information
PHP—a self-referencing acronym for PHP - Hypertext Preprocessor—is a popular scripting language commonly embedded with HTML to create dynamic web pages. PHP is perfect for rapidly developing Web 2.0 applications. PHP's oci8 extension is a stable, high-performance PHP database driver that is fully integrated with Oracle Database. Using PHP with Oracle Database, you can query and update data, execute stored procedures and functions, load images, and easily build scalable, global applications.
See Also:Oracle Database 2 Day + PHP Developer's Guide for more information
In the Microsoft Windows environment, Oracle provides the following development environments:
The Oracle Data Provider for .NET (ODP.NET) features optimized data access to the Oracle database from a .NET environment. ODP.NET allows developers to take advantage of advanced Oracle database functionality, including Oracle Real Application Clusters, XML DB, and advanced security. The data provider can be used from any .NET language, including C# and Visual Basic .NET.
Each column value and constant in a SQL statement has a datatype, which is associated with a specific storage format, constraints, and a valid range of values. When you create a table, you must specify a datatype for each of its columns.
Scalar datatypes, such as character, numeric, and datetime datatypes
Collection types such as variable-length arrays (varrays) and nested tables for more fine-graine3d organization of and access to data in the database
ANSI-supported types, which facilitates working with data from non-Oracle databases
Supplied datatypes, which are SQL-based interfaces for defining new types when the built-in or ANSI-supported types are not sufficient. The behavior for these types can be implemented in C/C++, Java, or PL/ SQL.
In addition, user-defined object types can be created from any built-in datatypes or any previously created object types, object references, and collection types. Metadata for user-defined types is stored in a schema available to SQL, PL/SQL, Java, and other published interfaces.
A user-defined object type differs from native SQL datatypes in that it specifies both the underlying persistent data (attributes) and the related behaviors (methods). Object types are abstractions of the real-world entities and are sometimes called abstract datatypes (ADTs).
Oracle Database SQL Language Reference for a complete listing of the Oracle built-in and supplied datatypes
Oracle databases can be deployed anywhere in the world, and a single instance of Oracle Database can be accessed by users across the globe. Information is presented to each user in the language and format specific to his or her location.
The Globalization Development Kit (GDK) simplifies the development process and reduces the cost of developing internet applications for a multilingual market. GDK lets a single program work with text in any language from anywhere in the world.