Copyright: Copyright (c) 1994, Oracle Corporation.


Entry Topic
Oracle7 Server Documentation Addendum, Release 7.1, First Edition

Oracle7™ Server Documentation Addendum

PREFACE

CHAPTER 1. INTRODUCTION

CHAPTER 2. SQL ADDITIONS

CHAPTER 3. READ-ONLY TABLESPACES

CHAPTER 4. PARALLEL RECOVERY

CHAPTER 5. DATABASE CONNECTION SECURITY

CHAPTER 6. PARALLEL QUERY OPTION

CHAPTER 7. DYNAMIC SQL

CHAPTER 8. SNAPSHOT REFRESH

CHAPTER 9. MISCELLANEOUS ADDITIONS

CHAPTER 10. TRUSTED ORACLE CHANGES

APPENDIX A. REFERENCE

APPENDIX B. CHANGES TO RELEASE 7.0

APPENDIX C. OPERATING SYSTEM- SPECIFIC INFORMATION


This help file created via:

Oracle Book to Microsoft Help Version 1.0p

© 1994 Oracle Corporation Inc.

Authored by KM .


Oracle7ä Server Documentation Addendum
Oracle7™ Server Documentation Addendum

Release 7.1FIRST EDITION

Oracle7 Server Documentation Addendum,  Release 7.1

Part No. A12042-3

Copyright © Oracle Corporation 1993, 1994

Contributing Authors:  John Frazzini, Ruth Hiltner, Maria Pratt

Contributors:  Richard Allen, John Bellemore, Yakoub Billawala, Bill Bridge, Greg Doherty, Gary Hallmark, Mike Hartstein, Ken Jacobs, Hakan Jacobsson, Amit Jasuja, Bob Jenkins, Derry Kabcenell, Dan Leary, Richard Lim, Bill Maimone, Andy Mendelsohn,  Valarie Moore, Cetin Ozbutin, Gregory Pongracz, Tom Portfolio, Jay Rossiter, Gordon Smith, Linda Vetter

This software/documentation contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited.

If this software/documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:

Restricted Rights Legend

Use, duplication, or disclosure by the Government is subject to restrictions

as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in

Technical Data and Computer Software (October 1988).

Oracle Corporation, 500 Oracle Parkway, Redwood City, CA  94065.

this software/documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights," as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987).

The information in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error-free.

Oracle and SQL*Net are registered trademarks of Oracle Corporation.

Oracle7, Trusted Oracle7, Oracle Parallel Server, Oracle Server Manager, Oracle Office, Oracle Names, Oracle Network Manager, and PL/SQL are trademarks of Oracle Corporation.

All other product or company names mentioned are used for identification purposes only, and may be trademarks of their respective owners.


PREFACE

This addendum supplements the following manuals:

•	ORACLE7 Server Administrator's Guide, part number 6694-70-1292

•	ORACLE7 Server Application Developer's Guide, part number 6695-70-1292

•	ORACLE7 Server Concepts Manual, part number 6693-70-1292

•	ORACLE7 Server SQL Language Reference Manual, part number 778-70-1292

•	ORACLE7 Parallel Server Administrator's Guide, part number 5990-70-1292

•	ORACLE7 Server Migration Guide, part number 6617-70-1292

•	Trusted ORACLE7 Server Administrator's Guide, part number 6610-70-0193

•	Oracle7 Parallel Server Administrator's Guide, part number 5590-70-1292

This addendum describes the improvements and new features available with Oracle7 release 7.1.

 ________________________________________

Audience

This addendum was written for those people responsible for administering, or designing applications for, release 7.1 of the Oracle7 Server or Trusted Oracle7 Server. This addendum assumes that you are already familiar with Oracle7 release 7.0.

Additional Information:  This addendum does not contain any installation instructions or port-specific information. See your platform specific Oracle documentation for this information.

 ________________________________________

How This Addendum Is Organized

This addendum is divided into the following chapters:

Chapter 1: Introduction This chapter provides a brief overview of the enhancements in release 7.1 of Oracle7, as well as compatibility issues with earlier versions.

Chapter 2: SQL Additions This chapter describes the additions to SQL in release 7.1.

Chapter 3: Read-Only Tablespaces This chapter describes the new READ ONLY option of the ALTER TABLESPACE command.

Chapter 4: Parallel Recovery This chapter describes how to use multiple concurrent processes to decrease recovery time.

Chapter 5: Connecting to a Remote Database This chapter describes the improved security available in release 7.1 when you are connecting to a remote database.

Chapter 6: Parallel Query Option This chapter describes the new parallel query option available in  release 7.1.

Chapter 7: Dynamic SQL This chapter describes the new DBMS_SQL package, which allows you to use dynamic SQL in stored procedures and PL/SQL anonymous blocks.

Chapter 8: Snapshot Refresh This chapter describes how you can use snapshot refresh groups to consistently refresh a collection of snapshots to a transaction consistent point in time.

Chapter 9: Miscellaneous Additions This chapter describes several miscellaneous changes and features, such as the SQL*Net Oracle Names feature, the use of multiple triggers of the same type per table, and table caching.

Chapter 10: Trusted Oracle Changes This chapter describes the additional enhancements in release 7.1 of Trusted Oracle7.

Appendix A: Reference This appendix describes the changes to the initialization parameters, dynamic performance tables, and data dictionary views.

Appendix B: Changes to Release 7.0 This appendix describes features introduced in release 7.0 of the Oracle Server that are not described in the Oracle7 Server documentation set.

Appendix C: Operating System Specific Information This appendix provides a summary of the operating system specific information referenced within this addendum.

 ________________________________________

Notation Used in This Addendum

The notation used in this addendum is outlined in the following list.

Feature	Explanation

------------------------------------------------------------------------

monospace text	A monospaced font is used for code samples.

[ ]	Square brackets enclose optional items. Do

not type the square brackets.

|	A vertical bar represents an "or" option

among several options. You must enter one,

and only one, of the options separated by ver-

tical bars. Do not enter the vertical bars them-

selves.

UPPERCASE	UPPERCASE characters within the text repre-

sent command names.

 ________________________________________

Related Publications

In addition to this addendum, the other manuals in the Oracle7 Server documentation set, and the Oracle operating system specific documentation for your platform, you may want to refer to the following documents published by Oracle Corporation:

•	Understanding SQL*Net Version 2.1, part number A16029

•	Oracle Network Manager Administrator's Guide Version 1.0, part number A16264

•	Oracle Names Administrator's Guide Version 1.0, part number A16266

•	Oracle Office Server Release Notes, Release 2.0.12, part number A10966-3.

•	PL/SQL Release 2.1 and Oracle Precompilers Release 1.6 Addendum, part number A11828-3

 ________________________________________

Your Comments Are Welcome

The Oracle Corporation technical staff values your comments. As we write and revise, your opinions are the most important input we receive. Please use the Reader's Comment Form at the back of this addendum to tell us what you like and dislike about this Oracle publication. If the form has been used or you want to contact us, please call us at (415)506-7000 or use the following address or FAX number:

Server Documentation Manager

Corporation

Oracle Parkway

City, CA  94065

: (415)506-7200


CHAPTER 1. INTRODUCTION

This chapter briefly introduces the new features in release 7.1 of the Oracle7 Server and the associated compatibility issues. The topics covered include the following:

•	New features available with release 7.1 of the Oracle Server

•	Upgrading and downgrading issues

•	Enabling and disabling 7.1 features

 ________________________________________

Using Release 7.1

Release 7.1 of the Oracle7 Server is primarily a maintenance release, which contains bug fixes and performance enhancements. Release 7.1 is fully compatible with release 7.0. It is not necessary for you to migrate your data or change your applications in order to begin using release 7.1.

Release 7.1 also provides several important new features, which are outlined on page 1 - 3. You can choose to take advantage of these new features by setting the COMPATIBLE parameter as described in "Enabling 7.1 Features" on page 1 - 5. If you leave this parameter set to its default value, 7.0.12, and do not take advantage of any 7.1 new features, you can return to any 7.0 release of Oracle greater than 7.0.12, at any time. If you enable the 7.1 features, you must disable them as described in "Disabling 7.1 Features" on page 1 - 5 before downgrading. Even if you enable the 7.1 features however, it is not necessary to migrate or export your data in order to return to 7.0.

 ________________________________________

Upgrading/Downgrading

Upgrading to release 7.1 is similar to upgrading to a new patch release, such as 7.0.16. The information on upgrading to release 7.1 of the Oracle Server or Trusted Oracle Server from earlier releases is contained in the README.DOC file shipped with release 7.1.

Additional Information:  You should also refer to your platform-specific README.DOC file in case there are any platform-specific issues.

After upgrading to release 7.1 it is possible to downgrade to an earlier version of the Oracle Server or Trusted Oracle Server, following a NORMAL shutdown,  by following the downgrading instructions contained in the README.DOC file.

 ________________________________________

New Features

Although primarily a maintenance release, release 7.1 of the Oracle Server does contain several important functional enhancements. These enhancements include the following:

•	Server Manager

Oracle has a new database administration tool with a graphical user interface. This tool is documented in the Oracle Server Manager User's Guide, part number A14808-1. Release notes for Server Manager installation and startup are included in the file SVRMGR.TXT.

•	Procedural option

Features previously available only to users of the procedural option, such as stored procedures and triggers, are now available to all users. Although this is no longer a separate option, it is still installed separately. Additionally, you may now have multiple triggers of the same type on a single table.

•	Symmetric Replication

Oracle's symmetric replication facility, which lets you maintain multiple updatable copies of data at different sites in a distributed environment, is available on a separate release schedule from the Oracle Server and is described in a separate document.

•	Consistent Snapshot Refresh

Snapshot refresh groups now allow you to refresh a collection of related snapshots to a transaction consistent point in time. For example, this allows you to preserve master detail relationships between snapshots.

•	Enhancements to SQL and PL/SQL

Oracle7 release 7.1 conforms to the entry level ANSI SQL92 (ANSI X3.135-1992 and ISO 9075:1992) standard requirements.  This enhancement includes the use of the optional AS keyword to define column or expression aliases as well as allowing column or expression aliases to be used in the ORDER BY clause.  This enhancement also allows administrators to enforce SELECT privileges on tables for deletes and updates that require a scan of the table.

SQL has been enhanced to allow PL/SQL user-defined functions to be called from expressions in SQL statements.

•	Read-only tablespaces

You can now create read-only tablespaces to avoid backup and recovery of static data. For multimedia or archival applications, read-only tablespaces can be stored on low cost per bit media, such as CD-ROM and WORM drives.

•	Parallel recovery

You can now invoke multiple processes to concurrently recover your database, thus decreasing recovery time.

•	Improved security when connecting to remote databases

When you log on to a remote database, the password is now encrypted when it is sent over the network. Additionally, remote O/S-authorized DBA and OPER sessions over non-secure connections are now supported, even when a database is not available.

•	Parallel query option

Oracle7 with the new parallel query option improves the performance of bulk operations, such as data loading, queries, and index creation, by using parallel processing on multiple CPUs.

•	Dynamic SQL supplied package

The new DBMS_SQL package allows you to use dynamic SQL in stored procedures and PL/SQL anonymous blocks. This allows you to develop more flexible and general-purpose procedures.

•	SQL*Net Oracle Names

The SQL*Net Oracle Names feature allows you to use a database link without defining a connect string in the database. This is done by providing a central location for database link definitions.

•	Link to Oracle Office

The DBMS_OFFICE supplied package allows you to send a message to Oracle Office, in much the same manner that the DBMS_MAIL package, available with release 7.0 of the Oracle Server, allows you to send a message to Oracle*Mail. The DBMS_OFFICE package is available with Oracle*Office and is described in the Oracle Office Server Release Notes, Release 2.0.12.

•	Mapping of Trusted Oracle7 labels during Import

Exported Trusted Oracle MLSLABELs can now be imported to Trusted Oracle databases with different labels.

•	There are changes to dynamic performance tables, data dictionary views, and SQL syntax, as required to support the above enhancements.

 ________________________________________

Enabling and Disabling 7.1 Features

The following sections describe how to enable and disable the new features available with Oracle7 release 7.1.

Enabling 7.1 Features

After upgrading to Oracle7 release 7.1, to enable the new features associated release 7.1, set the COMPATIBLE parameter in the parameter file (INIT.ORA) to 7.1.0. Set this parameter before starting up the database.

COMPATIBLE = 7.1.0

The COMPATIBLE parameter must be set if you want to create read-only tablespaces, or if you want to create multiple triggers of the same type on a single table.

Disabling 7.1 Features

Before downgrading to an earlier release, you must disable certain features associated with release 7.1. To disable the new features associated with Oracle7 release 7.1, complete the following steps:

1.	Determine whether the new 7.1 features are in use. Check the following views for use of their corresponding feature:

View	Feature

DBA_TABLESPACES	read-only tablespaces

DBA_TRIGGERS	multiple same-type triggers per table

The following SQL statements are examples of queries that could be used to determine which 7.1 features have been enabled:

SELECT tablespace_name from DBA_TABLESPACES

   WHERE status = 'READ ONLY';

SELECT table_name, trigger_name,

     trigger_type||' '||triggering_event AS type_name

   FROM ALL_TRIGGERS

   WHERE (table_name,trigger_type,triggering_event) =

     (SELECT table_name,trigger_type,triggering_event

        FROM ALL_TRIGGERS

 	      GROUP BY table_name, trigger_type,triggering_event

        HAVING count(*) > 1)

   ORDER BY table_name, type_name;

2.	Drop or change any 7.1 features so that they are no longer in use.

•	Change the READ ONLY tablespaces to READ WRITE. For example, to make the read-only FLIGHTS tablespace writeable again, you would issue the following command:

  ALTER TABLESPACE flights READ WRITE;

•	Ensure that there is only one trigger of each type per table by either merging multiple triggers of the same type into one trigger or deleting the extra triggers.

•	Reset any new initialization parameters to their default values, and remove any changes that you have implemented to take advantage of these parameters.

For example, if you are using the new database connection security features, you must set the ORA_ENCRYPT_LOGIN environment variable and DBLINK_ENCRYPT_LOGIN initialization parameter to FALSE. You must also set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE and return to using CONNECT INTERNAL to perform database administration.

If you have begun using the new SQL syntax, you must set the SQL92_SECURITY initialization paramter to FALSE and remove this new syntax from your SQL statements. Any code written to take advantage of the DBMS_SQL package must also be removed. Any snapshot refresh groups that you have created will be ignored after you downgrade to release 7.0.

If you are using the parallel query option, you must alter any table definitions to not include the parallel clause.  Also you must reset any parallel query initialization parameters (for example, PARALLEL_MIN_SERVERS) to null.

3.	Issue the following command:

ALTER DATABASE RESET COMPATIBILITY

4.	Shut down the database.

5.	Restart the database without enabling the new features (set the COMPATIBLE parameter to 7.0.12 or above, excluding 7.1.0) and install an earlier release of Oracle (release 7.0).


CHAPTER 2. SQL ADDITIONS

This chapter describes changes to SQL syntax introduced with release 7.1 of the Oracle Server. The following changes are described in this chapter:

•	conforming with Entry level of the ANSI/ISO SQL92 standard

•	requiring SELECT privileges when executing a searched update or delete

•	referencing PL/SQL functions from SQL expressions

•	using sequences without restrictions in distributed SQL statements

 ________________________________________

ANSI SQL92 Entry Level Conformance

Release 7.0 of the Oracle7 Server fully complies with the ANSI/ISO SQL89 standard. The SELECT list, ORDER BY clause, and FIPS SQL flagging changes introduced in release 7.1 bring Oracle7 into Entry Level compliance with the recently adopted ANSI/ISO SQL92 standard (ANSI X3.135-1992, FIPS 127-2, and ISO 9075:1992).

 SELECT List

Column aliases in the SELECT list can optionally be separated from their expressions by the new AS keyword. Although the AS keyword adds no functionality it may make SQL statements easier to read. These column aliases effectively rename the SELECT list items. For example:

SELECT empno, ename AS name

FROM emp

 ORDER BY Clause

The ORDER BY clause now can reference column expression aliases defined in the SELECT list.

For example, the following statement which uses an ORDER BY column expression:

SELECT empno, ename, sal+comm AS comp

FROM emp

ORDER BY sal+comm

is equivalent to the following statement which uses an ORDER BY column expression alias:

SELECT empno, ename, sal+comm AS comp

FROM emp

ORDER BY comp

Both statements result in column names of EMPNO, ENAME, and COMP, and both order the results by SAL+COMM. The difference is that the second statement uses the column expression's alias in the ORDER BY clause rather than the column expression itself. This saves typing and can prevent errors because the expression need not be retyped.

The following cases summarize how the Oracle Server acts depending on the value used in the ORDER BY clause:

•	If the ORDER BY value is a column expression alias only (not a SELECT list column name), then the statement orders the data by the column expression values. For example:

SELECT empno, ename, sal+comm AS comp

FROM emp

ORDER BY comp

•	If the ORDER BY value is a column expression, then the statement orders the data by the column expression values. For example:

SELECT empno, ename, sal, sal+comm AS comp

FROM emp

ORDER BY sal+comm

•	If the ORDER BY value is a column name only (not also a column expression alias), then the statement orders the data by the column values. For example:

SELECT empno, ename, sal, sal+comm AS comp

FROM emp

ORDER BY ename

•	If the ORDER BY value is a SELECT list column name and a SELECT list column expression alias, then Oracle returns an error message. In this case, there is a naming conflict that Oracle cannot resolve. For example:

SELECT empno, ename, sal, sal+comm AS sal

FROM emp

ORDER BY sal  /* this returns an error */

returns

ORDER BY sal

         *

ORA-00960:  ambiguous column naming in select list

 Resolving Names

Oracle resolves names in the ORDER BY clause by first matching them to the expression names and aliases in the SELECT list. If the ORDER BY item matches more than one expression, Oracle returns an error message. If the ORDER BY item does not match an item, expression, or expression alias in the SELECT list, Oracle tries to match it to a column in the query's table(s). If there is no match, Oracle returns an error message.

This behavior is compatible with Oracle 7.0.14 releases and above. If your application requires behavior associated with pre-7.0.14 releases, use the SET COMPATIBILITY command in SQL*Plus, SQL*DBA, or Server Manager (setting it to V6) or use the DBMS=V6 command line option with the precompilers. In OCI applications, set the LNGFLG parameter to 0 (V6 behavior) in the OPARSE call for the SQL statement.

Note: If a column alias is the same name as a column in one of the query's tables, and the column is not part of the select list, Oracle orders by the aliased column (if the alias is specified in the ORDER BY clause). This can cause unexpected results, and therefore Oracle recommends that you avoid using column names as column aliases.

 Using Integers

The SQL92 standard does not officially include the use of integers when referring to the ordinal position of SELECT list items in the ORDER BY clause. This feature may also be taken out of future versions of the SQL standard. To ensure compliance with future standards, Oracle recommends that you do not use integers in the ORDER BY clause to reference SELECT list items. For example, the following query uses integers to refer to a SELECT list item's ordinal position:

SELECT empno, ename, sal, sal+comm AS comp

FROM emp

ORDER BY 4

Instead, use the column alias or a column name to refer to an expression or column.

SELECT empno, ename, sal, sal+comm AS comp

FROM emp

ORDER BY comp

 FIPS SQL Flagging

The Federal Information Processing Standard for SQL (FIPS 127-2) requires a way to identify SQL statements that use vendor-supplied extensions. Oracle provides a FIPS flagger through interactive SQL to help you write portable applications.

When SQL flagging is set on, your SQL statements are checked to see whether they include extensions that go beyond the ANSI/ISO SQL92 standard. If any non-standard constructs are found, the Oracle Server flags them as errors and displays the violating syntax.

The FIPS flagging feature supports flagging through interactive SQL statements submitted via SQL*DBA, Server Manager, and SQL*Plus. The Oracle Precompilers already support FIPS flagging of embedded SQL.

When flagging is on and non-standard SQL is encountered, the message returned is

ORA-00097: Use of Oracle SQL feature not in SQL92 %s Level

where %s can be either ENTRY, INTERMEDIATE, or FULL.

Example	The following SQL statements produce the ORA-00097 message and a list corresponding to the violating extended SQL syntax.

SQL> ALTER SESSION SET FLAGGER = ENTRY;

SQL> SELECT trunc (1.1,1) FROM emp;

returns:

SELECT trunc (1.1,1) FROM emp

       *

ORA-00097: Use of Oracle SQL feature not in SQL92 Entry Level

ORA-06550: line 2, column 8:

PLS-01416: Use of <id> (<value>...) here

ORA-06550: line 2, column 8:

PLS-01452: This function is not part of the ANSI standard

 Turning on Flagging

To turn on FIPS flagging (the default is OFF) through interactive SQL, issue the following statement after connecting to the database:

where:

ENTRY	flags for SQL92 Entry level

INTERMEDIATE	flags for SQL92 Intermediate level

FULL	flags Full level

OFF	turns off flagging

In release 7.1, there is no difference between Entry, Intermediate, or Full level flagging.

To turn on FIPS flagging through SQL*DBA or SQL*Plus, issue the SET command with the FLAGGER option. That is, rather than ALTER SESSION SET FLAGGER = <option>, you execute SET FLAGGER <option>. Server Manager requires that you use the ALTER SESSION command to enable and disable flagging.

Note: Interactive SQL tools (such as SQL*Plus) check current SQL statements; the precompilers check embedded SQL constructs. Once flagging has been set in a session, subsequent executions of the ALTER SESSION SET FLAGGER command cause the ORA-00097 error to be returned because ALTER SESSION is not part of the ANSI standard. However, the ALTER SESSION SET FLAGGER command executes regardless of the returned error. This allows flagging to be turned on and off without disconnecting.

 SELECT Privileges When Updating or Deleting

In conformance with Entry level SQL92, Oracle7 release 7.1 allows the security administrator to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in a WHERE or SET clause.

This feature can prevent unauthorized users from learning about the table's data through a covert channel. For example, suppose a user has the DELETE privilege on the EMP table, but is only permitted SELECT access on a view of the EMP table to prevent viewing rows whose salaries are greater than $50,000. That user can execute the following statements:

DELETE FROM emp WHERE sal > 50000;

5 rows deleted.

ROLLBACK;

This user has just learned that there are five employees whose salaries are greater than $50,000. With this feature, only users with SELECT privilege on the table are permitted to perform such a delete.

To use this feature, you must set the SQL92_SECURITY initialization parameter in your parameter file to TRUE. The default is FALSE.

When you grant the UPDATE or DELETE object privileges to a user or role, you should always also grant the SELECT privilege for the same table.

Examples	The following statements require SELECT privileges on table TITAN:

UPDATE titan SET c1 = c2;

UPDATE titan SET c1 = 2 WHERE c1 = 1;

DELETE titan WHERE c1=1;

The following statements DO NOT require SELECT privileges on table TITAN:

UPDATE titan SET c = 1;

DELETE titan;

 Compatibility with Previous Releases

To ensure compatibility with Oracle release 7.0 or release 6, when migrating to release 7.1, keep the SQL92_SECURITY parameter at FALSE, thus inhibiting this feature.

 ________________________________________

Referencing PL/SQL Functions in SQL Expressions

In release 7.1 of the Oracle Server, you can include user-written PL/SQL functions in SQL expressions. See the PL/SQL Release  2.1 and Oracle Precompilers Release 1.6 Addendum for more information on this feature.

By using PL/SQL functions in SQL statements, you can do the following:

•	Increase user productivity by extending SQL. Expressiveness of the SQL statement increases where activities are too complex, too awkward, or unavailable with SQL.

•	Increase query efficiency. Functions used in the WHERE clause of a query can filter data using criteria that would otherwise have to be evaluated by the application.

•	Manipulate character strings to represent special datatypes (for example, latitude, longitude, or temperature).

•	Provide parallel query execution. If the query is parallelized, SQL statements in your PL/SQL function may be executed in parallel also (using the parallel query option).

 Using PL/SQL Functions

PL/SQL functions must be created as top-level functions or declared within a package specification before they can be named within a SQL statement. PL/SQL functions are created as top-level functions by using the CREATE FUNCTION statement. Packaged functions are specified within a package with the CREATE PACKAGE statement and created in the CREATE PACKAGE BODY statement.

See the Oracle7 Server Application Developer's Guide for more information about creating functions and packages.

Stored PL/SQL functions are used in the same manner as built-in Oracle functions (such as SUBSTR or ABS). PL/SQL functions can be placed wherever an ORACLE function can be placed within a SQL statement; that is, wherever expressions can occur in SQL.

For example, they can be called from the following:

•	the select list of the SELECT command

•	the condition of the WHERE and HAVING clause

•	the CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses

•	the VALUES clause of the INSERT command

•	the SET clause of the UPDATE command

Stored PL/SQL functions cannot be called from a CHECK constraint clause of a CREATE or ALTER TABLE command or be used to specify a default value for a column because these situations require an unchanging definition.

Stored PL/SQL or Oracle functions can be invoked from:

•	the body of another procedure or a trigger

•	SQL expressions

See the Oracle7 Server Application Developer's Guide for more information about using procedures, functions, and packages.

Note: PL/SQL procedures are not directly callable from SQL statements. However, functions called from a PL/SQL statement or referenced in a SQL expression can call a PL/SQL procedure.

 Syntax

Use the following syntax to reference a PL/SQL function from SQL:

 Naming Conventions

If only one of the optional schema or package names is given, the first identifier can be either a schema name or a package name. For example, to determine whether PAYROLL in the reference PAYROLL.TAX_RATE is a schema or package name, Oracle proceeds as follows:

•	Oracle first checks for the PAYROLL package in the current schema.

•	If a PAYROLL package is not found, Oracle looks for a schema named PAYROLL that contains a top-level TAX_RATE function. If the TAX_RATE function is not found in the PAYROLL schema, an error message is returned.

•	If the PAYROLL package is found in the current schema, Oracle looks for a TAX_RATE function in the PAYROLL package. If a TAX_RATE function is not found in the PAYROLL package, an error message is returned.

You can also refer to a stored top-level function using any synonym that you have defined for it.

 Name Precedence

With PL/SQL, the names of database columns take precedence over the names of functions with no parameters. For example, if schema SCOTT creates the following two objects:

CREATE TABLE emp(new_sal NUMBER ...);

CREATE FUNCTION new_sal RETURN NUMBER IS ...;

then in the following two statements, the reference to NEW_SAL refers to the column EMP.NEW_SAL:

SELECT new_sal FROM emp;

SELECT emp.new_sal FROM emp;

To access the function NEW_SAL, you would enter:

SELECT scott.new_sal FROM emp;

Example	For example, to call the TAX_RATE PL/SQL function from schema SCOTT, execute it against the SS_NO and SAL columns in TAX_TABLE, and place the results in the variable INCOME_TAX, specify the following:

SELECT scott.tax_rate (ss_no, sal)

INTO income_tax

FROM tax_table

WHERE ss_no = tax_id;

Listed below are sample calls to PL/SQL functions that are allowed in SQL expressions.

circle_area(radius)

payroll.tax_rate(empno)

scott.payroll.tax_rate(dependents, empno)@ny

 Arguments

To pass any number of arguments to a function, supply the arguments within the parentheses. You must use positional notation; named notation is not currently supported. For functions that do not accept arguments, omit the parentheses.

The argument's datatypes and the function's return type are limited to those types that are supported by SQL. For example, you cannot call a PL/SQL function that returns a PL/SQL BINARY_INTEGER from a SQL statement.

See the PL/SQL Release 2.1 and Oracle Precompilers Release 1.6 Addendum for more information on passing arguments.

 Restrictions

PL/SQL functions called from SQL statements cannot violate the following restrictions:

•	The functions cannot contain OUT or IN OUT parameters; all parameters must be IN parameters.

•	The functions cannot update the database.

•	Only local functions that are referenced in a SELECT list, VALUES clause of an INSERT statement, or SET clause of an UPDATE statement can update a variable defined in a package.

•	Remote functions cannot read or write package state.

•	Functions that read or write package state cannot take advantage of the parallel query option.

•	Functions cannot be called from a CHECK constraint or DEFAULT clause in a CREATE or ALTER TABLE command

See the Oracle7 Server Application Developer's Guide for more information on PL/SQL functions and packages.

 Privileges Required

To call a PL/SQL function from SQL, you must either own or have EXECUTE privileges on the function. To select from a view defined with a PL/SQL function, you are required to have SELECT privileges on the view. No separate EXECUTE privileges are needed to select from the view.

 ________________________________________

Referencing Sequences in Distributed SQL Statements

In Oracle release 7.0, all of the following object types referenced in distributed SQL statement must be located on the same database:

•	updated tables

•	tables locked by the FOR UPDATE clause of a SELECT statement

•	tables from which LONG columns are selected

•	sequences

In release 7.1, this restriction no longer applies to sequences.

Example 1	In release 7.1, you can issue the following statement which locks the EMP table on the NY database and selects the next value of the SEQ1 sequence on the local database:

SELECT emp.ename, seq1.NEXTVAL

FROM emp@ny emp

WHERE empno = 7369

FOR UPDATE OF emp

You could not issue this statement in release 7.0 because the EMP table and the SEQ1 sequence are on different databases.

Example 2	In release 7.1, you can issue the following statement which inserts a row into the EMP table in the NY database using EMPSEQ sequence on the local database:

INSERT INTO emp@ny (empno,ename,job,mgr,

                    hiredate,sal,comm,deptno)

VALUES (empseq.nextval,'JACK','CLERK', 7782,

        SYSDATE,1300,NULL,10)

You could not issue this statement in release 7.0 because the EMP table and the EMPSEQ sequence are on different databases.


CHAPTER 3. READ-ONLY TABLESPACES

This chapter describes read-only tablespaces, including:

•	the use of read-only tablespaces

•	making tablespaces read-only

•	making read-only tablespaces writeable

•	making backups of read-only tablespaces

•	recovery and read-only tablespaces

 ________________________________________

Overview

The ALTER TABLESPACE command has two new options: READ ONLY and READ WRITE. The files of a read-only tablespace are never updated by Oracle, and are therefore allowed to reside on read-only media, such as CD-ROMs or WORM drives. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database.

Note: Because a tablespace can only be brought online to the database in which it was created, read-only tablespaces are not meant to satisfy archiving or data publishing requirements.

Whenever you create a new tablespace, it is always created as read-write. The READ ONLY option of the ALTER TABLESPACE command allows you to change the tablespace to read-only, making all of its associated datafiles read-only as well. You can then use the READ WRITE option to make a read-only tablespace writeable again.

Read-only tablespaces cannot be modified. Therefore, they do not need repeated backup. Also, should you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.

It is possible to drop items, such as tables and indexes, from a read-only tablespace, just as they can be dropped from an offline tablespace. However, objects cannot be created or altered in a read-only tablespace.

 ________________________________________

Making a Tablespace Read-Only

Use the SQL command ALTER TABLESPACE to change a tablespace to read-only. For example, the following statement makes the FLIGHTS tablespace read-only:

ALTER TABLESPACE flights READ ONLY

This command prevents any further write operations on the datafiles in the tablespace. After making the tablespace read-only, you should back-up the tablespace.

Once a tablespace is read-only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL command ALTER DATABASE RENAME.

Prerequisites

Before you can make a tablespace read-only, the following conditions must be met. It may be easiest to meet these restrictions by performing this function in restricted mode, so that only users with the RESTRICTED SESSION system privilege can be logged on.

•	The tablespace must be online.

•	There must not be any active transactions in the entire database.

This is necessary to ensure that there is no undo information that needs to be applied to the tablespace.

•	The tablespace must not contain any active rollback segments.

For this reason, the SYSTEM tablespace can never be made read-only, since it contains the SYSTEM rollback segment. Additionally, because the rollback segments of a read-only tablespace are not accessible, it is recommended that you drop the rollback segments before you make a tablespace read-only.

•	The tablespace must not be involved in an online backup, since the end of a backup updates the header file of all datafiles in the tablespace.

•	The COMPATIBLE initialization parameter must be set to 7.1.0 or greater.

For somewhat improved performance while accessing data in a read-only tablespace, you might want to issue a query that accesses all of the blocks of the tables in the tablespace just before making it read-only. A simple query, such as SELECT COUNT (*), executed against each table will ensure that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for Oracle to check the status of the transactions that most recently modified the blocks.

 Privileges Required to Make a Tablespace Read-Only

You must have the ALTER TABLESPACE system privilege to make a tablespace read-only.

 ________________________________________

Making a Read-Only Tablespace Writeable

Whenever you create a tablespace, it is both readable and writeable. To change a read-only tablespace back to a read-write tablespace, use the SQL command ALTER TABLESPACE. The following command makes the FLIGHTS tablespace writeable:

ALTER TABLESPACE flights READ WRITE;

Making a read-only tablespace writeable updates the control file for the datafiles, so that you can use the read-only version of the datafiles as a starting point for recovery.

 Prerequisites

To issue this command, all of the datafiles in the tablespace must be online. Use the DATAFILE ONLINE option of the ALTER DATABASE command to bring a datafile online. The V$DATAFILE view lists the current status of a datafile.

 Privileges Required to Make a Tablespace Writeable

You must have the ALTER TABLESPACE system privilege to change a read-only tablespace to a read-write tablespace.

 ________________________________________

Read-Only Tablespaces and Backup

You can create backups of a read-only tablespace while the database is open. Immediately after making a tablespace read-only, you should back up the tablespace. As long as the tablespace remains read-only, there is no need to perform any further backups of it.

Unlike backups of writeable tablespaces, you do not need to mark the beginning and end of the online backup of a read-only tablespace. Using the BEGIN and END BACKUP commands with reference to a read-only tablespace causes an error.

After you change a read-only tablespace to a read-write tablespace, you need to resume your normal backups of the tablespace, just as you do when you bring an offline read-write tablespace back online.

Bringing the datafiles of a read-only tablespace online does not make these files writeable, nor does it cause the file header to be updated. Thus it is not necessary to perform a backup of these files, as is necessary when you bring a writeable datafile back online.

 ________________________________________

Read-Only Tablespaces and Recovery

This section describes how read-only tablespaces affect instance and media recovery.

 Instance Recovery

No recovery is ever needed on read-only datafiles after instance recovery. Recovery during startup verifies that an online read-only file does not need any media recovery. That is, the file was not restored from a backup taken before it was made read-only. If you restore a read-only tablespace from a backup taken before the tablespace was made read-only, you cannot access the tablespace until you complete media recovery.

 Media Recovery

Normal media recovery does not check the read-only status of a data file. When you perform media recovery of a tablespace that was once read-only, you have three possible options depending upon when the tablespace was made read-only and when you performed the most recent backup. These scenarios are illustrated in Figure 3 - 1.

Figure 3 - 1. Type of Media Recovery

Case 1	The tablespace being recovered is read-only, and was read-only when the last backup occurred. In this case, you can simply restore the tablespace from the backup. There is no need to apply any redo information.

Case 2	The tablespace being recovered is writeable, but was read-only when the last backup occurred. In this case, you would need to restore the tablespace from the backup and apply the redo information from the point of time when the tablespace was made writeable.

Case 3	The tablespace being recovered is read-only, but was writeable when the last backup occurred. Because you should always backup a tablespace after making it read-only, you should not experience this situation. However, if this does occur, you must restore the tablespace from the backup and recover up to the time that the tablespace was made read-only.

Unlike writeable datafiles, read-only datafiles are not automatically taken offline if a media failure occurs. If you experience media failure that affects only a portion of your datafiles, you should take these datafiles offline and follow the instructions in the Oracle7 Server Administrator's Guide for performing recovery of offline tablespaces in an open database.

 Using a Backup Control File

Media recovery with the USING BACKUP CONTROLFILE option does check for read-only files. It is an error to attempt recovery of a read-only file. You can avoid this error by taking all datafiles from read-only tablespaces offline before doing recovery with a backup control file. This means that it is very important to have the correct version of the control file for the recovery. If the tablespace will be read-only when the recovery is complete, then the control file must be from a time when the tablespace was read-only. Similarly, if the tablespace will be read-write at the end of recovery, it should be read-write in the control file. If the appropriate control file is not available, you should create a new control file with the CREATE CONTROLFILE command.

 Re-creating a Control File

If you need to re-create a control file for a database with read-only tablespaces, you must follow some special procedures. Issue the command ALTER DATABASE BACKUP CONTROLFILE TO TRACE to get a listing of the procedure that you need to follow. The procedure is essentially the same as for offline normal tablespaces, except that you need to bring the tablespace online after the database is open. The TRACE option of the ALTER DATABASE BACKUP CONTROLFILE command is described in the Oracle7 Server Administrator's Guide.

Re-creating a control file can also affect the recovery of read-write tablespaces that were at one time read-only. If you re-create the control file after making the tablespace writeable, Oracle can no longer determine when the tablespace was changed from read-only to read-write. Thus you can no longer recover from the read-only version of the tablespace. Instead, you must recover from the time of the most recent backup.

 ________________________________________

Read-Only Tablespaces and Import

There are no special requirements for exporting a read-only tablespace. However, for Oracle to write the data during an import, you must import the data into a writeable tablespace. If you want the tablespace to remain read-only after import, you must ensure that you have met the requirements outlined on page 3 - 3, and reissue the ALTER TABLESPACE READ ONLY command.

 ________________________________________

Online and Offline Datafiles

A read-only tablespace is neither online nor offline. Issuing the ALTER TABLESPACE command with the ONLINE or OFFLINE option does not change the read-only state of the tablespace; rather, it causes all of the datafiles in the tablespace to be brought online or offline.

Offline datafiles cannot be accessed. Bringing a datafile in a read-only tablespace online makes the file readable. The file cannot be written to unless its associated tablespace is returned to the read-write state. The files of a read-only tablespace can independently be taken online or offline using the DATAFILE option of the ALTER DATABASE command.

 ________________________________________

Restrictions on Read-Only Tablespaces

You cannot open a database that contains read-only tablespaces if you are using any release of Oracle before release 7.1. You also cannot open such a database if your COMPATIBLE initialization parameter is set to a version earlier than 7.1.0. For information on downgrading to an earlier release, see page 1 - 2.

Before you can return to an earlier release of Oracle, you must make all of your read-only tablespaces writeable. Query the STATUS column of the DBA_TABLESPACES view to determine which tablespaces are read-only.

You cannot add datafiles to a tablespace that is read-only, even if you take the tablespace offline. When you add a datafile, Oracle must update the file header, and this write operation is not allowed.

To update a read-only tablespace, you must first make the tablespace writeable. After updating the tablespace, you can then reset it to be read-only.


CHAPTER 4. PARALLEL RECOVERY

This chapter describes the capability of the Oracle Server release 7.1 to perform instance and media recovery in parallel. Parallel recovery can decrease the amount of time required for instance startup or media recovery. Topics in this chapter include:

•	performing recovery in parallel

•	specification of initialization parameters

•	syntax of recovery commands

 ________________________________________

Performing Recovery in Parallel

Recovery reapplies the changes generated by several concurrent processes, and therefore instance or media recovery in release 7.0 can take longer than the time it took to initially generate the changes to a database. Previously, a single process applied the changes in the redo log files sequentially. In release 7.1, several processes can simultaneously apply changes from the redo log files.

One form of parallel recovery can be performed by spawning several SQL*DBA sessions and issuing the RECOVER DATAFILE command on a different set of data files in each session. However, this method causes each SQL*DBA session to read the entire redo log file.

With release 7.1, instance and media recovery can be parallelized by specifying an initialization parameter or command-line options to the RECOVER command. The Oracle Server can now use one process to sequentially read the log files and dispatch redo information to several recovery processes to apply the changes from the log files to the datafiles. The recovery processes are started automatically by Oracle, so there is no need to use more than one session to perform recovery.

 What Situations Benefit from Parallel Recovery

In general, parallel recovery is most effective at reducing recovery time when several datafiles on several different disks are being recovered concurrently. Crash recovery (recovery after instance failure) and media recovery of many datafiles on many different disk drives are good candidates for parallel recovery.

Additional Information:  The performance improvement from parallel recovery is also dependent upon whether the operating system supports asynchronous I/O. If asynchronous I/O is not supported, parallel recovery can dramatically reduce recovery time. If asynchronous I/O is supported, the recovery time may only be slightly reduced by using parallel recovery. Consult your operating system documentation to determine whether asynchronous I/O is supported on your system.

 Recovery Processes

In a typical parallel recovery situation, one process is responsible for reading and dispatching redo entries from the redo log files. This is the dedicated server process that begins the recovery session, typically a SQL*DBA session, a Server Manager session, or an application designed to use the ALTER DATABASE RECOVER ... command. The server process reading the redo log files enlists two or more recovery processes to apply the changes from the redo entries to the datafiles. Figure 4 - 1 illustrates a typical parallel recovery session.

Figure 4 - 1. Typical Parallel Recovery Session

In most situations, one recovery session and one or two recovery processes per disk drive containing datafiles needing recovery is sufficient. Recovery is a disk-intensive activity as opposed to a CPU-intensive activity, and therefore the number of recovery processes needed is dependent entirely upon how many disk drives are involved in recovery. In general, a minimum of eight recovery processes is needed before parallel recovery can show improvement over a serial recovery.

 Specifying Parallel Recovery

The RECOVERY_PARALLELISM initialization parameter specifies the number of concurrent recovery processes to use for any recovery operation. Since crash recovery occurs at instance startup, this parameter is useful for specifying the number of processes to use for crash recovery. The value of this parameter is also the default number of processes used for media recovery if the PARALLEL clause of the RECOVER command is not specified. The value of this parameter must be greater than one and cannot exceed the value of the PARALLEL_MAX_SERVERS parameter. See Appendix A "Reference" for more information about initialization parameters.

Note: In an Oracle Parallel Server environment, the number of recovery processes specified with RECOVERY_PARALLELISM is divided among the available instances.

The RECOVER command in SQL*DBA and Server Manager has been enhanced to allow users to specify the degree of parallelism for a recovery session. The syntax for the RECOVER command is illustrated below.

PARALLEL	DEGREE specifies the number of recovery processes used to apply redo entries to datafiles on each instance. An integer specified for DEGREE overrides the initialization parameter RECOVERY_PARALLELISM.

DEGREE DEFAULT indicates that twice the number of datafiles being recovered is the number of recovery processes to use.

INSTANCES specifies the number of instances to use for parallel recovery. The number of recovery processes specified with DEGREE is used on each instance, thus the total number of recovery processes is the integer specified with DEGREE multiplied by the integer specified with INSTANCES. INSTANCES is only pertinent for the Oracle Parallel Server.

         INSTANCES DEFAULT or not including the INSTANCES keyword causes has port-specific consequences. Consult your Oracle operating system documentation for a description of the default behavior of the INSTANCES DEFAULT specification.

NOPARALLEL	 The NOPARALLEL keyword specifies that recovery is to proceed serially.  Note that a specification of PARALLEL(DEGREE 1 INSTANCES 1) is equivalent to specifying the NOPARALLEL keyword.

The RECOVER clause of the ALTER DATABASE command has similar parallel recovery options. The syntax of the RECOVER clause is described below.


CHAPTER 5. DATABASE CONNECTION SECURITY

Oracle7 release 7.1 provides additional security for client/server and server/server connections. This chapter discusses the following topics:

•	encrypted passwords for connections to database servers

•	improved security for connections by privileged users, formerly known as CONNECT INTERNAL

•	separation of database administration from the role of operating system administration

•	support for remote database administration

 ________________________________________

Overview

Release 7.1 of the Oracle Server introduces two new features that allow you to better protect the confidentiality of your corporate information. These features are password encryption and password files.

password encryption	In earlier releases of the Oracle Server, passwords were passed from client to server, or server to server as clear text. This provided an opportunity for malicious users to eavesdrop on network lines in an attempt to capture password information. With release 7.1 you can require that all passwords be sent in encrypted form. The password encryption method used encrypts to a different value on each connection attempt, further improving security by making it more difficult to decipher.

password files	Database startup and shutdown are powerful administrative capabilities. To protect their use, with release 7.0, only those who could connect to Oracle as INTERNAL could execute them. Release 7.1 provides an added layer of security by allowing you to require that these users connect with a unique name and password, allowing you to audit these connections by user ID. This password information is stored in an external, encrypted file. The use of a secure password file also permits remote database administration over non-secure network connections.

 ________________________________________

Secure Connections with Encrypted Passwords

To better protect the confidentiality of your password, Oracle7 release 7.1 can be configured to use encrypted passwords for client/server and server/server connections.

You can require that the password used to verify a connection always be encrypted by setting the following values.

•	Set the ORA_ENCRYPT_LOGIN environment variable to TRUE on the client machine.

•	Set the DBLINK_ENCRYPT_LOGIN server initialization parameter to TRUE.

If enabled at both the client and server, passwords will not be sent across the network "in the clear", but will be encrypted using a modified DES (Data Encryption Standard) algorithm.

The DBLINK_ENCRYPT_LOGIN parameter is used for connections between two Oracle servers (for example, when performing distributed queries). If you are connecting from a client, Oracle checks the ORA_ENCRYPT_LOGIN environment variable.

Additional Information: The name of the environment variable ORA_ENCRYPT_LOGIN may vary. Consult the Oracle documentation for your operating system for the name of this variable on your platform.

Whenever you attempt to connect to a server using a password, Oracle encrypts the password before sending it to the server. If the connection fails and auditing is enabled, the failure is noted in the audit log. Oracle then checks the appropriate DBLINK_ENCRYPT_LOGIN or ORA_ENCRYPT_LOGIN value. If it is set to FALSE, Oracle attempts the connection again using an unencrypted version of the password. If the connection is successful, the success replaces the previous failure in the audit log, and the connection proceeds. To prevent malicious users from forcing Oracle to reattempt a connection with an unencrypted version of the password, you must set the appropriate values to TRUE.

Oracle releases before release 7.1 are not designed to accept encrypted passwords. If you know that you will attempt connections from a release 7.1 server to earlier versions of the Oracle Server (for example, if you are using a database link for distributed queries) you must set DBLINK_ENCRYPT_LOGIN to FALSE for the connection to succeed. If you will be connecting from a new client to an older version of the server (for example, if you run Server Manager against an earlier version of Oracle) you must set ORA_ENCRYPT_LOGIN to FALSE.

 Connecting with an Old Password

Because of a change in the password encryption mechanism between release 6.0 and release 7.1 of the Oracle Server, the first time you connect to an instance using a password that has not been updated since version 6.0, the connection must be made with an unencrypted version of the password. Thereafter, connections using the encrypted form of the password will succeed, because the first time that you access a version 6.0 password, Oracle updates it using the new encryption method. A version 6.0 password is any password that you have not changed since upgrading to Oracle7.

For example, if you are migrating a version 6.0 database, you must set ORA_ENCRYPT_LOGIN to FALSE before you can connect to an instance using release 7.1 of the Oracle Server. After you have successfully connected once, you can change this parameter to TRUE.

 ________________________________________

Secure Connections for Database Administration

When a privileged user connects to an instance to perform database administration tasks, it is important to be able to establish the true identity of that user. Therefore, with Oracle7 release 7.1, you can require that users accessing the SYS schema must connect using a unique username and password. Privileged connections to the database are always audited, whether database auditing is enabled or not. Requiring privileged users to connect with a specific username (as opposed to SYS or INTERNAL) allows you to capture this information in the OS audit trail, if one is avaialable, for greater accountability. See page B - 19 for more information on auditing to the OS audit trail.

When a privileged user attempts a connection, Oracle first checks the value of the new REMOTE_LOGIN_PASSWORDFILE initialization parameter. If this parameter is set to NONE, the default, the user must be authenticated by the operating system. OS authentication is only allowed for local or secure remote connections; otherwise, the connection will fail. Operating system authentication was first supported in release 7.0 of Oracle.

If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE, Oracle checks an externally stored password file for the username/password combination supplied by the user. If it locates the user in the file and the user has the appropriate authorization, the connection can proceed, and the connection by this user is noted in the audit trail. EXCLUSIVE password files provide the greatest level of security by allowing you to audit all privileged connections by unique usernames. The use of a secure password file allows connections to succeed even over non-secure remote connections.

If REMOTE_LOGIN_PASSWORDFILE is set to SHARED, all privileged users must connect as user SYS with the appropriate password. Although SHARED password files do not provide the same level of accountability as EXCLUSIVE password files, they do allow for remote database administration over non-secure connections, and can be useful for individual database administrators who are responsible for multiple databases.

 ________________________________________

Choosing a Connection Method

Release 7.1 allows you to perform database administration more securely by using password files. Oracle7 release 7.0 uses operating system authentication to authorize users to perform database administration. Release 7.1 introduces an additional facility, password files, to authenticate users as database administrators. Password files are most often required by users performing remote database administration, but may be used for local administration. Use Figure 5 - 1 to determine which of these two methods you can use.

Figure 5 - 1. User Authentication Methods

These methods of connection are meant to replace the CONNECT INTERNAL syntax provided with earlier versions of the Oracle Server. (CONNECT INTERNAL continues to be supported for backwards compatibility only.) In addition to the new connection methods, release 7.1 introduces two new system privileges, SYSDBA and SYSOPER. These privileges provide the same access to the database as the OSDBA and OSOPER roles associated with CONNECT INTERNAL. The operations allowed for these privileges are as follows:

SYSOPER	permits you to perform STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, and RECOVER, and includes the RESTRICTED SESSION privilege

SYSDBA	contains all system privileges with ADMIN OPTION, and the SYSOPER system privilege; permits CREATE DATABASE and time-based recovery

Using Operating System Authentication

If you choose, you can have your operating system authenticate users performing database administration operations. The following steps outline the use of OS authentication.

1.	Set up the user to be authenticated by the operating system. The procedure for OS authentication has not changed from release 7.0 of the Oracle Server and is described in the Oracle7 Server Administrator's Guide.

2.	Make sure that the new initialization parameter, REMOTE_LOGIN_PASSWORD, is set to NONE. This is the default value for this parameter.

3.	Authenticated users should now be able to connect to a local database, or to connect to a remote database over a secure connection, by typing one of the following commands:

CONNECT / AS SYSOPER

CONNECT / AS SYSDBA

If you were able to successfully connect as INTERNAL using an earlier release of Oracle, you should be able to continue to connect successfully using the new syntax shown in step 3. Note that to connect as SYSOPER or SYSDBA using OS authentication you do not have to have been granted the SYSOPER or SYSDBA system privileges. Instead, the server verifies that you have been granted the appropriate OSDBA or OSOPER roles at the operating system level.

Additional Information: If you are performing remote database administration, you should consult your platform-specific SQL*Net documentation to determine if you are using a secure connection. Most popular connection protocols, such as TCP/IP and DECnet, are not secure, regardless of which version of SQL*Net you are using.

 Using a Password File

If you have determined that you need to use a password file to authenticate users performing database administration, you must complete the steps outlined below. Each of these steps is explained in more detail in the following sections of this chapter.

1.	Create the password file using the ORAPWD utility. The command that you issue should follow the format shown below.

ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users

2.	Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.

3.	Add users to the password file by using SQL to grant the appropriate privileges to each user who needs to perform database administration, as shown in the following examples.

GRANT SYSDBA TO scott

GRANT SYSOPER TO scott

4.	Privileged users should now be able to connect to the database by using a command similar to the one shown below.

CONNECT scott/tiger@acct.hq.com AS SYSDBA

Additional Information: Some platforms provided support for password files before release 7.1. If you are currently using a password file, you should consult your platform-specific Oracle documentation for additional information on migrating to the new password file utility.

 ________________________________________

Creating a Password File

You can create a password file using the password file creation utility, ORAPWD or, for selected operating systems, you can create this file as part of your standard installation.

Additional Information: Consult your platform-specific Oracle documentation for information on using the installer utilityto install the password file.

 Using ORAPWD

When you invoke the password file creation utility without supplying any parameters, you receive a message indicating the proper use of the command as shown in the following sample output:

> orapwd

Usage: orapwd file=<fname> password=<password> entries=<users>

  where

    file - name of password file (mand),

    password - password for SYS and INTERNAL (mand),

    entries - maximum number of distinct DBAs and OPERs (opt),

  There are no spaces around the equal-to (=) character.

For example, the following command creates a password file named ACCT.PWD that allows up to 30 privileged users with different passwords. The file is initially created with the password SECRET for users connecting as INTERNAL or SYS.

> ORAPWD FILE=orasidpwfile.ora PASSWORD=secret ENTRIES=30

The following sections describe each of the parameters to the ORAPWD utility.

 File

This parameter sets the name of the password file being created. You must specify the full pathname for the file. The contents of this file are encrypted, and the file is not user-readable. This parameter is mandatory.

On some platforms, the name of the password file is derived from the SID. If so, you must specify this predefined name when using the ORAPWD utility. On other systems the name of the password file is stored in an environment variable, such as ORA_sid_PWFILE. If your system uses an environment variable, you must set this variable on the server platform to match the pathname that you specified for the file before starting the instance.

If you are running multiple instances of Oracle using the Oracle Parallel Server, the environment variable for each instance should point to the same password file.

Additional Information: Consult your platform-specific Oracle documentation for the exact name of the password file, or for the name of the environment variable used to specify this name for your operating system.

Warning: It is critically important to the security of your system that you protect your password file, as well as the environment variables that identify the location of the password file. Any user with access to these could potentially compromise the security of the connection.

 Password

The password for INTERNAL and SYS. If you issue the ALTER USER command to change the password after connecting to the database, both the password stored in the data dictionary and the password stored in the password file are updated. The INTERNAL user is supported for backwards compatibility only. This parameter is mandatory.

 Entries

The maximum number of entries allowed in the password file. This corresponds to the maximum number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. Entries can be reused as users are added to and removed from the password file. Because you must create a new password file if you ever need to exceed this limit, it is safest to select a number larger than you think that you will ever need. This parameter is required if you ever want this password file to be EXCLUSIVE.

 ________________________________________

Setting REMOTE_LOGIN_PASSWORDFILE

In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are described below.

Note: To STARTUP an instance or database, you must use SQL*DBA or Server Manager. You must specify a database name and a parameter file to initialize the instance settings. You may specify a fully-qualified remote database name using SQL*Net, however, the initialization parameter file and any associated files, such as a configuration file, must exist on the client machine. That is, the parameter file must be on the machine where you are running SQL*DBA or Server Manager. Database STARTUP is described in greater detail in your Oracle7 Server Administrator's Guide.

NONE	Setting this parameter to NONE causes Oracle to behave as if the password file does not exist. That is, no privileged connections are allowed over non-secure connections. NONE is the default value for this parameter.

EXCLUSIVE	An EXCLUSIVE password file can be used with only one database. Only an EXCLUSIVE file can contain the names of users other than SYS and INTERNAL. Using an EXCLUSIVE password file allows you to grant SYSDBA and SYSOPER system privileges to individual users and have them connect as themselves.

SHARED	A SHARED password file can be used by multiple databases. However, the only users recognized by a SHARED password file are SYS and INTERNAL; you cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must connect using the same name, SYS, and password. This option is useful if you have a single DBA administering multiple databases.

Suggestion: To achieve the greatest level of security, you should set the REMOTE_LOGIN_PASSWORDFILE file initialization parameter to EXCLUSIVE immediately after creating the password file.

 ________________________________________

Adding Users to a Password File

When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information is added to the password file. If the server does not have an EXCLUSIVE password file, that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, you receive an error message if you attempt to grant these privileges.

A user's name only remains in the password file as long as that user has at least one of these two privileges. When you revoke the last of these privileges from a user, that user is removed from the password file.

The process of creating a password file and adding new users to it is outlined below.

1.	Follow the instructions on page 5 - 7 for creating a password file.

2.	Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.

3.	Connect with SYSDBA privileges as shown in the following example:

CONNECT SYS/change_on_install AS SYSDBA

4.	Startup the instance and create the database if necessary, or mount and open an existing database.

5.	Create users as necessary. Grant SYSOPER or SYSDBA privileges to yourself and other users as appropriate.

6.	These users are now added to the password file and can connect to the database as SYSOPER or SYSDBA with a username and password (instead of using SYS). The use of a password file does not prevent OS authenticated users from connecting if they meet the criteria for OS authentication.

  Granting and Revoking SYSOPER and SYSDBA Privileges

If your server is using an EXCLUSIVE password file, use the GRANT command to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example.

GRANT SYSDBA TO scott

Use the REVOKE command to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example.

REVOKE SYSDBA FROM scott

Because SYSDBA and SYSOPER are the most powerful database privileges, the ADMIN OPTION is not used. Only users currently connected as SYSDBA (or INTERNAL) can grant SYSDBA or SYSOPER system privileges to another user. This is also true of REVOKE. These privileges cannot be granted to roles, since roles are only available after database startup. Do not confuse the SYSDBA and SYSOPER database privileges with operating system roles. Operating system roles are a completely independent feature.

 Listing Password File Members

Use the V$PWFILE_USERS view to determine which users have been granted SYSDBA and SYSOPER system privileges for a database. The columns displayed by this view are as follows:

USERNAME	The name of the user that is recognized by the password file.

SYSDBA	If the value of this column is TRUE, the user can log on with SYSDBA system privileges.

SYSOPER	If the value of this column is TRUE, the user can log on with SYSOPER system privileges.

 ________________________________________

Connecting with SYSOPER or SYSDBA Privileges

Oracle7 release 7.1 introduces a new syntax for privileged database connections that replaces CONNECT INTERNAL. Because INTERNAL is an authorization and not an actual user, you are no longer considered to be connecting as INTERNAL; rather, you are allowed to connect as a particular user with either the SYSOPER or SYSDBA system privilege. The syntax is shown in the following syntax diagram.

When you connect with SYSOPER or SYSDBA privileges using a username and password, you are connecting with a default schema of SYS, not the schema that is generally associated with your username.

For example, assume user SCOTT has issued the following commands:

CONNECT scott/tiger

CREATE TABLE scott_test(name VARCHAR2(20));

Later, when SCOTT issues these commands:

CONNECT scott/tiger AS SYSDBA

SELECT * FROM scott_test;

he receives an error that SCOTT_TEST does not exist. That is because SCOTT now references the SYS schema by default, whereas the table was created in the SCOTT schema.

 Connecting as INTERNAL

This option is provided for backwards compatibility only. For information on how to use CONNECT INTERNAL, refer to your Oracle7 Server Administrator's Guide.

 Connecting as SYSOPER or SYSDBA

Connecting as SYSOPER or SYSDBA allows you to perform privileged operations, such as instance startup and shutdown.

 Non-secure Remote Connections

To connect to Oracle as a privileged user over a non-secure connection, you must meet the following conditions:

•	The server to which you are connecting must have a password file. The creation and use of password files is described starting on page 5 - 7.

•	You must be granted the SYSOPER or SYSDBA system privilege.

•	You must connect using a username and password.

 Local and Secure Remote Connections

To connect to Oracle as a privileged user over a local or a secure remote connection you must meet either of the following sets of conditions:

•	You can connect using a password file, provided that you meet the criteria outlined for non-secure connections in the previous bulleted list.

•	If the server is not using a password file, or you have not been granted SYSOPER or SYSDBA privileges and are therefore not in the password file, your operating system name must be authenticated for a privileged connection by the operating system. This form of authentication is operating system specific.

Additional Information: Consult your platform-specific Oracle documentationfor details on operating system authentication.

________________________________________

Maintaining a Password File

This section describes how to expand, relocate, and remove the password file, as well as how to avoid changing the state of the password file.

 Expanding the Number of Password File Users

If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file. The following steps outline the procedure for replacing a password file.

1.	Note which users have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.

2.	Shut down the database.

3.	Delete the existing password file.

4.	Follow the instructions for creating a new password file using the ORAPWD utility on page 5 - 7. Be sure to set the ENTRIES parameter to a sufficiently large number.

5.	Follow the instructions for adding users to the password file on page 5 - 10.

 Relocating the Password File

After you have created the password file, you can relocate it as you choose. After relocating the password file, you must reset the appropriate environment variables to the new pathname. If your operating system uses a predefined pathname, you cannot change the password file location

 Removing a Password File

If you determine that you no longer need to use a password file to authenticate users you can simply delete the password file and reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After removing this file, only users who can be authenticated by the operating system can perform database administration operations.

 Changing the Password File State

The password file state is stored in the password file. When you first create a password file, its default state is SHARED. You can change the state of the password file by setting the parameter REMOTE_LOGIN_PASSWORDFILE. When you STARTUP an instance, Oracle retrieves the value of this parameter from the initialization parameter file stored on your client machine. When you mount the database, Oracle compares the value of this parameter to the value stored in the password file. If these values do not match, the value stored in the file is overwritten.

Warning: You should use caution to ensure that an EXCLUSIVE password file is not accidentally changed to SHARED. If you plan to allow instance STARTUP from multiple clients, each of those clients must have an initialization parameter file, and the value of the parameter REMOTE_LOGIN_PASSWORDFILE must be the same in each of these files. Otherwise, the state of the password file could change depending upon where the instance was started.


CHAPTER 6. PARALLEL QUERY OPTION

This chapter describes the Oracle Server parallel query option, a new optional component introduced with release 7.1. The parallel query option contains these features:

•	parallel query processing

•	parallel index creation

•	parallel data loading

The information in this chapter applies only to the Oracle Server release 7.1 with the parallel query option.

Note: This option is not the same as the Parallel Server option of the Oracle Server. The Parallel Server option is not required to use this feature. However, some aspects of the parallel query option apply only to an Oracle Parallel Server.

 ________________________________________

Parallel Query Processing

In previous releases of the Oracle Server, the processing of a SQL statement was always performed by a single server process. With the parallel query option of release 7.1, multiple processes can work together simultaneously to process a single SQL statement. This capability is called parallel query processing. By dividing the work necessary to process a statement among multiple server processes, the Oracle Server can process the statement more quickly than if only a single server process processed it.

The parallel query option can dramatically improve performance for data-intensive operations associated with decision support applications or very large database environments. Symmetric multiprocessing (SMP), clustered, or massively parallel systems gain the largest performance benefits from the parallel query option because query processing can be effectively split up among many CPUs on a single system.

It is important to note that the query is parallelized dynamically at execution time. Thus, if the distribution or location of the data changes, Oracle automatically adapts to optimize the parallelization for each execution of a SQL statement.

The parallel query option helps systems scale in performance when adding hardware resources. If your system's CPUs and disk controllers are already heavily loaded, you need to alleviate the system's load before using the parallel data option to improve performance. The section "Tuning for the Parallel Query Option" on page 6 - 18 describes how your system can achieve the best performance with the parallel query option.

The Oracle Server can use parallel query processing for any of these statements:

•	SELECT statements

•	subqueries in UPDATE and DELETE statements

•	subqueries in INSERT and CREATE TABLE statements

 Parallel Query Process Architecture

Before release 7.1 of the Oracle Server and without the parallel query option, a server process performs all necessary processing for the execution of a SQL statement. For example, to perform a full table scan (e.g., SELECT * FROM EMP), one process performed the entire operation. Figure 6 - 1 illustrates a server process performing a full table scan:

Figure 6 - 1. Full table scan without the parallel query option

The parallel query option of Oracle Server release 7.1 allows certain operations (for example, full table scans or sorts) to be performed in parallel by multiple query server processes. One process, known as the query coordinator, dispatches the execution of a statement to several query servers and coordinates the results from all of the servers to send the results back to the user. Figure 6 - 2 illustrates several query server processes simultaneously performing a partial scan of the EMP table. The results are then sent back to the query coordinator which assembles the pieces into the desired full table scan.

Figure 6 - 2. Multiple query servers performing a full table scan in parallel

The query coordinator process is very similar to the server processes in previous releases of the Oracle Server. The difference is that the query coordinator can break down execution functions into parallel pieces and then integrate the partial results produced by the query servers. Query servers get assigned to each operation in a SQL statement (for example, a table scan, or a sort operation), and the number of query servers assigned to a single operation is the degree of parallelism for a query.

The query coordinator calls upon the query servers during the execution of the SQL statement (not during the parsing of the statement). Therefore, when using the parallel query option with the multi-threaded server, the server processing the EXECUTE call of a user's statement becomes the query coordinator for the statement.

 ________________________________________

Parallelizing SQL Statements

When a statement is parsed, the optimizer determines the execution plan of a statement. Optimization is discussed in Chapter 13 "The Optimizer" of the Oracle7 Server Concepts Manual. After the optimizer determines the execution plan of a statement, the query coordinator process determines the parallelization method of the statement. Parallelization is the process by which the query coordinator determines which operations can be performed in parallel and then enlists query server processes to execute the statement. This section tells you how the query coordinator process decides to parallelize a statement and how the user can specify how many query server processes can be assigned to each operation in an execution plan (that is, the degree of parallelism).

To decide how to parallelize a statement, the query coordinator process must decide whether to enlist query server processes and, if so, how many query server processes to enlist. When making these decisions, the query coordinator uses information specified in hints of a query, the table's definition, and initialization parameters. The precedence for selecting the degree of parallelism is described later in this section. It is important to note, the optimizer attempts to parallelize a query only if it contains at least one full table scan operation.

Each query undergoes an optimization and parallelization process when it is parsed. Therefore, when the data changes, if a more optimal execution plan or parallelization plan becomes available, Oracle can automatically adapt to the new situation.

 Parallelizing  Operations

Before enlisting query server processes, the query coordinator process examines the operations in the execution plan to determine whether the individual operations can be parallelized. The Oracle Server can parallelize these operations:

•	AGGREGATE (GROUP BY)

•	MERGE JOIN

•	NESTED LOOPS

•	SORT (GROUP BY)

•	SORT (JOIN)

•	SORT (ORDER BY)

•	SORT (UNIQUE)

•	TABLE ACCESS (FULL)

For a description of these and all operations, see Appendix B "Performance Diagnostic Tools" of the Oracle7 Server Application Developer's Guide.

 Partitioning Rows to Each Query Server

The query coordinator process also examines the partitioning requirements of each operation. An operation's partitioning requirement is the way in which the rows operated on by the operation must be divided, or partitioned, among the query server processes. For example:

•	The rows operated on by all sort operations and the MERGE JOIN operation must be partitioned so that each query server process operates on rows with the same values for the columns in the sort key. The distribution of sort keys among the query servers is determined by an estimation based on a parallelized sampling operation. Figure 6 - 4 illustrates the partitioning of a sort operation.

•	The rows operated on by the TABLE ACCESS (FULL) operation, also called a full table scan, as well as the NESTED LOOPS join are partitioned based on ranges of contiguous blocks in the table. The table is divided into several groups of contiguous blocks, each group being of a small, medium, or large number of blocks. Query servers are given successively smaller groups of blocks as a query server completes a scan or nested loops join. This way, each query server completes as much work as possible, and all query servers complete an operation at approximately the same time.

After determining the partitioning requirement for each operation in the execution plan, the query coordinator determines the order in which the operations must be performed. With this information, the query coordinator determines the data flow of the statement. Figure 6 - 3 illustrates the data flow of the following query:

SELECT dname, MAX(sal), AVG(sal)

FROM emp, dept

WHERE emp.deptno = dept.deptno

GROUP BY dname;

Figure 6 - 3. Data flow diagram for a join of the EMP and DEPT tables

Operations that require the output of other operations are known as parent operations. In Figure 6 - 3 the GROUP BY SORT operation is the parent of the MERGE JOIN operation because GROUP BY SORT requires the MERGE JOIN output.

 Parallelism Between Operations

Parent operations can begin processing rows as soon as the child operations have produced rows for the parent operation to consume. In the previous example, while the query servers are producing rows in the FULL SCAN DEPT operation, another set of query servers can begin to perform the MERGE JOIN operation to consume the rows. When the FULL SCAN DEPT operation is complete, the FULL SCAN EMP operation can begin to produce rows.

Each operation performed concurrently is given its own set of query server processes. Therefore, both query operations and the data flow tree itself have degrees of parallelism. The degree of parallelism of an individual operation is called inter-operation parallelism and the degree of parallelism between operations in a data flow tree is called intra-operation parallelism.

Due to the producer/consumer nature of the Oracle Server's query operations, only two operations in a given tree need to be performed simultaneously to minimize execution time. If additional sets of query servers were allocated to each operation in a data flow tree, sets higher in the tree may be idle for a period of time while waiting for the lower sets to produce rows for consumption.

To illustrate inter-operation parallelism and intra-operator parallelism, consider the following statement:

SELECT * FROM emp ORDER BY ename;

The execution plan consists of a full scan of the EMP table followed by a sorting of the retrieved rows based on the value of the ENAME column. For the sake of this example, assume the ENAME column is not indexed. Also assume that the degree of parallelism for the query is set to four, which means that four query servers can be active for any given operation. Figure 6 - 4 illustrates the parallel execution of our example query.

Figure 6 - 4. Inter-Operator Parallelism and Dynamic Partitioning

As you can see from Figure 6 - 4, there are actually eight query servers involved in the query even though the degree of parallelism is four. This is because a parent and child operator can be performed at the same time. Also note that all of the query servers involved in the scan operation send rows to the appropriate query server performing the sort operation. If a row scanned by a query server contains an value for the ENAME column between A and G, that row gets sent to the first ORDER BY query server. When the scan operation is complete, the sorting query servers can return the sorted results to the query coordinator which in turn returns the complete query results to the user.

Note: When a set of query servers completes its operation, it moves on to operations higher in the data flow. For example, in the previous diagram, if there was a GROUP BY operation after the ORDER BY, the query servers performing the table scan perform the GROUP BY operation after completing the table scan.

 ________________________________________

Setting the Degree of Parallelism

The query coordinator process may enlist two or more of the instance's query server processes to process the statement. The number of query server processes associated with a single operation is known as the degree of parallelism. The degree of parallelism is specified at the query level (with hints), at the table level (in the table's definition), or by default in the initialization parameter file. Note that the degree of parallelism applies only to the inter-operation parallelism. If intra-operation parallelism is possible, the total number of query servers can be twice the specified degree of parallelism.

 Determining the Degree of Parallelism for Operations

The query coordinator determines the degree of parallelism by considering three specifications. The query coordinator first checks for query hints, then looks at the table's definition, and finally checks initialization parameters for the instance for the default degree of parallelism. Once a degree of parallelism is found in one of these specifications, it becomes the degree of parallelism for the query.

For queries involving more than one table, the query coordinator requests the greatest number specified for any table in the query. For example, on a query joining the EMP and DEPT tables, if EMP's degree of parallelism is specified as 5 and DEPT's degree of parallelism is specified as 6, the query coordinator would request six query servers for each operation in the query.

Keep in mind that no more than two operations can be performed simultaneously. Therefore, the maximum number of query servers requested for any query can be up to twice the degree of parallelism.

Hints, the table definitions, or initialization parameters only determine the number of query servers that the query coordinator requests for a given operation. The actual number of query servers used depends upon how many query servers are available in the query server pool and whether intra-operation parallelism is possible.

 Hints

Four new hints allow you to set the degree of parallelism for a table in a query and the caching behavior of the query. Refer to the Oracle7 Server Application Developer's Guide for a general discussion on using hints in queries. The new hints are described in the following sections.

 PARALLEL

The PARALLEL hint allows you to specify the desired number of concurrent query servers that can be used for the query. The syntax is

The PARALLEL hint (as with all other hints) must use the table alias if an alias is specified in the query. The PARALLEL hint can then take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table, the second value specifies how the table is to be split among the instances of a Parallel Server. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism.

In the following example, the PARALLEL hint overrides the degree of parallelism specified in the EMP table definition.

SELECT /*+ FULL(scott_emp) PARALLEL(scott_emp, 5) */

ename

FROM scott.emp scott_emp;

In the next example, the PARALLEL hint overrides the degree of parallelism specified in the EMP table definition and tells the optimizer to use the default degree of parallelism determined by the initialization parameters. This hint also specifies that the table should be split among all of the available instances with the default degree of parallelism on each instance.

SELECT /*+ FULL(scott_emp) PARALLEL(scott_emp, DEFAULT,DEFAULT) */

ename

FROM scott.emp scott_emp;

 NOPARALLEL

The NOPARALLEL hint allows you to disable parallel scanning of a table, even if the table was created with a PARALLEL clause. The following example illustrates the NOPARALLEL hint:

SELECT /*+ NOPARALLEL(scott_emp) */

ename

FROM scott.emp scott_emp;

The NOPARALLEL hint is equivalent to specifiying the hint  /*+ PARALLEL(table,1,1) */.

 Table and Cluster Definition Syntax

You can specify the degree of parallelism within a table definition. The syntax for specifying the degree of parallelism in the CREATE TABLE statement is illustrated below.

PARALLEL	Specifies the parallel characteristics of the table. See the descriptions of DEGREE and INSTANCES for more information.

DEGREE	An integer specifies the default degree of parallelism for the table.

DEFAULT specifies the degree of parallelism is calculated from an estimate of the size of the table and the value of the initialization parameter PARALLEL_DEFAULT_SCANSIZE.

INSTANCES	Specifies how a table is split among the available instances of a Parallel Server for parallel query processing. An integer signifies that the table is to be split among the specified number of available instances. DEFAULT specifies that the table is to be split among all availible instances.

The number of query servers specified with the DEGREE keyword is the degree of parallelism for each instance.

You can limit the number of available instances for parallel query processing by using the ALTER SYSTEM command or with the parameter PARALLEL_DEFAULT_MAX_INSTANCES. See the section "Limiting the Number of Available Instances" on page 6 - 16.

INSTANCES only applies to an instance using the Oracle Parallel Server.

NOPARALLEL	Specifies that queries on this table are not performed in parallel by default. A hint in the query can still cause the query to be performed in parallel.

Note that a specification of PARALLEL(DEGREE 1 INSTANCES 1) is equivalent to specifying NOPARALLEL.

CACHE	Specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. See Chapter 9 "Miscellaneous Additions" for a more complete description of the CACHE clause.

If the INSTANCES keyword is specified along with the CACHE keyword, and if the size of a partition on each instance is greater than the value of the parameter CACHE_SIZE_THRESHOLD, the table is not cached among the instances.

NOCACHE	Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the default behavior. See Chapter 9 "Miscellaneous Additions" for a more complete description of the CACHE clause.

You can also alter an existing table to specify a degree of parallelism. The syntax of the ALTER TABLE statement is illustrated below. The keywords for the ALTER TABLE command have the same description as the keywords for the CREATE TABLE command.

Clustered tables can also have a parallel, cache, or partition specification. The specification for a clustered table is derived from the cluster's definition. The CREATE CLUSTER and ALTER CLUSTER command have a similar syntax to the CREATE and ALTER TABLE commands. The syntax for cluster commands is as follows:

 Default Degree of Parallelism

The default degree of parallelism is always determined by two initialization parameters. First, Oracle estimates the number of blocks in the table (based on statistics in the data dictionary) and divides that number by the value of the initialization parameter PARALLEL_DEFAULT_SCANSIZE. Next, you can limit the number of query servers to use by default by setting the initialization parameter PARALLEL_DEFAULT_MAX_SCANS. The smaller value of these two methods is used as the default degree of parallelism.

For example, if you have a table with 50,000 blocks and the parameter PARALLEL_DEFAULT_SCANSIZE is set to 1000, the default degree of parallelism is 50 (this also implies that queries on tables with no fewer than 2000 blocks will be parallelized by default). If you have a limited resources to devote to parallel query processing, you might want to limit the default degree of parallelism by setting the parameter PARALLEL_DEFAULT_MAX_SCANS. In this example, if PARALLEL_DEFAULT_MAX_SCANS is set to 25, then the default degree of parallelism would be 25.

 Limiting the Number of Available Instances

The INSTANCES keyword of the CREATE/ALTER TABLE/CLUSTER commands allows you to specify that a table or cluster is split up among the buffer caches of all available instances of an Oracle Parallel Server. If you do not want tables to be dynamically partitioned among all the available instances, you can specify the number of instances that can participate in scanning or caching with the parameter PARALLEL_DEFAULT_MAX_INSTANCES or the ALTER SYSTEM command.

If you want to specify the number of instances to participate in parallel query processing at startup time, you can specify a value for the initialization parameter PARALLEL_DEFAULT_MAX_INSTANCES. See Appendix A "Reference" for more information about this parameter.

If you want to limit the number of instances available for parallel query processing dynamically, use the ALTER SYSTEM command. For example, if you have ten instances running in your Parallel Server, but you want only eight to be involved in parallel query processing while the other two instances should be dedicated to other use, you can specify a value for issue the following command:

ALTER SYSTEM SET SCAN_INSTANCES = 8;

Therefore, if a table's definition has a value of ten specified in the INSTANCES keyword, the table will be scanned by query servers on eight of the ten instances. Oracle selects the first eight instances in this example. Set the parameter PARALLEL_MAX_SERVERS to zero on the instances that you do no want to participate in parallel query processing.

If you wish to limit the number of instances which cache a table, you can issue the following command:

ALTER SYSTEM SET CACHE_INSTANCES = 8;

Therefore, if a table specifies the CACHE keyword with the INSTANCES keyword specified as 10, it will divided evenly among eight of the ten available instances' buffer caches.

 ________________________________________

Managing the Query Servers

When you start your instance, the Oracle Server creates a pool of query server processes available for any query coordinator. The number of query server processes that the Oracle Server creates at instance startup is specified by the initialization parameter PARALLEL_MIN_SERVERS.

Query server processes remain associated with a statement throughout its execution phase. When the statement is completely processed, its query server processes become available to process other statements. The query coordinator process returns any resulting data to the user process issuing the statement.

 Varying Pool of Query Server Processes

If the volume of SQL statements processed concurrently by your instance changes drastically, the Oracle Server automatically changes the number of query server processes in the pool to accommodate this volume.

•	If this volume increases, the Oracle Server automatically creates additional query server processes to handle incoming statements. The maximum number of query server processes for your instance is specified by the initialization parameter PARALLEL_MAX_SERVERS.

•	If this volume subsequently decreases, the Oracle Server terminates a query server process if it has been idle for the period of time specified by the initialization parameter PARALLEL_SERVER_IDLE_TIME. The Oracle Server does not reduce the size of the pool below the value of PARALLEL_MIN_SERVERS regardless of how long the query server processes have been idle.

If all query servers in the pool are occupied and the maximum number of query servers has been started, a query coordinator processes the statement sequentially. "Tuning the Query Servers" on page 6 - 21 describes how to monitor an instance's pool of query servers and determine the appropriate values of the initialization parameters.

 ________________________________________

Tuning for the Parallel Query Option

Four questions are very important in tuning when using the parallel query option:

•	what systems are appropriate for the parallel query option?

•	how does I/O and file striping relate to the parallel query option?

•	how should I set the degree of parallelism?

•	how many query server processes do I need on my system?

This section details some of the factors to consider when answering the above questions. This section also details the EXPLAIN PLAN facility for examining a query's execution plan and parallelization scheme. Some factors involved in determining the answers to the questions above are operating system dependent.

Additional Information:  Refer to your platform specific Oracle documentation for more information about tuning while using the parallel query option.

 What Systems Benefit?

The parallel query option can help the most on the following systems:

•	symmetric multiprocessors (SMP) or massively parallel systems

•	systems with high I/O bandwith (that is, many datafiles on many different disk drives)

•	systems with under-utilized or intermittently-used CPUs (for example, systems where CPU usage is typically less than 30%)

•	systems with sufficient memory to devote to sorting space for queries

If any one of these conditions is not true for your system, the parallel query option may not significantly help performance. In fact, on over-utilized systems or systems with small I/O bandwith, the parallel query option can impede system performance.

It is important to realize that Oracle cannot return results to a user process in parallel. Therefore, if a query returns a large number of rows, the execution of the query will be faster, but the user process receives the rows serially. Thus, the parallel query option is best for batch processing of queries, or computing results based on a large number of records.

The parallel query option does not currently parallelize data definition language (such as CREATE TABLE) or INSERT, UPDATE, and DELETE statements. Scans or sorts of a table performed in these statements (for example, CREATE TABLE AS SELECT..., or DELETE FROM EMP WHERE ...) can be parallelized, but the actual DDL or DML operation cannot.

 What Are the I/O Considerations?

The parallel query option works best on files that are spread or striped across many disk drives. This allows the query servers to maximize concurrent access to the datafiles.

Automatic file striping by the operating system is nearly as good as manually striping your tables across datafiles, so take advantage of automatic file striping if supported in your operating system. Operating system striping is usually easy to configure and provides automatic I/O balancing. Operating striping is also optimal for random I/O operations such as sorts using temporary segments. Manual table striping is optimal for sequential I/O and may improve the performance for table scans.

File stripe size should be a few multiples larger than the size of DB_FILE_MULTIBLOCK_READ_COUNT blocks (the amount of blocks that Oracle attempts to read with a single I/O).

Additional Information:  For a discussion of manually striping tables across datafiles refer to Chapter 22 of the Oracle7 Server Administrator's Guide. For more information about automatic file striping and tools to use to determine I/O distribution among your devices, refer to your operating system documentation.

It is also important to note that large contiguous extents can help the query coordinator break up scan operations more efficiently for the query servers. During a scan operation, the query coordinator identifies all of the contiguous ranges of blocks and then breaks up these ranges into large, medium, and small groups of blocks. Each query server is given a large group of contiguous blocks to begin with and then is given successively smaller groups of blocks until the scan is completed. This ensures that all of the query servers complete the scan at approximately the same time. If there are several large extents in a table, the query coordinator can easily find groups of contiguous blocks to dispatch to the query servers.

The temporary tablespace used for sorting should also be striped across several disks (by the operating system, if possible). This tablespace should also contain several large extents.

 How Should I Set the Degree of Parallelism?

After handling the I/O distribution, the degree of parallelism is the most important factor in tuning the parallel query option. You must first determine the maximum number of query servers your system can support and then divide those query servers among the estimated number of concurrent queries. The maximum number of query servers your system can support depends upon combinations of the following factors:

•	the number and capacity of CPUs on your system

•	the limitations on number of processes on your system

•	if tables are striped, the number of disk drives the table is striped across

•	the amount of query processing on your system

•	the location of the data (that is, whether it is cached or on disk)

•	the type of operations in a query (for example, sorts or full table scans)

While some of these factors are operating system dependent, most of these factors are dependent upon the characteristics of your specific system and the type of operations involved in each query. Therefore, it is difficult to make generic recommendations for the degree of parallelism. It is often best to pick a degree of parallelism for a query, determine the execution time, and then determine the execution time of several higher and lower degrees of parallelism to find the optimal number.

Additional Information:  To determine whether your system is being fully utilized, there are several graphical system monitors available on most operating systems. These monitors often give you a better idea of CPU utilization and system performance than monitoring the execution time of a query. Consult your operating system documentation to determine if your system supports graphical system monitors.

For single-user parallel execution, one guideline for your initial estimate of the degree of parallelism is to use one or two times the number of CPUs on your system (depending on the capacity of your CPUs). This is a good guess for sort-intensive operations because those tend to be CPU-bound. If your query is I/O intensive (that is, more scanning of tables than sorting), you may want to use one or two times the number of disk drives involved in the scans as your initial guess at the degree of parallelism.

If you have several concurrent queries that all require parallel execution, you should consider adding CPUs and/or disk drives to your system to allow for a greater number of query servers per query. If you cannot expand your system to accommodate multiple users, you may need to consider limiting parallel query execution to fewer users or lowering the degree of parallelism for each user's query.

 Memory Requirements for Sort Operations

The SORT_AREA_SIZE initialization parameter specifies the amount of memory to allocate per query server for sort operations. Setting this parameter to a larger value can dramatically increase the performance of sort operations since the entire sort is more likely to be performed in memory. Keep in mind that the actual amount of memory used for a sort is value of SORT_AREA_SIZE multiplied by the number of query servers (for example, 10 query servers each with 1M for sorts requires 10M of memory allocated for the sort).

If memory is abundant on your system, you can benefit from setting SORT_AREA_SIZE to a large value. However, if memory is a concern for your system, you may want to limit the amount of memory allocated for sorts and increase the size of the buffer cache so that data blocks from temporary sort segments can be cached in the buffer cache.

 Tuning the Query Servers

The V$PQ_SYSSTAT view contains statistics which are useful for determining the appropriate number of query server processes for an instance. The statistics which are particularly useful are "Servers Busy", "Servers Idle", "Servers Started", and "Servers Shutdown".

Frequently you will not be able to increase the maximum number of query servers for an instance because the maximum number is heavily dependent upon the capacity of your CPUs and your I/O bandwith. However, if servers are continuously starting and shutting down, you should consider increasing the value of the parameter PARALLEL_MIN_SERVERS.

For example, if you have determined that the maximum number of concurrent query servers that your machine can manage is 100, you should set PARALLEL_MAX_SERVERS to 100. Next determine how many query servers the average query needs, and how many queries are likely to be executed concurrently. For this example, let's assume you'll have two concurrent queries with 20 as the average degree of parallelism. Thus, at any given point in time, there could be 80 query servers busy on an instance. Thus you should set the parameter PARALLEL_MIN_SERVERS to be 80.

Now you should periodically examine V$PQ_SYSSTAT to determine if the 80 query servers for the instance are actually busy. To determine if the instance's query servers are active, issue the following query:

SELECT * FROM V$PQ_SYSSTAT

WHERE statistic = "Servers Busy";

STATISTIC             VALUE

--------------------- -----------

Servers Busy          70

If you find that there are typically fewer than PARALLEL_MIN_SERVERS busy at any given time, your idle query servers are additional system overhead that is not being used. You should then consider decreasing the value of the parameter PARALLEL_MIN_SERVERS. If you find that there are typically more query servers active than the value of PARALLEL_MIN_SERVERS and the "Servers Started" statistic is continuously growing, then you should consider increasing the value of the parameter PARALLEL_MIN_SERVERS.

 EXPLAIN PLAN

The EXPLAIN PLAN facility details a query's execution plan and how a query is parallelized.

The OBJECT_NODE and OTHER columns of the PLAN_TABLE describe the parallelism (if any) of a given query. For non-distributed queries, the OBJECT_NODE column describes the order that the output from operations is consumed. The OTHER column describes the text of the query that is used by query servers for each operation. For example, in the following query:

EXPLAIN PLAN FOR

SELECT dname, MAX(sal), AVG(sal)

FROM emp, dept

WHERE emp.deptno = dept.deptno

GROUP BY dname;

the contents of the plan table may look something like this:

QUERY PLAN            OBJECT_NODE   OTHER

--------------------- ------------- --------------------------

GROUP BY SORT         :Q704003      select c0, avg(c1), max(c1) from

                                       :Q704002 group by c0

    MERGE JOIN        :Q704002      select /*+ ordered use_merge(a2) */

                                       a2.c1 c0, a1.c1 c1 from

                                       :Q704001 a1, :Q704000 a2

                                       where a1.c0 = a2.c0

      JOIN SORT       :Q704002     operation combined with parent

         FULL SCAN    :Q704001     select /*+ rowid(a1) */ a1.deptno c0,

                                      a1.sal c1 where rowid between :1

                                      and :2

      JOIN SORT       :Q704002     operation combined with parent

         FULL SCAN    :Q704000     output consumed in parallel

From this output you can construct the data flow diagram for the query.

Figure 6 - 5.  Data Flow Diagram with OBJECT_NODE Information

 Combining Operations within a Tree

The EXPLAIN PLAN output for the preceding example join query describes that the MERGE JOIN operation also requires a JOIN SORT operation on both tables involved in the join. Because the MERGE JOIN operation requires the information from both JOIN SORT operations, the MERGE JOIN is considered the parent operation of the JOIN SORT. The query coordinator process compares the partitioning requirement of each operator with that of its parent.

•	If they are compatible, the operators can be combined and performed together as a single operator.

•	If they are incompatible, the query coordinator repartitions the rows returned by the operator so that the rows meet the partitioning requirements of its parent operator. The rows are then sent from the operator to its parent.

In the example above, the two JOIN SORT operations are compatible with their parent operation and therefore are combined into the MERGE JOIN. All other operations require communication of the results to the parent operation.

 ________________________________________

Parallel Index Creation

Multiple processes can work together simultaneously to create an index. By dividing the work necessary to create an index among multiple server processes, the Oracle Server can create the index more quickly than if a single server process created the index sequentially.

Parallel index creation works in much the same way as parallel execution of queries. One set of query servers scans the table to be indexed to obtain the rowids and column values for the rows. Then another set of query servers performs the sorting of the index entries based on the index column values and passes off the sorted entries to the coordinator process which builds the B*-tree index from the sorted lists.

By default, the Oracle Server uses the table definition's PARALLEL clause value to determine the number of server processes to use when creating an index. You can override the default number of processes by using the PARALLEL clause in the CREATE INDEX command. The syntax is illustrated in the next section.

See the syntax of the CREATE TABLE command on page 6 - 10 for a description of the keywords in the PARALLEL clause.

When creating an index in parallel, the STORAGE clause refers to the storage of each of the subindexes created by the query server processes. Therefore, an index created with an INITIAL of 5M and a PARALLEL DEGREE of 12 consumes at least 60M of storage during index creation because each process starts with an extent of 5M. When the query coordinator process combines the sorted subindexes, some of the extents may be trimmed, and the resulting index may be smaller than the requested 60M.

When you add or enable a UNIQUE key or PRIMARY KEY constraint on a table, you cannot automatically create the required index in parallel. Instead, manually create an index on the desired columns using the CREATE INDEX command and an appropriate PARALLEL clause and then add or enable the constraint. Oracle then uses the existing index when enabling or adding the constraint.

Refer to "Tuning for the Parallel Query Option" on page 6 - 18 for advice on tuning your system when creating indexes in parallel.

 ________________________________________

Parallel Data Loading

SQL*Loader now permits multiple concurrent sessions to perform a direct path load into the same table. Multiple SQL*Loader sessions improve the performance of a direct path load given the available resources on your system.

Previously, a direct path load caused an exclusive lock to be obtained on a table during the load, thus only one direct path load could be performed on any given table. With the Oracle Server release 7.1, you can optionally specify a PARALLEL clause within the SQL*Loader control file or on the command line to indicate that multiple SQL*Loader direct path load sessions can share access to the table.

 Initiating Multiple SQL*Loader Sessions

Each SQL*Loader session takes a different source file as input. In all sessions executing a direct load on the same table, you must specify the PARALLEL clause on the command line. The syntax of the PARALLEL clause is:

The complete syntax for the SQL*Loader control file is listed in the Oracle7 Server Utilities User's Guide.

For example, to invoke three SQL*Loader direct path load sessions on the same table, you would execute the following commands at the operating system prompt:

SQLLOAD USERID=SCOTT/TIGER CONTROL=LOAD1.CTL DIRECT=TRUE PARALLEL=TRUE

SQLLOAD USERID=SCOTT/TIGER CONTROL=LOAD2.CTL DIRECT=TRUE PARALLEL=TRUE

SQLLOAD USERID=SCOTT/TIGER CONTROL=LOAD3.CTL DIRECT=TRUE PARALLEL=TRUE

The previous commands must be executed in separate sessions, or if permitted on your operating system, as separate background jobs. Note the use of multiple control files. This allows you to be flexible in specifying the files to use for the direct path load (see the example of one of the control files below).

Note: Indexes are not created during a parallel load. Any indexes must be created manually after the load completes. You can use the parallel index creation feature to speed the creation of large indexes after a parallel load.

When you use the PARALLEL clause, SQL*Loader creates temporary segments for each concurrent session and then merges the segments upon completion. The segment created from the merge is then added to the existing table in the database above the table's high water mark. The last extent used for each loader session is trimmed of any free space before being combined with the other extents of the SQL*Loader session. For a detailed description of direct path load with SQL*Loader, see the Oracle7 Server Utilities User's Guide.

You can specify the database file from which the temporary segments are allocated with the FILE keyword in the OPTIONS clause for each table in the control file. You can also specify the FILE parameter on the command line of each concurrent SQL*Loader session, but then it will globally apply to all tables being loaded with that session.

It is recommended that each concurrent session use files located on different disks to allow for the maximum I/O throughput. You can specify the filename of any valid datafile in the table's tablespace with the FILE keyword of the OPTIONS clause. The following example illustrates a portion of one of the control files used for the SQL*Loader sessions in the previous example:

LOAD DATA

INFILE 'load1.dat'

INSERT INTO TABLE emp

OPTIONS(FILE='/dat/data1.dat')

(empno POSITION(01:04) INTEGER EXTERNAL NULLIF empno=BLANKS

...

 Enabling Constraints After A Parallel Direct Path Load

Because each SQL*Loader session can attempt to re-enable constraints on a table after a direct path load, there is a danger that one session attempts to re-enable a constraint before another session is finished loading data. In this case, the first session to complete the load will be unable to enable the constraint because the remaining sessions possess share locks on the table.

Because there is a danger that some constraints may not be re-enabled after a direct path load, you should check the status of the constraint after completing the load to ensure that it was enabled properly.

 PRIMARY and UNIQUE KEY constraints

PRIMARY KEY and UNIQUE key constraints create indexes on a table when they are enabled, and subsequently can take a significantly long time to enable after a direct path loading session if the table is very large.

You should consider enabling these constraints manually after a load (and not specify the automatic enable feature). This allows you to manually create the required indexes in parallel to save time before enabling the constraint. See the section "Parallel Index Creation" on page 6 - 24 for more information about creating indexes in parallel.


CHAPTER 7. DYNAMIC SQL

This chapter describes the dynamic SQL package, DBMS_SQL. The following topics are described in this chapter:

•	The differences between the DBMS_SQL package and the Oracle Call Interfaces.

•	Using the DBMS_SQL package to parse DDL.

•	Using the DBMS_SQL package to perform dynamic SQL.

•	The procedures and functions provided in the DBMS_SQL package.

 ________________________________________

Overview

You can write stored procedures and anonymous PL/SQL blocks using dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime.

This permits you to create procedures that are more general purpose. For example, using dynamic SQL allows you to create a procedure that operates on a table whose name is not known until runtime.

Additionally, you can parse any data manipulation language (DML) or data definition language (DDL) statement using the DBMS_SQL package. This helps solve the problem of not being able to parse data definition language statements directly using PL/SQL. For example, you might now choose to issue a DROP TABLE statement from within a stored procedure by using the PARSE procedure supplied with the DBMS_SQL package.

 ________________________________________

Using DBMS_SQL

The ability to use dynamic SQL from within stored procedures generally follows the model of the Oracle Call Interfaces (OCIs). You should refer to the Programmer's Guide to the Oracle Call Interfaces for additional information on the concepts presented in this chapter.

PL/SQL differs somewhat from other common programming languages, such as C. For example, addresses (also called pointers) are not user visible in PL/SQL. As a result, there are some differences between the Oracle Call Interfaces and the DBMS_SQL package. These differences include the following:

•	The OCIs use bind by address, whereas the DBMS_SQL package uses bind by value.

•	With DBMS_SQL you must call VARIABLE_VALUE to retrieve the value of an OUT parameter for an anonymous block, and you must call COLUMN_VALUE after fetching rows to actually retrieve the values of the columns in the rows into your program.

•	The current release of the DBMS_SQL package does not provide DESCRIBE or CANCEL procedures, nor support for the array interface.

•	Indicator variables are not required because nulls are fully supported as values of a PL/SQL variable.

A sample usage of the DBMS_SQL package is shown below. For users of the Oracle Call Interfaces, this code should seem fairly straightforward. Each of the functions and procedures used in this example is described later in this chapter. A more detailed example, which shows how you can use the DBMS_SQL package to build a query statement dynamically, begins on page 7 - 16. This example does not actually require the use of dynamic SQL, because the text of the statement is known at compile time. However, it illustrates the concepts of this package.

/*

 * The DEMO procedure deletes all of the employees from the EMP

 * table whose salaries are greater than the salary that you

 * specify when you run DEMO.

 */

CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS

    cursor INTEGER;

    rows_processed INTEGER;

BEGIN

    cursor := dbms_sql.open_cursor;

    dbms_sql.parse(cursor, 'DELETE FROM emp WHERE sal > :x',

                   dbms_sql.v7);

    dbms_sql.bind_variable(cursor, ':x', salary);

    rows_processed := dbms_sql.execute(cursor);

    dbms_sql.close_cursor(cursor);

EXCEPTION

WHEN OTHERS THEN

    dbms_sql.close_cursor(cursor);

END

 ________________________________________

Execution Flow

The typical flow of procedure calls is shown in Figure 7 - 1. A general explanation of these procedures follows. Each of these procedures is described in greater detail starting on page 7 - 6.

 OPEN_CURSOR

To process a SQL statement, you must have an open cursor. When you call the OPEN_CURSOR function, you receive a cursor ID number for the data structure representing a valid cursor maintained by Oracle. These cursors are distinct from cursors defined at the precompiler, OCI or PL/SQL level, and are used only by the DBMS_SQL package.

Figure 7 - 1. DBMS_SQL Execution Flow

 PARSE

Every SQL statement must be parsed by calling the PARSE procedure. Parsing the statement checks the statement's syntax and associates it with the cursor in your program. A complete explanation of how SQL statements are parsed is included in the Oracle7 Server Application Developer's Guide.

You can parse any data manipulation language or data definition language statements. Data definition language statements are executed on the parse, which performs the implied commit.

Note: When parsing a data definition language statement to drop a package, a deadlock can occur if a procedure in the package is still in use by you. After a call to a procedure, that procedure is considered to be in use until execution has returned to the user side. Any such deadlock will timeout after five minutes.

 BIND_VARIABLE

Many data manipulation language statements require that data in your program be input to Oracle. When you define a SQL statement that contains input data to be supplied at runtime, you can use placeholders in the SQL statement to mark where data must be supplied.

For each placeholder in the SQL statement, you must call the BIND_VARIABLE procedure to supply the value of a variable in your program to the placeholder. When the SQL statement is subsequently executed, Oracle uses the data that your program has placed in the output and input, or bind, variables.

 DEFINE_COLUMN

The columns of the row being selected in a SELECT statement are identified by their relative positions as they appear in the select list, from left to right. For a query, you must call DEFINE_COLUMN to specify the variables that are to receive the SELECT values, much the way an INTO clause does for a static query.

 EXECUTE

Call the EXECUTE procedure to execute your SQL statement.

 FETCH_ROWS

Call FETCH_ROWS to retrieve the rows that satisfy the query. Each successive fetch retrieves another row, until the fetch is unable to retrieve the specified number of rows. Instead of calling EXECUTE and then FETCH_ROWS, you may find it more efficient to call EXECUTE_AND_FETCH, if you are calling EXECUTE for a single iteration.

 VARIABLE_VALUE

For queries, call COLUMN_VALUE to determine the value of a column retrieved by the FETCH_ROWS call. For anonymous blocks containing calls to PL/SQL procedures, call VARIABLE_VALUE to retrieve the values assigned to the output variables of the PL/SQL procedures when they were executed.

 CLOSE_CURSOR

When you no longer need a cursor for a session, close the cursor by calling CLOSE_CURSOR. If you are using a gateway, you may need to close cursors at other times as well. Consult your gateways documentation for additional information. If you neglect to close a cursor, the memory used by that cursor remains allocated even though it is no longer needed.

 ________________________________________

Security

This section describes the security domain for DBMS_SQL procedures when you are using the Oracle Server or Trusted Oracle Server.

 For Oracle Server Users

Any DBMS_SQL procedures called from an anonymous PL/SQL block are executed using the privileges of the current user. Any DBMS_SQL procedures called from a stored procedure are executed using the privileges of the owner of the stored procedure.

 For Trusted Oracle Server Users

Any DBMS_SQL procedures called from an anonymous PL/SQL block are executed using the privileges of the current user. Any DBMS_SQL procedures called from a stored procedure are executed using the discretionary access control (DAC) and system privileges of the owner of the stored procedure and the mandatory access control (MAC) privileges of the current user.

 ________________________________________

Procedures and Functions

This section explains the syntax and use of each of the procedures and functions associated with the DBMS_SQL package. An example of how these functions can be used begins on page 7 - 16.

In the syntax definitions, square brackets [ ] indicate optional parameters.

 BIND_VARIABLE Procedure

This procedure allows you to bind a given value to a given variable in a cursor, based on the name of the variable in the statement. If the variable is an IN or IN/OUT variable, the given bind value must be valid for the variable type. Bind values for OUT variables are ignored.

The bind variables of a SQL statement are identified by their names. When binding a value to a bind variable, the string identifying the bind variable in the statement must contain a leading colon, as shown in the following example:

SELECT emp_name FROM emp WHERE SAL > :X;

For this example, the corresponding bind call would look similar to:

BIND_VARIABLE(cursor, ':x', 3500);

Syntax

The bind_variable procedure has the following syntax:

procedure bind_variable(

cursor in integer,

name in varchar2,

value in <datatype>)

where <datatype> can be any one of the following types:

number

date

mlslabel

The following syntax is also supported for the bind_variable procedure:

procedure bind_variable(

cursor in integer,

name in varchar2,

value in varchar2 [, out_value_size in integer)

procedure bind_variable_char(

cursor in integer,

name in varchar2,

value in char [, out_value_size in integer)

procedure bind_variable_raw(

cursor in integer,

name in varchar2,

value in raw [,out_value_size in integer])

procedure bind_variable_rowid(

cursor in integer,

name in varchar2,

value in rowid)

Input Parameters

cursor

The ID number of the cursor to which you want to bind a value.

name

The name of the variable in the statement.

value

The value that you want to bind to the variable in the cursor. For IN and IN/OUT variables, the value has the same type as the type of the value being passed in for this parameter.

out_value_size

The maximum expected OUT value size, in bytes, for the VARCHAR2, RAW, and CHAR OUT or IN/OUT variable. If no size is given, the length of the current value is used.

 CLOSE_CURSOR Procedure

This function allows you to close a given cursor.

Syntax

close_cursor(cursor in out integer);

Input Parameter

cursor

The ID number of the cursor that you want to close.

Output Parameter

cursor

The cursor is set to null. After you call CLOSE_CURSOR, the memory allocated to the cursor is released, and you can no longer fetch from that cursor.

 COLUMN_VALUE Procedure

This procedure returns the value of the cursor element for a given position in a given cursor. This procedure is used to access the data fetched by calling FETCH_ROWS.

Syntax

procedure column_value(

cursor in integer,

position in integer,

value out <datatype>,

[, column_error out number]

[, actual_length out integer])

where <datatype> can be any one of the following types:

number

date

mlslabel

varchar2

The following syntax is also supported for the column_value procedure:

procedure column_value_char(

cursor in integer,

position in integer,

value out char

[, column_error out number]

[, actual_length out integer])

procedure column_value_raw(

cursor in integer,

position in integer,

value out raw

[, column_error out number]

[, actual_length out integer])

procedure column_value_rowid(

cursor in integer,

position in integer,

value out rowid

[, column_error out number]

[, actual_length out integer])

Input parameters

cursor

The ID number of the cursor from which you are fetching the values.

position

The relative position of the column in the cursor. The first column in a statement has position 1.

row

The row in which the column value is located. The first row fetched is row 1.

Output parameters

value

The value at the specified column and row. If the row number specified is greater than the total number of rows fetched, you receive an error message.

actual_length

The actual length, before any truncation, of the value in the specified column.

column_error

Any error code for the specified column value.

Exception

inconsistent_type (ORA-06562)

This exception is raised if the type of the output parameter differs from the actual type of the value, as defined by the call to DEFINE_COLUMN.

 DEFINE_COLUMN Procedure

This procedure is only used with SELECT cursors. This procedure defines a column to be selected from the given cursor. The column being defined is identified by its relative position in the SELECT list of the statement in the given cursor. The type of the column value determines the type of the column being defined.

Syntax

procedure define_column(

cursor in integer,

position in integer

column in <datatype>)

where <datatype> can be any one of the following types:

number

date

mlslabel

The following syntax is also supported for the define_column procedure:

procedure define_column(

cursor in integer,

position in integer,

column in varchar2,

column_size in integer)

procedure define_column_char(

cursor in integer,

position in integer,

column in char,

column_size in integer)

procedure define_column_raw(

cursor in integer,

position in integer,

column in raw,

column_size in integer)

procedure define_column_rowid(

cursor in integer,

position in integer,

column in rowid)

Input parameters

cursor

The ID number of the cursor for the row being defined to be selected.

position

The relative position of the column in the row being defined. The first column in a statement has position 1.

column

The value of the column being defined. The type of this value determines the type for the column being defined.

column_size

The maximum expected size of the column value, in bytes, for columns of type VARCHAR2, CHAR, and RAW.

 EXECUTE Function

This function executes the given cursor and returns the number of rows processed. The return value is only valid for INSERT, UPDATE and DELETE statements; for other types of statements, the return value is undefined and should be ignored.

Syntax

function execute (cursor in integer)

return integer

Input parameters

cursor

The ID number of the cursor to execute.

Return Value

For INSERT, UPDATE, and DELETE statements, this function returns the number of rows processed; otherwise, the return value is undefined and should be ignored.

 EXECUTE_AND_FETCH Function

This function executes the given cursor and fetches rows. This function provides the same functionality as calling EXECUTE and then calling FETCH_ROWS. Calling EXECUTE_AND_FETCH instead, however, may cut down on the number of message round-trips when used against a remote database.

Syntax

function execute_and_fetch(

cursor in integer,

exact in boolean default false)

return integer

Input parameters

cursor

The ID number of the cursor to execute and fetch.

exact

Set TRUE to raise an exception if the number of rows actually matching the query differs from one. Even if an exception is raised, the rows are still fetched and available.

Return Value

This function returns the number of rows actually fetched.

 FETCH_ROWS Function

This function tries to fetch a row from the given cursor. You can call FETCH_ROWS repeatedly as long as there are rows remaining to be fetched. These rows are retrieved into a buffer, and must be read by calling COLUMN_VALUE, for each column, after each call to FETCH_ROWS.

Syntax

function fetch_rows(cursor in integer)

return integer

Input parameters

cursor

The ID number of the cursor to fetch.

Return Value

This function returns the number of rows actually fetched.

 IS_OPEN Function

This function returns TRUE if the given cursor is currently open.

Syntax

function is_open(cursor in integer)

return boolean

Input parameters

cursor

The ID number of the cursor to check.

Return Value

This function returns TRUE if the cursor is currently open, or FALSE if it is not.

 OPEN_CURSOR Function

This function opens a new cursor. When you no longer need this cursor, you must close it explicitly by calling CLOSE_CURSOR.

You can use cursors to execute the same SQL statement repeatedly or to execute a new SQL statement. When a cursor is reused, the contents of the corresponding cursor data area are reset when the new SQL statement is parsed. It is never necessary to close and reopen a cursor before reusing it.

Syntax

function open_cursor return integer

Return Value

This function returns the cursor ID number of the new cursor.

 PARSE Procedure

This procedure parses the given statement in the given cursor. Currently, unlike the OCI parsing feature, which supports deferred parsing, all statements are parsed immediately. This may change in future versions; you should not rely on this timing.

Syntax

procedure parse(

cursor in integer,

statement in varchar2,

language_flag in integer)

Input parameters

cursor

The ID number of the cursor in which to parse the statement.

statement

The SQL statement to be parsed.

language_flag

This parameter determines how Oracle handles the SQL statement. The following options are recognized for this parameter:

V6	specifies Version 6 behavior

V7	specifies Oracle7 behavior

NATIVE	specifies normal behavior for the database to which the program is connected

 VARIABLE_VALUE Procedure

This procedure returns the value of the named variable for a given cursor.

Syntax

procedure variable_value(

cursor in integer,

name in varchar2,

value out <datatype>)

where <datatype> can be any one of the following types:

number

date

mlslabel

varchar2

The following syntax is also supported for the variable_value procedure:

procedure variable_value_char(

cursor in integer,

name in varchar2,

value out char)

procedure variable_value_raw(

cursor in integer,

name in varchar2,

value out raw)

procedure variable_value_rowid(

cursor in integer,

name in varchar2,

value out rowid)

Input parameters

cursor

The ID number of the cursor from which to get the values.

name

The name of the variable for which you are retrieving the value.

Output parameters

value

The value of the variable for the specified position.

Exception

inconsistent_type (ORA-06562)

This exception is raised if the type of the output parameter differs from the actual type of the value as it was originally defined by the call to BIND_VARIABLE.

 ________________________________________

Locating Errors

There are additional functions in the DBMS_SQL package for obtaining information about the last-referenced cursor in the session. The values returned by these functions are only meaningful immediately after a SQL statement is executed.

 LAST_ERROR_POSITION Function

Returns the byte offset in the SQL statement text where the error occurred. The first character in the SQL statement is at position 0.

function last_error_position return integer;

 LAST_ROW_COUNT Function

Returns the cumulative count of the number of rows fetched.

function last_row_count return integer;

 LAST_ROW_ID Function

Returns the ROWID of the last row processed.

function last_row_id return rowid;

 LAST_SQL_FUNCTION_CODE Function

Returns the SQL function code for the statement. These codes are listed on page 2-8 of the Programmer's Guide to the ORACLE Call Interfaces Version 7.0. You should only check this function immediately after the SQL statement is executed; otherwise, the return value is undefined.

function last_row_id return integer;

 ________________________________________

Examples

This section provides example procedures that make use of the DBMS_SQL package.

Example 1	The following sample procedure is passed a SQL statement, which it then parses and executes.

create or replace procedure exec(string in varchar2) as

cursor integer;

return integer;

begin

   cursor := dbms_sql.open_cursor;

   --DDL statements are executed by the parse call, which

   --performs the implied commit

   dbms_sql.parse(cursor, string, dbms_sql.v7);

   return := dbms_sql.execute(cursor);

   dbms_sql.close_cursor(cursor);

end;

Creating such a procedure allows you to perform the following operations:

•	The SQL statement can be dynamically generated at run time by the calling program.

•	The SQL statement can be a DDL statement.

For example, after creating this procedure, you could make the following call:

exec('create table acct(c1 integer)');

You could even call this procedure remotely, as shown in the following example. This allows you to perform remote DDL.

exec@hq.com('create table acct(c1 integer)');

Example 2	The following sample procedure is passed the names of a source and a destination table, and copies the rows from the source table to the destination table. This sample procedure assumes that both the source and destination tables have the following columns:

ID of type NUMBER

NAME of type VARCHAR2(30)

BIRTHDATE of type DATE

This procedure does not specifically require the use of dynamic SQL, however, it illustrates the concepts of this package.

create or replace procedure copy(source in varchar2,

                          destination in varchar2) is

-- This procedure copies rows from a given source table to a

-- given destination table assuming that both source and

-- destination tables have the following columns:

--   - ID of type NUMBER,

--   - NAME of type VARCHAR2(30),

--   - BIRTHDATE of type DATE.

  id number;

  name varchar2(30);

  birthdate date;

  source_cursor integer;

  destination_cursor integer;

  ignore integer;

begin

  -- prepare a cursor to select from the source table

  source_cursor := dbms_sql.open_cursor;

  dbms_sql.parse(source_cursor,

       'select id, name, birthdate from ' || source,

        dbms_sql.v7);

  dbms_sql.define_column(source_cursor, 1, id);

  dbms_sql.define_column(source_cursor, 2, name, 30);

  dbms_sql.define_column(source_cursor, 3, birthdate);

  ignore := dbms_sql.execute(source_cursor);

  -- prepare a cursor to insert into the destination table

  destination_cursor := dbms_sql.open_cursor;

  dbms_sql.parse(destination_cursor,

                'insert into ' || destination ||

                ' values (:id, :name, :birthdate)',

                 dbms_sql.v7);

  -- fetch a row from the source table and

  -- insert it into the destination table

  loop

    if dbms_sql.fetch_rows(source_cursor)>0 then

      -- get column values of the row

      dbms_sql.column_value(source_cursor, 1, id);

      dbms_sql.column_value(source_cursor, 2, name);

      dbms_sql.column_value(source_cursor, 3, birthdate);

      -- bind the row into the cursor that inserts into the

      -- destination table

      -- You could alter this example to require the use of

      -- dynamic SQL by inserting an if condition before the

      -- bind.

      dbms_sql.bind_variable(destination_cursor, 1, id);

      dbms_sql.bind_variable(destination_cursor, 2, name);

      dbms_sql.bind_variable(destination_cursor, 3,

                             birthdate);

      ignore := dbms_sql.execute(destination_cursor);

    else

      -- no more row to copy

      exit;

    end if;

  end loop;

  -- commit and close all cursors

  commit;

  dbms_sql.close_cursor(source_cursor);

  dbms_sql.close_cursor(destination_cursor);

exception

  when others then

    if dbms_sql.is_open(source_cursor) then

      dbms_sql.close_cursor(source_cursor);

    end if;

    if dbms_sql.is_open(destination_cursor) then

      dbms_sql.close_cursor(destination_cursor);

    end if;

    raise;

end;


CHAPTER 8. SNAPSHOT REFRESH

This chapter describes the following topics:

•	Snapshot refresh groups

•	Automatically refreshing snapshots

•	Manually refreshing snapshots

•	New privilege requirements for creating, altering and dropping snapshots

 ________________________________________

Using Snapshot Refresh Groups

With release 7.1 of the Oracle Server, you can consistently update a collection of snapshots to a single point in time by creating a snapshot refresh group. For example, if you want to preserve a master detail relationship between a pair of snapshots, you should define this relationship on the master tables using declarative referential integrity constraints, and then place the related snapshots in the same refresh group.

This section describes the procedures provided in the DBMS_REFRESH package that allow you to create, alter and delete refresh groups. For more information on automatically refreshing snapshots see page 8 - 6.

 Creating a Refresh Group

The MAKE procedure of the DBMS_REFRESH package allows you to specify the members of the refresh group and the time interval used to determine when the members of the group should be refreshed.

The type of refresh performed is determined by the mode that you specified when you created each snapshot. If you did not specify a refresh mode for a snapshot, Oracle performs a fast refresh if possible; otherwise, it performs a complete refresh.

The MAKE procedure has the following syntax:

make(name IN VARCHAR2,

     list IN VARCHAR2,

     next_date IN DATE,

     interval IN VARCHAR2,

     implicit_destroy IN BOOLEAN DEFAULT FALSE,

     lax IN BOOLEAN DEFAULT FALSE,

     job IN INTEGER DEFAULT 0,

     rollback_seg IN VARCHAR2 DEFAULT NULL)

where:

list := [schema.]name[, snapshot_list]

name

is a unique name used to identify the refresh group. Refresh groups must follow the same naming conventions as tables.

list

is a comma-separated list of snapshots that you want to refresh. These snapshots can be located in different schemas and have different master tables; however, all of the listed snapshots must be in your current database.

Instead of a comma separated list, you can supply a PL/SQL table of names of snapshots that you want to refresh using the datatype DBMS_UTILITY.UNCL_ARRAY. If the table contains the names of N snapshots, the first snapshot should be in position 1 and the N + 1 position should be set to null.

next_date

is the next date that you want a refresh to occur.

interval

is the function used to calculate the next time to refresh the snapshots in the group. This field is used with the NEXT_DATE value. For example if you specify NEXT_DAY(SYSDATE+1, ''MONDAY'') as your interval, and your NEXT_DATE evaluates to Monday, Oracle will refresh the snapshots every Monday. For more information on how to specify a refresh interval, see page 8 - 7.

implicit_destroy

Set this argument to TRUE if you want to automatically delete the refresh group when it no longer contains any members. This flag is only checked when you call the SUBTRACT procedure. That is, setting this flag still allows you to create an empty refresh group.

lax

A snapshot can belong to only one refresh group at a time. If you are moving a snapshot from an existing group to a new refresh group, you must set the LAX flag to TRUE to succeed. Oracle then automatically removes the snapshot from the other refresh group and updates its refresh interval to be that of its new group. Otherwise, the call to MAKE generates an error message.

job

This parameter is reserved for future use. Use the default value 0.

rollback_seg

is the name of the rollback segment to use while refreshing snapshots. The default, null, uses the default rollback segment.

 Altering a Refresh Group

The DBMS_REFRESH package contains separate procedures for adding new members to a refresh group, removing members from a refresh group, and altering the automatic refresh interval for a refresh group.

 Adding Members to a Refresh Group

Use the DBMS_REFRESH.ADD procedure, which has the following syntax, to add snapshots to a refresh group.

add(name IN VARCHAR2,

    list IN VARCHAR2

    lax IN BOOLEAN DEFAULT FALSE)

name

is the name of the refresh group to which you want to add members.

list

is a comma-separated list of snapshots that you want to add to the refresh group. Alternatively, you may pass in a PL/SQL table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a snapshot.

lax

A snapshot can belong to only one refresh group at a time. If you are moving a snapshot from one refresh group to another, you must set the LAX flag to TRUE to succeed. Oracle then automatically removes the snapshot from the other refresh group. Otherwise, the call to ADD generates an error message.

 Removing Members from a Refresh Group

Use the DBMS_REFRESH.SUBTRACT procedure to remove snapshots from a refresh group. If you set IMPLICIT_DESTROY to TRUE in the MAKE call for the refresh group, Oracle automatically deletes the group whenever you subtract the last member.

The syntax of the SUBTRACT procedure is:

subtract(name IN VARCHAR2,

         list IN VARCHAR2)

name

is the name of the refresh group from which you want to remove members.

list

is a comma-separated list of snapshots that you want to remove from the refresh group. Alternatively, you may pass in a PL/SQL table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a snapshot.

 Altering the Refresh Interval

If you want to change how often a snapshot group is refreshed (for example, if you want the snapshots refreshed once a day as opposed to once a week) you can use the DBMS_REFRESH.CHANGE command to change the refresh interval.

The CHANGE procedure has the following syntax:

change(name IN VARCHAR2,

       next_date IN DATE,

       interval IN VARCHAR2,

       implicit_destroy IN BOOLEAN DEFAULT NULL,

       rollback_segment IN BOOLEAN DEFAULT NULL)

name

is the name of the refresh group for which you want to alter the refresh interval.

next_date

is the date on which you want the next refresh to occur. Specify null to leave the date unchanged.

interval

is the formula that you want used to calculate the time period between automatic refreshes. Specify null to leave the interval unchanged.

implicit_destroy

Allows you to reset the value of the IMPLICIT_DESTROY flag. If this flag is set, Oracle automatically deletes the group if it no longer contains any members. By default, this flag remains unchanged.

rollback_segment

Allows you to change the rollback segment used. By default, the rollback segment remains unchanged. To reset this parameter to use the default rollback segment, specify 'null', including the quotes. Specifying null, without quotes, indicates that you do not want to change the rollback segment currently being used.

 Deleting a Refresh Group

If you want to remove all of the snapshots from a refresh group and delete the refresh group, use the DBMS_REFRESH.DESTROY procedure. The DESTROY procedure has the following syntax:

destroy(name IN VARCHAR2)

name

The name of the refresh group that you want to remove.

 ________________________________________

Refreshing Snapshots

The information in this section replaces the information in the section "Refreshing Snapshots" on pages 16-5 through 16-9 of the Oracle7 Server Administrator's Guide.

You can refresh a snapshot to make the snapshot reflect a more recent state of its master tables. There are two types of refreshes: a fast refresh and a complete refresh. A fast refresh uses the snapshot log of a master table to refresh a simple snapshot, by transmitting only the changes needed to bring the snapshot up to date. Only simple snapshots (that is, those consisting of a subset of rows and columns of a single table) can execute a fast refresh. A complete refresh entirely replaces the existing data in a simple or complex snapshot. Also, snapshots can be refreshed automatically or manually, either individually or in groups.

Consider the following issues when deciding how to refresh a snapshot:

•	In most cases with a simple snapshot, use fast refreshes because they are more efficient than complete refreshes.

•	If the master tables receive predictable updates, automatically refresh the associated snapshots at the appropriate interval.

•	After bulk loads to the master tables, manually refresh all snapshots based on the master tables. This propagates the new rows of the master tables to associated snapshots.

•	If you need to refresh a collection of snapshots to a single point in time, such as when there is a parent/child relationship between a pair of snapshots, use snapshot refresh groups.

Automatically Refreshing Snapshots

If you want to have your snapshots automatically refreshed at a periodic interval, you must complete the following steps.

•	You must specify a snapshot refresh interval and type of refresh.

•	You must have one or more snapshot refresh processes that wake up periodically and refresh any snapshots that are due to be refreshed.

 Specifying a Snapshot Refresh Interval

If you want to automatically refresh an individual snapshot, specify a refresh interval using the START WITH and NEXT parameters in the REFRESH clause of a CREATE SNAPSHOT or ALTER SNAPSHOT statement. This automatically creates a snapshot refresh group that consists of exactly one snapshot. This refresh group has the same name as the snapshot itself.

If you want to automatically refresh a collection of snapshots to a single point of time, you must create a snapshot refresh group using the DBMS_REFRESH.MAKE procedure. To automatically refresh a snapshot refresh group, supply NEXT_DATE and INTERVAL values when you create the group.

 Specifying the Refresh Type

When you refresh a snapshot, you can specify that Oracle perform a FAST, COMPLETE, or FORCEd refresh. You can specify one of these three refresh types in the REFRESH clause of a CREATE SNAPSHOT or ALTER SNAPSHOT statement. The snapshots in a refresh group do not have to have the same refresh type. If you do not specify a refresh type, Oracle performs a FORCEd refresh. FORCE performs a fast refresh if possible, or a complete refresh otherwise.

 Starting a Snapshot Refresh Process

You must have at least one snapshot refresh process to automatically refresh your snapshots. An instance can have up to ten of these processes, named SNP0 to SNP9. These processes periodically wake up and refresh any snapshot refresh groups that are due to be automatically refreshed.

If an instance has multiple snapshot refresh processes, the task of updating the snapshots can be shared across these processes, thus improving performance. All snapshots in a single refresh group are refreshed by a single snapshot refresh process.

Snapshot refresh processes differ from other background processes, in that the failure of the SNPn process does NOT cause the instance to fail.

You use three initialization parameters to control the operation of the snapshot refresh processes. Set these parameters in the initialization parameter file for an instance. They take effect the next time you start the instance. The snapshot refresh process parameters are as follows:

•	SNAPSHOT_REFRESH_PROCESSES

•	SNAPSHOT_REFRESH_INTERVAL

•	SNAPSHOT_REFRESH_KEEP_CONNECTIONS

These parameters are described in Appendix A of this addendum.

As described in Chapter 16 of the Oracle7 Server Administrator's Guide, DBMS_SNAPSHOT.REFRESH_ALL can be used to manually refresh all snapshots due for refreshing. The DBMS_SNAPSHOT.REFRESH_ALL procedure is unnecessary when SNPn background processes are running.

Understanding the Snapshot Refresh Interval

When setting a snapshot's refresh interval, understand the following behavior:

•	The dates or date expressions in the START WITH and NEXT parameters of an individual snapshot, or INTERVAL and NEXT_DATE parameters in the procedure calls for a refresh group, must evaluate to a future point in time. If they do not, you receive an error message. A date literal must be enclosed in single quotes, while date expressions do not require quotes.

•	If a snapshot should be refreshed periodically at a set interval, use the NEXT or INTERVAL parameter with a date expression similar to "SYSDATE + 7". For example, if you set the automatic refresh interval to "SYSDATE + 7" on Monday but for some reason, such as network failure, the snapshot is not refreshed until Thursday, "SYSDATE + 7" now evaluates to every Thursday, not Monday. If you always want to automatically refresh a snapshot group at a specific time, regardless of the last refresh (for example, every Monday), the INTERVAL or NEXT parameters should specify a date expression similar to "NEXT_DAY(TRUNC(SYSDATE), `MONDAY')".

Table 8 - 1 lists some common date expressions used for snapshot refresh intervals.

Date Expression	Evaluation

------------------------------------------------------------------------

SYSDATE + 7	Exactly seven days

from the last refresh

SYSDATE + 1/48	Every half hour

NEXT_DAY(TRUNC(SYSDATE), 'MONDAY')+15/24	Every Monday at 3 PM

NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3),	The first Thursday of

       'THURSDAY')	each quarter

Table 8 - 1. Examples of Common Refresh Intervals

Examples	The following examples show valid combinations for specifying a refresh interval.

CREATE SNAPSHOT snap

  . . .

  REFRESH COMPLETE

          START WITH '01-JUN-94'

          NEXT sysdate + 7

  AS . . . ;

This statement creates the SNAP snapshot, specifying complete automatic refreshes, the first of which occurs on June 1, 1994, with an automatic refresh interval of seven days from the most recent refresh.

The following command creates the ACCT refresh group, which is composed of three snapshots that will be refreshed every Monday.

EXECUTE dbms_refresh.make('acct', 'scott.acct, finance, inventory',

        SYSDATE, 'next_day(SYSDATE + 1, ''MONDAY'')',

        TRUE, TRUE);

Note that because the interval function evaluates to a literal, it must be enclosed in single quotes. Because you must use two single quotes to represent one single quote within a literal, in this example, the literal MONDAY is enclosed in two sets of single quotes.

 Troubleshooting Automatic Refresh Problems

Several factors can prevent the automatic refresh of snapshots: not having a snapshot refresh process, an intervening network or instance failure, or an instance shutdown. You may also encounter an error if you attempt to define a master detail relationship between two snapshots. You should only define master detail relationships on the master tables by using declarative referential integrity constraints; the related snapshots should then be placed in the same refresh group in order to preserve this relationship. Although not prevented by Oracle, if you attempt to define these constraints at the snapshot level, when refreshed, the snapshots may temporarily enter into a state where they violate the integrity constraints that you have defined, producing a runtime error.

When any of the factors described above prevents the automatic refresh of a snapshot group, the group remains due to be refreshed. (Remember, when you specify a refresh interval for an individual snapshot, Oracle automatically creates a refresh group for that snapshot.)

If Oracle encounters a failure, such as a network failure, when attempting to refresh a snapshot refresh group it reattempts the refresh. The first attempt is made after one minute, the second attempt after two minutes, the third after four minutes, and so on, with the interval doubling between each attempt. When the retry interval exceeds the refresh interval, Oracle continues to retry the refresh at the normal refresh interval.

Thus, snapshot refresh groups due to be refreshed will generally be refreshed automatically shortly after you start a snapshot refresh process or resolve any network failures. However, if Oracle continues to encounter errors when attempting to refresh a snapshot, it considers the group broken after its sixteenth unsuccessful attempt. Oracle indicates that a snapshot is broken by setting the BROKEN column of the USER_REFRESH and USER_REFRESH_CHILDREN views to Y.

The errors causing Oracle to consider a snapshot refresh group broken are recorded in a trace file. After you have corrected these errors you must manually refresh the group by calling the procedure DBMS_REFRESH.REFRESH described on page 8 - 9. This resets the broken flag to N, and automatic refreshes will proceed from this point.

Additional Information:  The name of the snapshot trace file is of the form SNPn, where n is platform specific. Consult your platform-specific Oracle documentation for the name on your system.

 Manually Refreshing Snapshots

If you want to manually force a refresh to occur, you have two options.

•	You can manually refresh an existing snapshot refresh group.

•	You can manually refresh one or more snapshots, that may, or may not, be part of one or more refresh groups.

Manually Refreshing a Snapshot Refresh Group

Use the DBMS_REFRESH.REFRESH procedure to manually refresh a refresh group. This causes the refresh to occur immediately instead of waiting for the next automatic refresh interval. Manually refreshing a refresh group does not affect the next automatic refresh of the group.

The syntax of the REFRESH procedure is:

refresh(name IN VARCHAR2)

name is the name of the refresh group that you want to refresh manually. For example, to refresh the ACCT refresh group, you would issue the following command in SQL*Plus or SQL*DBA.

EXECUTE dbms_refresh.refresh('acct');

 Manually Refreshing One or More Snapshots

To consistently refresh one or more snapshots that are not members of the same refresh group use the REFRESH procedure of the package DBMS_SNAPSHOT. This command allows you to provide a comma- separated list of snapshots that you want to refresh to a transaction consistent point in time. These snapshots can belong to other refresh groups. Refreshing them using this procedure will not affect their regularly scheduled refresh interval if they are part of an automatic snapshot refresh group. However, if they are members of other groups, you should be aware that those groups will no longer be transaction consistent.

Invoke the REFRESH procedure with the following syntax:

dbms_snapshot.refresh(list VARCHAR2,

                      method VARCHAR2 DEFAULT NULL,

                      rollback_seg VARCHAR2 DEFAULT NULL)

list

is a comma-separated list of snapshots that you want to refresh of the form:

list := [schema.]name[, list]

These snapshots can be located in different schemas and have different master tables; however, all of the listed snapshots must be in your current database. Alternatively, you may pass in a PL/SQL table of type DBMS_UTILITY.UNCL_ARRAY, where each element is the name of a snapshot.

method

specifies the type of refresh to perform for each snapshot listed; `F' or `f' indicates a fast refresh, `C' or `c' indicates a complete refresh, and `?' indicates a default refresh. If you specified a refresh mode when you created the snapshot, that mode is used when you specify a default refresh. If no mode was specified, Oracle performs a fast refresh if possible; otherwise, it performs a complete refresh. If the METHOD list contains fewer elements than the snapshot LIST, the trailing elements in the snapshot list are refreshed using a default refresh. For example, the following EXECUTE statement within SQL*DBA or SQL*Plus:

EXECUTE dbms_snapshot.refresh('emp, dept, scott.salary', 'CF');

performs a complete refresh of the EMP snapshot, a fast refresh of the DEPT snapshot, and a default refresh of the SCOTT.SALARY snapshot.

rollback_seg

is the name of the rollback segment to use while refreshing snapshots.

When you call REFRESH, all of the listed snapshots are updated to a single point in time. If the refresh fails for any of the snapshots, none of the snapshots are updated.

 Snapshot Refresh and Remote Databases

You must fully qualify remote database names when issuing a CREATE SNAPSHOT statement or snapshot refreshes may fail due to naming and privilege conflicts. For example, if you created a snapshot using the following commands:

CONNECT scott/tiger

CREATE DATABASE LINK newyork USING 'newyorkconnectstring';

CREATE SNAPSHOT mysnap AS SELECT * FROM emp@newyork;

then refreshes of the snapshot will fail if the user is not SCOTT.

Instead, you should replace the above CREATE SNAPSHOT statement with the following:

CREATE SNAPSHOT mysnap AS SELECT * FROM scott.emp@newyork;

Your manual refreshes should then succeed. Automatic refreshes, however, will only work if the username and password strings are embedded in the database link, as shown in the following statement:

CREATE DATABASE LINK newyork CONNECT TO scott IDENTIFIED BY tiger

    USING 'newyorkconnectstring';

The username and password that you specify allow the snapshot background process to connect to the database. For the refresh to succeed, this user must have SELECT privileges on the master table and, for fast refreshes, the snapshot log.

 Viewing Refresh Group Information

The data dictionary contains the following views that have information about refresh groups.

•	USER_REFRESH

•	USER_REFRESH_CHILDREN

 USER_REFRESH

This view lists each refresh group found in the database, and includes information about the refresh interval for each group.

ROWNER	The owner of the refresh group.

RNAME	The name of the refresh group.

REFGROUP	The ID number for the refresh group.

IMPLICIT_ DESTROY	The implicit delete flag. If this value is Y, Oracle deletes the refresh group after you have subtracted the last member from the group.

JOB	Reserved for future use.

NEXT_DATE	The date when the members of the group will next be refreshed.

INTERVAL	The function used to calculate the interval between refreshes.

BROKEN	The broken flag, which is used to indicate a problem with the refresh group. If this value is Y, Oracle will not refresh the group, even if it is scheduled to be refreshed.

 USER_REFRESH_CHILDREN

This view lists the members of each refresh group, and includes information about the refresh interval for each member.

OWNER	The owner of the refresh group member.

NAME	The name of the refresh group member.

TYPE	The type of the refresh group member, for example, SNAPSHOT.

ROWNER	The owner of the refresh group.

RNAME	The name of the refresh group.

REFGROUP	The ID number for the refresh group.

IMPLICIT_ DESTROY	The implicit delete flag. If this value is Y, Oracle deletes the refresh group after you have subtracted the last member from the group.

JOB	Reserved for future use.

NEXT_DATE	The date when the members of the group will next be refreshed.

INTERVAL	The function used to calculate the interval between refreshes.

BROKEN	The broken flag, which is used to indicate a problem with the refresh group. If this value is Y, Oracle will not refresh the group, even if it is scheduled to be refreshed.

 Privileges Required to Refresh a Snapshot

To refresh a snapshot, you must meet the following criteria:

•	You must own the snapshot or have the ALTER ANY SNAPSHOT system privilege.

•	The snapshot owner (or the user that you have connected as, if you are using a dblink) must have SELECT privileges on the master table and, for fast refreshes, on the snapshot log.

 ________________________________________

Privileges

The privileges required to CREATE, ALTER and DROP snapshots have been simplified in release 7.1 of the Oracle Server. Snapshots now operate out of the security domain of the snapshot owner, in much the same manner as stored procedures. You might want to note these changes in the appropriate manuals.

 Privileges Required to Create a Snapshot

This information replaces the section "Privileges Required to Create Snapshots" starting on page 16-3 of the Oracle7 Server Administrator's Guide and updates the prerequisite information on page 4-193 of the Oracle7 Server SQL Language Reference Manual.

To create a snapshot, the following sets of privileges must be granted:

•	To create a snapshot in your own schema, you must have the CREATE SNAPSHOT, CREATE TABLE, CREATE VIEW, and CREATE INDEX (only for simple snapshots) system privileges, as well as SELECT privilege on the master tables.

•	To create a snapshot in another user's schema, you must have the CREATE ANY SNAPSHOT privilege and the appropriate SELECT privilege on the master tables. Additionally, the schema owner must have CREATE TABLE, CREATE VIEW and CREATE INDEX (only for simple snapshots) system privileges, as well as SELECT privilege on the master table.

 Privileges Required to Alter a Snapshot

This information replaces the section "Privileges Required to Alter a Snapshot" on page 16-5 of the Oracle7 Server Administrator's Guide.

To refresh a snapshot or alter a snapshot's storage parameters, the snapshot must be contained in your schema or you must have the ALTER ANY SNAPSHOT system privilege.

In addition, to refresh a snapshot, the snapshot owner (or the user that you have connected as, if you are using a dblink) must have SELECT privileges on the master table and, for fast refreshes, the snapshot log.

 Privileges Required to Drop a Snapshot

This information replaces the section "Privileges Required to Drop a Snapshot" on page 16-10 of the Oracle7 Server Administrator's Guide and updates the prerequisite information on page 4-269 of the Oracle7 Server SQL Language Reference Manual.

Only the owner of a snapshot or a user with the DROP ANY SNAPSHOT system privilege can drop a snapshot.


CHAPTER 9. MISCELLANEOUS ADDITIONS

This chapter describes various features or capabilities that are available or different with Oracle7 release 7.1. Topics in this chapter include:

•	using Oracle Names product with the Oracle Server

•	using multiple triggers of the same type per table

•	using the CACHE option for tables and clusters

•	changes to auditing BY ACCESS

 ________________________________________

Oracle Names

This section describes the distributed database support provided by the new product Oracle Names. Oracle Names Version 1.0 is available with Oracle7 release 7.1 and SQL*Net release 2.1.

Oracle Names makes network address and database link information available to all nodes throughout the network. This information is loaded by Oracle Names from the network definition which you create using the SQL*Net administrative interface--the Oracle Network Manager.  For example, each database server's network address is stored with a name that is used to identify it. Client applications can then request a database connection by a simple name rather than a lengthy address.

The primary benefit to central storage of network names is reduced administration and ease of use. When an administrator enters or changes the information for a network object, this new information is immediately available to all clients accessing Oracle Names. This means that network address details are conveniently isolated from all clients and servers to provide further server location transparency.

This section describes how to configure your database for use with Oracle Names. For more information about Oracle Names, refer to Understanding SQL*Net Version 2.1, Oracle Network Manager Administrator's Guide Version 1.0, and the Oracle Names Administrator's Guide Version 1.0.

 Contents of Oracle Names

Oracle Names stores names and addresses for shared database link specifications, network database services, and object aliases.

Database or network services	The SQL*Net Version 2 or Version 1 address for connecting to any database server or network services in the network.

Database link specifications	The database link definitions to access remote databases.

Object aliases	A globally unique alternative name for a database or service or database link.

Once defined, the Oracle Names network objects are referenced only by name. When a connection is required, it is requested by name and the necessary exchange of details occurs between the requestor and Oracle Names.

 Implementing Oracle Names

To use Oracle Names, you need to appropriately configure new databases or reconfigure existing databases and set up your network database services and object aliases.

 Configuring a New Database

To configure a new database for implementing Oracle Names, the following steps are recommended:

1.	Determine a domain hierarchy. (Refer to the Oracle Names Administrator's Guide Version 1.0 for a detailed discussion of this topic.)

2.	Specify a database name and domain for the initialization parameters DB_NAME and DB_DOMAIN.

3.	Set the initialization parameter GLOBAL_NAMES to TRUE in the parameter file. This is not strictly required by Oracle Names, but Oracle recommends enabling global names to allow the database to check the validity of database link names.

4.	Create the database.

5.	Verify the global database name. For example:

SQLDBA> SELECT * FROM GLOBAL_NAME;

6.	Verify that the global database name is the same as the SQL*Net database service name.

Note: Distributed databases that attempt to connect to the configured database may need to update their existing database links.

 Configuring an Existing Database

To configure an existing database for implementing Oracle Names, the following steps are recommended:

1.	Determine a domain hierarchy. (Refer to the Oracle Names Administrator's Guide Version 1.0 for a detailed discussion of this topic.)

2.	Rename the database using the ALTER DATABASE RENAME GLOBAL_NAME command if the database name does not match the hierarchy determined in step 1.

3.	Turn on global names checking (optional, but recommended) issuing the statement ALTER SYSTEM SET GLOBAL_NAMES = TRUE. You must also set the initialization parameter GLOBAL_NAMES to TRUE in the parameter file.

4.	Verify the global database name. For example:

SQLDBA> SELECT * FROM GLOBAL_NAME;

5.	Verify that the global database name is the same as the SQL*Net database service name.

Note: Distributed databases that attempt to connect to the configured database may need to update their existing database links.

 Setting Up Your Network Database Service

To set up your network database service, define the connection descriptors for connecting to your databases. Generally, define the database domain, community, nodes, database name, alias names, and client profiles using Oracle Network Manager.

In addition, verify that the database service name is the same as the global database name. For more specific information on setting up your network database service, refer to Understanding SQL*Net Version 2.1, Oracle Network Manager Administrator's Guide Version 1.0, and the Oracle Names Administrator's Guide Version 1.0.

Creating a database definition in the Oracle Network Manager automatically creates a global database link to that database. Additional links may be created by specifying any number of connection qualifiers, usernames, and passwords in the Oracle Network Manager. These links are immediately available to all clients and databases. Refer to Understanding SQL*Net Version 2.1, Oracle Network Manager Administrator's Guide Version 1.0, and the Oracle Names Administrator's Guide Version 1.0.

 Using Oracle Names

Because Oracle Names stores names and definitions, the database string (specified by the USING clause in the CREATE DATABASE LINK command) is not necessary. In fact, unless you need to override the CONNECT TO clause, public or private database links do not need to be created.

 Database Search Path

When a user selects information from a database table (for example, SELECT * FROM EMP@HR.US.ORACLE.COM), Oracle searches, in order, for a connect string in the following locations in the following order:

1.	the user's private database links

2.	the database's public database links

3.	the Oracle Names server's global database links

Oracle ends the search when a connect string is found in one of the above areas. If a connect string is not found, the following message is returned:

ORA-02019: "connection description for remote database not found"

If a CONNECT TO clause (username/password) is found in any of the searched database links, the first one found is used. If none are found, then the current user's username/password is used.

 Connection Qualifiers

Connection qualifiers to a database link (delimited by the second @ sign) provide a means to create multiple links to a database. Multiple database links to the same database provide different access routes with different accounts and privileges. Database link connection qualifiers are used with private, public, and global database links.

The following examples select data from the same database (HR.US.ORACLE.COM), but use three distinct database links. Each database link accesses different accounts on the same database with a different set of access privileges.

SELECT * FROM emp@hr.us.oracle.com;

SELECT * FROM schedule@hr.us.oracle.com@admin;

SELECT * FROM emp_salaries@hr.us.oracle.com@fin;

In the above example, @admin and @fin are connection qualifiers. Each of the above links could be resolved as a private, public, or Oracle Names database link.

 Restrictions

When GLOBAL_NAMES = TRUE, the global database name used in the database link must match the GLOBAL_NAME parameter name of the remote database. This ensures that each database link connects to the correct database.

It is recommended that global database names be assigned either when the database is created or when Oracle Names is installed. Thereafter is it not recommended that you change global database names.

SQL*Net requires that all global names be less than or equal to 64 characters in length.

 ________________________________________

Triggers

In Oracle release 7.0, you are allowed to create only one of each of the following types of triggers:

BEFORE UPDATE row	AFTER UPDATE row

BEFORE DELETE row	AFTER DELETE row

BEFORE INSERT row	AFTER INSERT row

BEFORE UPDATE statement	AFTER UPDATE statement

BEFORE DELETE statement	AFTER DELETE statement

BEFORE INSERT statement	AFTER INSERT statement

In release 7.1, this restriction is removed, allowing multiple triggers of the same type per table. Although any trigger can execute a sequence of operations either in-line or by calling procedures, using multiple triggers of the same type enhances database administration by permitting the modular installation of applications that have triggers on the same tables. Since Oracle snapshot logs use AFTER ROW triggers, the ability to use multiple triggers of the same type also permits user applications to use AFTER ROW triggers on tables where the snapshot logs are maintained.

 Firing Order

There are no changes to the overall firing order of the various trigger types. When a trigger is invoked, Oracle does the following:

•	verifies user access

•	verifies trigger validity

•	executes the procedure

If you have multiple triggers of the same type on a single table, Oracle chooses an arbitrary order to execute the triggers. Each subsequent trigger sees the changes made by the previously fired triggers. Each trigger can see the old and new values. The old values are the original values and the new values are the current values as set by the UPDATE or INSERT trigger or subsequently modified by a previous trigger.

Additionally, Oracle executes all triggers of the same type before executing triggers of a different type.

To ensure that multiple triggered actions occur in a specific order, you must consolidate these actions into a single trigger (for example, by having the trigger call a series of procedures).

 Functionality

There are no changes to the functionality involved in the execution of each trigger; the same mutating and constraining table restrictions apply as in release 7.0.

See the Oracle7 Server Concepts Manual for more information on triggers.

 Restrictions on Triggers

You cannot open a database that contains multiple triggers of the same type if you are using any version of Oracle before release 7.1, nor can you open such a database if your COMPATIBLE initialization parameter is set to a version earlier than 7.1.0. For information on downgrading to an earlier release, see page 1 - 2.

 ________________________________________

Caching Tables

Previous releases of the Oracle Server allowed tables to be cached by specifying a value for the SMALL_TABLE_THRESHOLD initialization parameter. Tables whose sizes were smaller than the value of this parameter were treated differently than other tables when a full table scan was performed.

Instead of a full scan causing a database block to be placed on the least recently used end of the LRU list in the buffer cache (so it will be paged out more quickly), the block would be placed on the most recently used end of the LRU list. This causes the blocks of the table to stay in the buffer cache for a longer time, and subsequent accesses of the table are more likely to find the table in the buffer cache. You may want to cache small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.

In release 7.1 of the Oracle Server, you can specify tables this caching behavior on a per-table basis with the CACHE clause of the CREATE TABLE, ALTER TABLE, CREATE CLUSTER, and ALTER CLUSTER commands. The SMALL_TABLE_THRESHOLD parameter is no longer supported. The syntax of the CREATE TABLE command is illustrated below.

CACHE	Specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.

NOCACHE	Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the default behavior.

You can also alter an existing table to specify that it should be cached upon access. The syntax of the ALTER TABLE statement is illustrated below. The keywords for the ALTER TABLE command have the same description as the keywords for the CREATE TABLE command.

The CREATE CLUSTER and ALTER CLUSTER commands also have a cache clause. The new syntax for those commands is illustrated on pages 6 - 14 and 6 - 15.

 Hints

You can specify the caching behavior for a table with two new hints, CACHE and NOCACHE. You should use a hint to specify the caching behavior if you want a table treated differently upon each access. For example, if a table is used for both decision support applications and online transaction processing (OLTP) applications, you may want to cache the table for the decision support queries and have the table behave normally for the OLTP queries.

Refer to the Oracle7 Server Application Developer's Guide for a general discussion on using hints in SQL statements. The new hints are described in the following sections.

 CACHE

The CACHE hint specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. In the following example, the CACHE hint overrides the table's default caching specification.

SELECT /*+ FULL (scott_emp) CACHE(scott_emp) */

ename

FROM scott.emp scott_emp;

 NOCACHE

The NOCACHE hint specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache. The following example illustrates the NOCACHE hint.

SELECT /*+ FULL(scott_emp) NOCACHE(scott_emp) */

ename

FROM scott.emp scott_emp;

 ________________________________________

Caching Session Cursors

When a session cursor was closed in previous releases of Oracle, Oracle freed all the memory and state associated with the cursor. If an application repeatedly issues parse calls on the same set of SQL statements, the reopening of the session cursors can affect system performance. Release 7.1 allows session cursors to be stored in a session cursor cache. This feature can be particularly useful for applications designed using Oracle Forms because switching between forms closes all session cursors associated with a form.

Oracle uses the shared SQL area to determine if more than three parse requests have been issued on a given statement. If so, Oracle assumes the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session will then find the cursor in the session cursor cache.

To enable caching of session cursors, you must set the initialization parameter SESSION_CACHED_CURSORS. This parameter is a positive integer that specifies the maximum number of session cursors kept in the cache. A least recently used (LRU) algorithm ages out entries in the session cursor cache to make room for new entries when needed.

You can also enable the session cursor cache dynamically with the ALTER SYSTEM SET SESSION_CACHED_CURSORS command.

To determine whether the session cursor cache is sufficiently large for your instance, you can examine the session statistic "session cursor cache hits" in the V$SESSTAT view. This statistic counts the number of times a parse call found a cursor in the session cursor cache. If this statistic is a relatively low percentage of the total parse call count for the session, you should consider setting SESSION_CACHED_CURSORS to a larger value.

 ________________________________________

Auditing Operations By Access

In Oracle7 release 7.0, setting audit BY ACCESS inserts one audit record into the audit trail for each auditable event the first time that a cursor containing an audited statement is executed. In release 7.1, a record for each execution of a statement within a cursor is placed in the audit trail. See page B - 19 for information on how auditing by access worked in release 7.0.

 ________________________________________

Privileges Required to Truncate a Table or Cluster

In Oracle7 release 7.0, you could issue the TRUNCATE command for a table or cluster not in your schema if you had the DELETE ANY TABLE system privilege. In release 7.1, you need the DROP ANY TABLE system privilege. You must still disable all references to foreign keys in different tables.


CHAPTER 10. TRUSTED ORACLE CHANGES

All features described in this Addendum apply to Trusted Oracle7 as well as standard Oracle7. In addition, this chapter describes the new label mapping feature introduced in release 7.1 of Trusted Oracle7. Topics discussed in this chapter include the following:

•	Label mapping

•	Importing labeled data

 ________________________________________

Label Mapping Feature

When moving data from a Trusted Oracle7 database on one system (source) to a Trusted Oracle7 database on another system (target) using Export/Import, the export file from the source database may contain labels that are not valid for the target database. This would only occur if your target system did not support all of the labels supported by your source database.

If the source labels are not recognized by the target database, the data cannot be directly imported. To import data that has labels that are not in the target database, use the label mapping feature. This label mapping feature uses a map file, which translates the labels from the source database into valid target database labels.

 The Export File

If you specified that labels should be included in a Trusted Oracle7 export file with the parameter MLSLABEL=Y (the default), the file will contain the MLSLABEL's string representation. This string representation can be matched up with its counterpart in the target system by using the label mapping feature.

To use the label mapping feature, the export file must be from a Trusted Oracle7 database and it must be imported into a Trusted Oracle7 release 7.1 or later database.

 The Label Mapping File

The map file used to specify label relationships can be generated by Import or created manually. The map file must meet the following requirements:

•	Source labels for data that you want mapped during import must be listed in the label mapping file.

•	Optionally, a corresponding valid target label can be listed for each source label. The same target label can also be listed for more than one source label. If a source label does not have a corresponding target label, then no mapping will occur for that label.

•	There must not be any blank lines within label listings.

•	There must not be any duplicate source label entries.

•	Each label name must be enclosed by single quotes.

•	The label string length must be less than 256 characters.

•	The MLS_LABEL_FORMAT type must be the same for both the source and target labels. It is recommended that you use the export default.

For example, if the source label is in numeric format (such as 0), then its corresponding target label in the label mapping file must also be in numeric format (such as 11). See Table 19-1 "Label Formats" in Chapter 19 "Retrieving and Formatting Labels" of the Trusted Oracle7 Server Administrator's Guide for more information on label formats.

Syntax	The syntax for the label listing is shown below.

'source label' = 'target label'

Note that the use of single quotes is necessary when specifying label names because a space can be a legal part of a label on many systems.

Source label	Is the label name that is valid for the Trusted Oracle7 database from which you exported the data. The label name includes the security level and category. These labels apply to all label components (sensitivity labels, information labels, integrity labels, and other supported label components).

Target label	Is the label name that is valid for the Trusted Oracle7 database into which you will be importing the data. The label name includes the security level and category. These labels apply to all label components (sensitivity labels, information labels, integrity labels, and other supported label components).

If there is a syntax error in the label mapping file, the following message is returned during processing:

ORA-00045 "unexpected character found on line %lu in mapfile"

Since label strings can be large, the relationship can extend across several lines. However, if the label strings are too large (greater than 255 characters), the following message is returned during processing:

ORA-00047 "label string larger than 255 characters found at line

%lu in mapfile"

Example	Consider data that is exported from a source Trusted Oracle7 database that uses INTERNAL, PROPRIETARY, CONFIDENTIAL, and CONFIDENTIAL:ALPHA labels. The data will be imported into a target Trusted Oracle7 database that correspondingly uses YELLOW, RED, BLUE, and BLUE:MONGOOSE labels.

In the label mapping file, the labels would be listed in the following manner:

'INTERNAL' = 'YELLOW'

'PROPRIETARY' = 'RED'

'CONFIDENTIAL' = 'BLUE'

'CONFIDENTIAL:ALPHA' = 'BLUE:MONGOOSE'

When the export file is imported into the target database, the source labels are mapped to the target labels and the data is imported and automatically labeled with the target labels. In this example, the data labeled at INTERNAL becomes YELLOW, PROPRIETARY becomes RED, CONFIDENTIAL becomes BLUE, and CONFIDENTIAL:ALPHA becomes BLUE:MONGOOSE.

 Including Comments

Comments may be placed in the label mapping file starting with the pound (#) character and continuing to the end of the line.

For example, the syntax for the following label map entry is valid:

'INTERNAL:RUTH,LORI' = 'YELLOW:WRITERS' #writer group

 Creating Manually

You must manually create the label mapping file if your export file is from a Trusted Oracle7 release 7.0 database.

To create the label mapping file manually, perform the following steps.

1.	Connect to the source Trusted Oracle7 database at the system equivalent of DBHIGH.

2.	Spool the contents of the ALL_LABELS view to a regular flat system file.

SQL> SET PAGESIZE 0

SQL> SPOOL labelmapfile.map

SQL> SELECT ''''||label_name||''''

2>   FROM all_labels;

SQL> SPOOL OFF

3.	Add the valid target labels for each source label using your system's file editor.

It is suggested that the .map be used for the file extension since it is the default extension when generating the label mapping file. The syntax for the label mapping file is  <filename>.map.

 Creating Automatically

If you have not created a label mapping file, you can automatically create one using the export file from the source database; this involves a full scan of the export file. The export file must be from a Trusted Oracle7 release 7.1 or later database.

Note: If you try to run this procedure on an export file earlier than release 7.1, the following message is returned:

ORA-00048 "the MLS_LISTLABELS option cannot be used with this

export file"

Note: You must specify both MLS options:  MLS_LISTLABELS and MLS_MAPFILE. If you do not specify the MLS_MAPFILE during this procedure, the following message is returned:

ORA-00046 "must specify MLS_MAPFILE with MLS_LISTLABELS"

To create the label mapping file automatically, perform the following steps:

1.	Start the Import procedure with the special MLS options:  MLS_LISTLABELS and MLS_MAPFILE.

IMP / <export_filename> MLS_LISTLABELS=Y

MLS_MAPFILE=<filename>.map

If you do not specify an extension for the filename, Oracle automatically provides a .map extension.

Oracle generates the label mapping file, scans the export file, lists the source labels in the label mapping file, and exits from the Import utility. No data is loaded.

2.	Edit the label mapping file using your operating system's file editor.

3.	Enter a valid target label for each source label. The label mapping file will contain the source label, which will have the following format:

'<source label>'

Define the target label for each source label. For example:

'<source label>' = '<target label>'

Once the label mapping file is created and edited, you can import the data.

Example	The following example illustrates sample output of Import when automatically generating a label mapping file.

imp / file=fmd.dmp mls_listlabels=y mls_mapfile=test.map full=y

Import:  Release 7.1.3.0.0 - Production

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved

Connected to:  Trusted Oracle7 Server Release 7.1.3.0.0 - Production Release With the distributed, parallel query and Parallel Server options PL/SQL Release 2.1.3.0.0 - Production

Export file created by EXPORT:V07.01.03

Writing all labels to DISK$DEV22:[JGB1.TEST]TEST.MAP

Import terminated successfully without warnings.

Example	The following example illustrates sample output of Import when a label mapping file is not specified. The import terminates unsuccessfully.

imp / file=fmd.dmp mls_listlabels=y

Import:  Release 7.1.3.0.0 - Production

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved

Connected to:  Trusted Oracle7 Server Release 7.1.3.0.0 - Production Release With the distributed, parallel query and Parallel Server options PL/SQL Release 2.1.3.0.0 - Production

Export file created by EXPORT:V07.01.03

IMP-00046:  must specify MLS_MAPFILE with MLS_LISTLABELS

IMP-00000:  Import terminated unsuccessfully

 Importing Data

To import labeled data, perform the following steps.

1.	Make sure that you have a current label mapping file.

2.	Start the Import procedure with the export file and the map file name specified.

IMP / <export_filename> MLS_MAPFILE=<filename>.map

The MLS_LISTLABELS option need not be specified because the default is N (no). Do not specify MLS_LISTLABELS = Y (yes), unless you want Import to regenerate the label mapping file.

At this point, Import will import the data with labels that are valid on the target database. If the label mapping file contains a source label that is not valid on the target database and that does not have a corresponding valid target label, then that data and all associated data will not import.

Example	The following example illustrates sample output of Import using a label mapping file.

imp / file=fmd.dmp mls_mapfile=test.map full=y mls=y

Import:  Release 7.1.3.0.0 - Production

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved

Connected to:  Trusted Oracle7 Server Release 7.1.3.0.0 - Production Release With the distributed, parallel query and Parallel Server options PL/SQL Release 2.1.3.0.0 - Production

Export file created by EXPORT:V07.01.01

. importing SYSTEM's objects into SYSTEM

. importing SCOTT's objects into SCOTT

. . importing table "BONUS"                       0 rows imported

. . importing table "DEPT"                        4 rows imported

. . importing table "EMP"                        14 rows imported

. . importing table "SALGRADE"                    5 rows imported

. importing TKZTX1's objects into TKZTX1

. . importing table "MT1"                         3 rows imported

. . importing table "MT2"                         3 rows imported

. . importing table "MT3"                         2 rows imported

. importing SYSTEMS's objects into SYSTEM

. importing TKZTX1's objects into TKZTX1

Import terminated successfully without warnings.

Example	The following example illustrates sample output when running Import interactively.

imp /

Import:  Release 7.1.3.0.0 - Production

Copyright (c) Oracle Corporation 1979, 1994. All right reserved

Connected to:  Trusted Oracle7 Server Release 7.1.3.0.0 - Production Release With the distributed, parallel query and Parallel Server options PL/SQL Release 2.1.3.0.0 - Production

Import file:  DISK$DEV22:[JGB1.TEST]EXPDAT.DMP > fmd.dmp

Enter insert buffer size (minimum is 4096) 30730>

Export file created by EXPORT:V07.01.01

MLS Import mapping file (default is none) map2.map

List mapping relations (yes/no): no > yes

'SECRET' = 'UNCLASSIFIED:ALPHA'

'TOP_SECRET:ALPHA,BETA' = 'TOP_SECRET:BETA'

'UNCLASSIFIED:ALPHA,BETA' = 'UNCLASSIFIED:BETA'

Abort Import (yes/no): no > no

List contents of import file only (yes/no): no >

Ignore create error due to object existence (yes/no): yes >

Import grants (yes/no) yes >

Import entire export file (yes/no): yes >

. importing SYSTEM's objects into SYSTEM

. importing SCOTT's objects into SCOTT

. . importing table "BONUS"                        0 rows imported

. . importing table "DEPT"                         4 rows imported

. . importing table "EMP"                         14 rows imported

. . importing table "SALGRADE"                     5 rows imported

. importing TKZTX1's objects into TKZTX1

. . importing table "MT1"                          3 rows imported

. . importing table "MT2"                          3 rows imported

. . importing table "MT3"                          2 rows imported

. importing SYSTEMS's objects into SYSTEM

. importing TKZTX1's objects into TKZTX1

Import terminated successfully without warnings.

 Trusted Oracle7 Messages and Codes

The following new messages are associated with the label mapping feature.

ORA-00045	"unexpected character found on line %lu in mapfile"

ORA-00046	"must specify MLS_MAPFILE with MLS_LISTLABELS"

ORA-00047	"label string larger than 255 characters found at line

%lu in mapfile"

ORA-00048	"the MLS_LISTLABELS option cannot be used with this

export file"


APPENDIX A. REFERENCE

This appendix lists new reference information for Oracle7 release 7.1, including:

•	initialization parameters

•	dynamic performance tables

•	data dictionary tables

•	SQL syntax

 ________________________________________

Initialization Parameters

This section lists new or changed initialization parameters. Some initialization parameters are available only in release 7.1, while other parameters are changed or new in release 7.0 but were not documented in the Oracle7 manuals. The "Release" information for each parameter states which release of the product that each parameter is available.

CACHE_SIZE_THRESHOLD

Default value:	0.1*DB_BLOCK_BUFFERS

Multiple instances:	should have the same value

Release:	7.1

Specifies the maximum size of a cached partition of a table split among the caches of multiple instances. If the partition is larger than the value of this parameter, the table is not split among the instances' caches. The default value of this parameter is 1/10 the number of database blocks in the buffer cache.

CLOSE_CACHED_OPEN_CURSORS

Default value:	FALSE

Range of values:	TRUE/FALSE

Release:	7.1

This parameter controls whether cursors opened and cached in memory by PL/SQL are automatically closed at each COMMIT. A value of FALSE signifies that cursors opened by PL/SQL are held open so that subsequent executions need not open a new cursor. If PL/SQL cursors are reused frequently, setting the parameter to FALSE can cause subsequent executions to be faster.

A value of TRUE causes open cursors to be closed at each COMMIT or ROLLBACK. The cursor can then be reopened as needed. If cursors are rarely reused, setting the parameter to TRUE frees memory used by the cursor when the cursor is no longer in use.

COMPATIBLE

Default value:	Release Dependent

Range of values:	Default Version to Current Version

Multiple instances:	Must have the same value

Release:	7.0

This parameter allows you to use a new release while at the same time guaranteeing backwards compatibility with an earlier version (in case it becomes necessary to retreat to the earlier version). The version that the Oracle Server must remain compatible with is specified by this parameter. In order to maintain compatibility, some features of the current version may be restricted.

The default value is the earliest version with which compatibility can be guaranteed.

DB_DOMAIN

Default value:	WORLD

Range of values:	any legal string of name components, separated by periods and up to 128 characters long, including periods (see valid characters below)

Multiple instances:	must have the same value

Release:	7.0

This parameter specifies the extension components of a global database name, consisting of valid identifiers, separated by periods. Specifying DB_DOMAIN as a unique string for every database is highly recommended.

For example, this parameter allows one department to create a database without worrying that it might have the same name as a database created by another department. If one sales department's DB_DOMAIN = "JAPAN.ACME.COM", then their "SALES" database (SALES.JAPAN.ACME.COM) is uniquely distinguished from another database with DB_NAME = "SALES" but with DB_DOMAIN = "US.ACME.COM".

The following characters are valid in a database domain name:

•	alphabetic characters

•	numbers

•	underscore (_)

•	sharp (#)

DBLINK_ENCRYPT_LOGIN

Default:	FALSE

Range of values:	TRUE/FALSE

Release:	7.1

Signifies whether attempts to connect to other Oracle Servers through database links should use encrypted passwords. When you attempt to connect to a database using a password, Oracle encrypts the password before sending it to the database. If the DBLINK_ENCRYPT_LOGIN parameter is TRUE and the connection fails, Oracle does not reattempt the connection. If this parameter is FALSE, Oracle reattempts the connections using an unencrypted version of the password.

For more information about encrypting passwords when connecting, refer to Chapter 5 "Secure Database Connections".

LOG_CHECKPOINTS_TO_ALERT

Default:	NO

Range of values:	YES, NO

Release:	7.0

This parameter allows you to log your checkpoints to the alert file. This is useful to determine if checkpoints occur at the desired frequency.

MAX_COMMIT_PROPAGATION_DELAY

Default:	90000

Range of values:	0 .. 90000

OK to change?	No

Multiple instances:	must have identical values

Release:	7.0

This initialization parameter should not be changed except under a limited set of circumstances specific to the Parallel Server. This parameter indicates the maximum amount of time allowed before the System Change Number (SCN) held in the SGA of an instance is refreshed by LGWR. Units are in hundredths of seconds. Under very unusual circumstances involving rapid updates and queries of the same data from different instances, the SCN may not be refreshed in a timely manner. Setting the parameter to zero causes the SCN to be refreshed immediately after a commit. The default value of 90,000 hundredths of a second, or fifteen minutes, is an upper bound that allows the preferred existing high performance mechanism to remain in place.

NLS_DATE_FORMAT

Default value:	derived

Range of values:	any valid date format mask but not exceeding a fixed length (see below)

Release:	7.0

Defines the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY. The value of this parameter can be any valid date format mask, and the value must be surronded by double quotes. For example:

NLS_DATE_FORMAT = "MM/DD/YYYY"

The value of this parameter is stored in the tokenized internal date format. Each format element occupies two bytes and each string occupies the number of bytes in the string plus a terminator byte. Also, the entire format mask has a two-byte terminator. For example, "MM/DD/YY" occupies 12 bytes internally because there are three format elements, two one-byte strings (the two slashes), and the two-byte terminator for the format mask. The tokenized format for the value of this parameter cannot exceed 24 bytes.

OPTIMIZER_MODE

Default value:	CHOOSE

Range of values:	RULE, CHOOSE, FIRST_ROWS, ALL_ROWS

Release:	7.0

When set to RULE, this parameter causes rule-based optimization to be used unless hints are specified in the query. When set to CHOOSE, the optimizer uses the cost-based approach for a SQL statement if there are statistics in the dictionary for at least one table accessed in the statement. (Otherwise, the rule-based approach is used.)

You can set the goal for cost based optimization by setting this parameter to FIRST_ROWS or ALL_ROWS. FIRST_ROWS causes the optimizer to choose execution plans which minimize response time. ALL_ROWS causes the optimizer to choose execution plans which minimize total execution time. The goal of cost based optimization can also be set within a session by using the ALTER SESSION command. Refer to the Oracle7 Server SQL Language Reference Manual for more information about the ALTER SESSION command.

PARALLEL_DEFAULT_MAX_INSTANCES

Default value:	O/S dependent

Range of values:	0 .. number of instances

Multiple instances:	should have the same value

Release:	7.1

Specifies the default number of instances to split a table across for parallel query processing. The value of this parameter is used if the INSTANCES DEFAULT is specified in the PARALLEL clause of a table's definition.

PARALLEL_DEFAULT_MAX_SCANS

Default value:	O/S dependent

Range of values:	0 .. unlimited

Multiple instances:	should have the same value

Release:	7.1

The maximum number of query servers that can be used by default for a query. This parameter is only used if there is no numeric value specified in a PARALLEL hint or in the PARALLEL clause of the table's definition. This limits the number of query servers that are selected by default when the value of the PARALLEL_DEFAULT_SCANSIZE parameter is used by a query coordinator.

PARALLEL_DEFAULT_SCANSIZE

Default value:	O/S dependent

Range of values:	0 .. O/S dependent

Multiple instances:	should have the same value

Release:	7.1

Used with an estimate of a table's size to determine the default number of query servers needed for a particular table. The number of blocks in the table are divided by the value of this parameter and the result is the number of query servers to use for the query. This number cannot exceed the value of the PARALLEL_DEFAULT_MAX_SCANS parameter.

PARALLEL_MAX_SERVERS

Default value:	O/S dependent

0 .. 100

Multiple instances:	Each instance must either have a value of 0 or the same value as the other instances.

Release:	7.1

Maximum number of query server or parallel recovery processes for an instance.

PARALLEL_MIN_SERVERS

Default value:	0

Multiple instances:	0 .. PARALLEL_MAX_SERVERS

can have different values

Release:	7.1

Minimum number of query server processes for an instance. This is also the number of query server processes Oracle creates when the instance is started.

PARALLEL_SERVER_IDLE_TIME

Default value:	O/S dependent

Range of values:	0 .. unlimited

Multiple instances:	can have different values

Release:	7.1

The amount of idle time after which Oracle terminates a query server process. This value is expressed in minutes.

PRE_PAGE_SGA

Default:	NO

Range of values:	NO, YES

OK to change?	No

Release:	7.0

When set to YES, this parameter touches all the SGA pages, causing them to be brought into memory. As a result, it increases instance startup time and user login time, but it can reduce the number of page faults that occur shortly thereafter. The reduction in page faults allows the instance to reach its maximum performance capability quickly rather than an incremental build up. It is most useful on systems that have sufficient memory to hold all the SGA pages without degrading performance in other areas.

RECOVERY_PARALLELISM

Default value:	O/S dependent

Range of values:	O/S dependent, but cannot exceed PARALLEL_MAX_SERVERS

Release:	7.1

Specifies the number of processes to participate in instance or media recovery. A value of zero or one indicates that recovery is to be performed serially by one process.

REMOTE_LOGIN_PASSWORDFILE

Default value:	NONE

Range of values:	NONE/SHARED/EXCLUSIVE

Multiple instances:	should have the same value

Release:	7.1

Specifies whether Oracle checks for a password file and how many databases can use the password file. Setting the parameter to NONE signifies that Oracle should ignore any password file (and therefore privileged users must be authenticated by the operating system). Setting the parameter to EXCLUSIVE signifies that the password file can only be used by one database and the password file can contain names other than SYS and INTERNAL. Setting the parameter to SHARED allows more than one database to use a password file. However, the only users recognized by the password file are SYS and INTERNAL.

For more information about secure connections for privileged users, refer to Chapter 5 "Secure Database Connections".

SESSION_CACHED_CURSORS

Default:	0

Range of values;	0 .. O/S dependent

Multiple instances:	can have different values

Release:	7.1

This parameter allows you to specify the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and need not reopen the cursor. The value of this parameter is the maximum number of session cursors to keep in the session cursor cache.

SNAPSHOT_REFRESH_INTERVAL

Default:	60 (1 minute)

Range of values:	1 .. 3600 seconds (1 second to 60 minutes)

Multiple instances:	can have different values

Release:	7.0

This parameter sets the interval between wake-ups for the snapshot refresh process(es) on the instance.

SNAPSHOT_REFRESH_KEEP_CONNECTIONS

Default:	FALSE

Range of values:	TRUE, FALSE

Multiple instances:	can have different values

Release:	7.0

This parameter specifies whether the snapshot refresh process(es) should keep remote database connections after all snapshots are refreshed. If set to FALSE, remote database connections that are made to refresh snapshots are closed after all the refreshes are complete.

SNAPSHOT_REFRESH_PROCESSES

Default:	0

Range of values:	0 .. 10

Multiple instances:	can have different values

Release:	7.0

This parameter sets the number of snapshot refresh processes per instance. If you wish to have your snapshots updated automatically, you must set this parameter to a value of one or higher. One snapshot refresh process will usually be sufficient unless you have many snapshots that refresh simultaneously.

SQL92_SECURITY

Default value:	FALSE

Range of values:	TRUE, FALSE

Release:	7.1

Specifies whether table-level SELECT privileges are required to execute an update or delete that references table column values.

 ________________________________________

Data Dictionary Views and Dynamic Performance Tables for Release 7.1

This section lists new and changed V$ and data dictionary views for release 7.1.

 DBA_SEGMENTS

The columns INITIAL_EXTENT and NEXT_EXTENT are now expressed in bytes. In previous releases of Oracle these values were expressed in blocks.

 DBA_TABLESPACES

The STATUS column of this view can now have the value READ_ONLY.

 PLAN_TABLE

The OBJECT_NODE and OTHER columns of the PLAN table have changed to include information about parallel query execution. If you created PLAN_TABLE before 7.1.2, you must rerun the UTLXPLAN.SQL script to re-create PLAN_TABLE. EXPLAIN PLAN in 7.1 is compatible with previous versions of the PLAN_TABLE, but does not place output into the new columns since they do not exist in the older versions of the table.

 PRODUCT_COMPONENT_VERSION

This is a view on the V$VERSION view which breaks the strings in V$VERSION into separate columns.

PRODUCT	name of the product included with this release of the Oracle Server (for example, PL/SQL).

VERSION	version of the product currently installed (for example, 2.1.3.0.0).

STATUS	release status for the product (for example, Production, or Developer's Release)

 USER_SEGMENTS

The columns INITIAL_EXTENT and NEXT_EXTENT are now expressed in bytes. In previous releases of Oracle, these values were expressed in blocks.

 USER_TABLESPACES

The STATUS column of this view can now have the value READ_ONLY.

 V$COMPATIBILITY

This view provides data that is stored in shared memory for the instance. It includes data from the control files, datafiles, and log files, as well as other types that have been ready by this instance from the compatibility segment.

TYPE_ID	An eight-character internal name for the data.

RELEASE	The compatibility release number. Software earlier than this will not be able to interpret the data correctly.

DESCRIPTION	A text description of the purpose of this type.

 V$COMPATSEG

Current data stored in the (on-disk) compatibility segment. Records changes made in the database that may be incompatible with older software. It includes only changes that are made within the database, such as the compatibility segment itself and new data stored in the dictionary to support new features.

TYPE_ID	An eight-character internal name for the data.

RELEASE	The compatibility release number. Software earlier than this will not be able to interpret the data correctly.

UPDATED	The software release that was running when this entry was last changed.

 V$CONTROLFILE

This new view lists control file information.

STATUS	This field is reserved for future use.

NAME	The name of the control file.

 V$DATAFILE

This view has a new column named ENABLED, which can have one of the following values: READ_WRITE, READ_ONLY, or DISABLED.

A file that is online but disabled can only be accessed to apply outstanding save undo information.

 V$LOCK

This view has some new and changed lock types. This view contains information about locks and resources. This view does not include DDL locks.

ADDR	Address of the lock state object.

KADDR	Address of the lock.

SID	Identifier of process holding the lock.

TYPE	Resource type (see following table).

ID1	Resource identifier #1

ID2	Resource identifier #2.

LMODE	Lock mode held:  1 (null), 2 (row share), 3 (row exclusive), 4 (share),  5 (share row exclusive),  6 (exclusive)

REQUEST	Lock mode requested (same values as LMODE)

The following locks are obtained by user applications. Any process which is blocking others is likely to be holding one of these locks:

User Lock Type	Description

------------------------------------------------------------------------

TM	DML enqueue lock

TX	Transaction enqueue lock

UL	User supplied lock

The following system locks are held for extremely short periods of time:

System Lock Type	Description

------------------------------------------------------------------------

BL	Buffer hash table instance lock.

CF	Control transaction global enqueue (ID1=0).

CF	Control File shared info block (ID1=1).

CI	Cross instance function invocation instance locks.

CU	Cursor binding.

DF	Data file instance lock.

DL	Direct load parallel index create lock.

DM	Mount/startup database primary/secondary instance

lock.

DR	Distributed recovery process lock. Used to prevent more

than one RECO process active.

DX	Distributed transaction entry lock. Not multi-instance.

FS	File set enqueue, used to coordinate dropping files that

may be in use elsewhere (ID2=0).

FS	Dictionary Check enqueue (ID2=1).

IN	Instance number.

IR	Instance recovery serialization global enqueue lock.

IS	Instance state (ID2=0).

IS	Background instance state (ID2=1).

IS	Cache state (ID2=3).

IV	Library cache invalidation instance lock.

KK	Log switch Kick lock.

LA..LP	Library cache lock instance lock (A..P=namespace).

MM	Mount definition global enqueue lock.

MR	Media recovery enqueue.

PF	Password files enqueue.

PR	Process startup enqueue; single-threads starting up

background/MTS/parallel-query/parallel-recovery

processes.

PA..PZ	Library cache pin instance lock (A..Z=namespace).

QA..QZ	Row cache instance lock (A..Z=cache).

RT	Redo thread global enqueue lock.

SC	System commit number instance lock.

SM	SMON lock.

SN	Sequence number instance lock.

SQ	Sequence number enqueue lock.

ST	Space transaction enqueue lock.

SV	Sequence number value lock.

TA	Generic enqueue lock.

TS	Temporary segment enqueue lock (ID2=0).

TS	New block allocation enqueue lock (ID2=1).

TT	Temporary table enqueue lock.

UN	User name lock.

WL	Being-written redo log instance lock.

 V$NLS_VALID_VALUES

This view allows you to determine which NLS settings are available for language, territory, character set, and collating/sorting convention.

PARAMETER	Possible values are `LANGUAGE', `TERRITORY', `CHARACTERSET', and `SORT'.

VALUE	Contains the valid and available settings for the corresponding parameter that are supported by this version of the server.

 V$OPTION

This new view lists the available options for the current release.

PARAMETER	name of the option

VALUE	TRUE if the option is installed for this database, FALSE if the option is not installed.

 V$PQ_SESSTAT

This new view lists session statistics for parallel queries.

STATISTIC	name of the statistic.

LAST_QUERY	the value of the statistic for the last query.

SESSION_TO	the value of the statistic for the entire session to this point in time.

 V$PQ_SLAVE

This new view lists statistics for each of the active parallel query servers on an instance.

SLAVE_NAME	name of the parallel query server.

STATUS	the current status of the parallel query server (BUSY or IDLE).

SESSIONS	the number of sessions that have used this parallel query server.

IDLE_TIME_ CUR	the amount of time spent idle while processing statements in the current session.

BUSY_TIME_ CUR	the amount of time spent busy while processing statements in the current session.

CPU_SECS_CUR	the amount of CPU time spent on the current session.

MSGS_SENT_ CUR	the number of messages sent while processing statements for the current session.

MSGS_RCVD_ CUR	the number of messages received while processing statements for the current session.

ILDE_TIME_ TOTAL	the total amount of time this query server has been idle.

BUSY_TIME_ TOTAL	the total amount of time this query server has been active.

CPU_SECS_ TOTAL	the total amount of CPU time this query server has used to process statements.

MSGS_SENT_ TOTAL	the total number of messages this query server has sent.

MSGS_RCVD_ TOTAL	the total number of messages this query server has received.

 V$PQ_SYSSTAT

This new view lists system statistics for parallel queries.

STATISTIC	name of the statistic.

VALUE	the value of the statistic.

 V$PWFILE_USERS

This new view lists users who have been granted SYSDBA and SYSOPER privileges as derived from the password file.

USERNAME	The name of the user that is contained in the password file.

SYSDBA	If the value of this column is TRUE, the user can connect with SYSDBA privileges.

SYSOPER	If the value of this column is TRUE, the user can connect with SYSOPER privileges.

 ________________________________________

Dynamic Performance Tables and Data Dictionary Views for Release 7.0

This section lists new and changed V$ views and data dictionary views for release 7.0 that were not included in the Oracle7 documentation.

 ALL_CONSTRAINTSDBA_CONSTRAINTSUSER_CONSTRAINTS

These views have a changed description for the DELETE_RULE column. The DELETE_RULE column can have the following values:

•	NULL indicating a non-referential constraint

•	CASCADE indicating a delete cascade referential constraint

•	NO ACTION indicating any other referential constraint type

 NLS_INSTANCE_PARAMETERS

This view is relevant only if you use National Language Support. It provides a list of the instance-based NLS settings. There are three levels of NLS settings. This view along with NLS_SESSION_PARAMETERS and NLS_DATABASE_PARAMETERS allows you to clearly see which NLS parameter is active. NLS_INSTANCE_PARAMETERS shows the default values of the NLS parameters in the initialization file of your current instance.

PARAMETER	The name of the NLS parameter. It can be any of the following: NLS_LANGUAGE,  NLS_TERRITORY,  NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_NUMERIC_CHARACTERS, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE,  NLS_SORT

VALUE	The default value of the NLS parameter specified in the initialization file.

 NLS_DATABASE_PARAMETERS

This view is relevant only if you use National Language Support. It provides a list of the database-based NLS settings. There are three levels of NLS settings. This view along with NLS_SESSION_PARAMETERS and NLS_INSTANCE_PARAMETERS allows you to clearly see which NLS parameter is active. NLS_DATABASE_PARAMETERS shows the values of NLS parameters in effect when the database was created.

PARAMETER	The name of the NLS parameter. It can be any of the following: NLS_LANGUAGE,  NLS_TERRITORY,  NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_NUMERIC_CHARACTERS, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE,  NLS_SORT

VALUE	The value of the NLS parameter when the database was created.

 NLS_SESSION_PARAMETERS

This view is relevant only if you use National Language Support. It provides a list of the session-based NLS settings. There are three levels of NLS settings. This view along with NLS_INSTANCE_PARAMETERS and NLS_DATABASE_PARAMETERS allows you to clearly see which NLS parameters are active for the session.

PARAMETER	The name of the NLS parameter. It can be any of the following: NLS_LANGUAGE,  NLS_TERRITORY,  NLS_CURRENCY,                    NLS_ISO_CURRENCY, NLS_NUMERIC_CHARACTERS, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE,  NLS_SORT

VALUE	The current value of the NLS parameter for the session.

 V$DBLINK

This view describes all open database links (links with IN_TRANSACTION = `YES'). These database links must be committed or rolled back before being closed.

DB_LINK  	VARCHAR2	Name of the database link.

OWNER_ID	NUMBER	Owner of the database link UID.

LOGGED_ON	VARCHAR2	Whether the database link is cur-

rently logged on.

HETEROGE-	VARCHAR2	Whether the database link is heter-

NEOUS	ogeneous.

OPEN_CURSORS	NUMBER	Whether there are open cursors

for the database link.

IN_	VARCHAR2	Whether the database link is cur-

TRANSACTION	rently in a transaction.

UPDATE_SENT	VARCHAR2	Whether there has been an update

on the database link.

COMMIT_	NUMBER	Commit point strength of the

POINT_	transactions on the database link.

STRENGTH

 V$LOG

The column STATUS has been added to the V$LOG view. The STATUS column can have the following values:

•	UNUSED indicates the online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.

•	CURRENT indicates this is the current redo log. This implies that the redo log is active. The redo log could be open or closed.

•	ACTIVE indicates the log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It might or might not be archived.

•	INACTIVE indicates the log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not have already been archived.

 V$MTS

This view gives information for tuning the multi-threaded server.

MAXIMUM_	NUMBER	The maximum number of connec-

CONNECTIONS 	tions each dispatcher can support.

This value is determined at start-

up time using SQL*Net constants

and other port-specific informa-

tion.

SERVERS_	NUMBER	The total number of multi-

STARTED	threaded servers started since the

instance started (but not including

those started during startup).

SERVERS_	NUMBER	The total number of multi-

TERMINATED	threaded servers stopped by

Oracle since the instance started.

SERVERS_	NUMBER	The highest number of servers

HIGHWATER	running at one time since the

instance started. If this value

reaches the value set for the

MTS_MAX_SERVERS initializa-

tion parameter, consider raising

the value of MTS_MAX_SERVERS>.

TOTAL_	NUMBER	Total number of timeouts for the

TIMEOUTS	event.

 V$SESSION

This view gives session information for each current session. The following lists additions and modifications to the command numbers described in the Oracle7 Server Administrator's Guide.

Command Number	Command

------------------------------------------------------------------------

8	DROP CLUSTER

13	CREATE SEQUENCE

14	ALTER SEQUENCE

16	DROP SEQUENCE

23	VALIDATE INDEX

24	CREATE PROCEDURE

25	ALTER PROCEDURE

32	CREATE DATABASE LINK

33	DROP DATABASE LINK

47	PL/SQL EXECUTE

48	SET TRANSACTION

49	ALTER SYSTEM SWITCH LOG

50	EXPLAIN

51	CREATE USER

52	CREATE ROLE

53	DROP USER

54	DROP ROLE

55	SET ROLE

56	CREATE SCHEMA

57	CREATE CONTROL FILE

58	ALTER TRACING

59	CREATE TRIGGER

60	ALTER TRIGGER

61	DROP TRIGGER

62	ANALYZE TABLE

63	ANALYZE INDEX

64	ANALYZE CLUSTER

65	CREATE PROFILE

66	DROP PROFILE

67	ALTER PROFILE

68	DROP PROCEDURE

70	ALTER RESOURCE COST

71	CREATE SNAPSHOT LOG

72	ALTER SNAPSHOT LOG

73	DROP SNAPSHOT LOG

74	CREATE SNAPSHOT

75	ALTER SNAPSHOT

76	DROP SNAPSHOT

79	ALTER ROLE

85	TRUNCATE TABLE

86	TRUNCATE CLUSTER

88	ALTER VIEW

91	CREATE FUNCTION

92	ALTER FUNCTION

93	DROP FUNCTION

94	CREATE PACKAGE

95	ALTER PACKAGE

96	DROP PACKAGE

97	CREATE PACKAGE BODY

98	ALTER PACKAGE BODY

99	DROP PACKAGE BODY

 V$SESSION_EVENT

This view gives wait statistics for each session and each event.

SID 	NUMBER	Session identification number.

EVENT	VARCHAR2	Event description.

TOTAL_WAITS	NUMBER	The total number of waits for the

event.

TIME_WAITED	NUMBER	Total time waited for event in hun-

dredths of seconds.

AVERAGE_WAIT	NUMBER	Average time waited for event in

hundredths of seconds.

TOTAL_	NUMBER	Total number of timeouts for the

TIMEOUTS	event.

Note that the TIME_WAITED and AVERAGE_WAIT columns will contain a value of zero on those platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you wish this column to reflect true wait times, you must set TIMED_STATISTICS to TRUE in the parameter file. Please remember that doing this will have a small negative effect on system performance.

 V$SESSION_WAIT

Note that the WAIT_TIME column contains an arbitrarily large value on platforms that do not support a fast timing mechanism. If you are running on one of these platforms and you wish this column to reflect true wait times, you must set the TIMED_STATISTICS parameter to TRUE. Remember that doing this has a small negative effect on system performance.

The datatypes for the P1, P2, and P3 columns are NUMBER and not VARCHAR2.

 V$SYSTEM_EVENT

This view is a systemwide collation of the per-session event statistics.

EVENT	VARCHAR2	Event description.

TOTAL_WAITS	NUMBER	The total number of waits for the

event.

TIME_WAITED	NUMBER	Total time waited for event in hun-

dredths of seconds.

AVERAGE_WAIT	NUMBER	Average time waited for event in

hundredths of seconds.

Note that the TIME_WAITED and AVERAGE_WAIT columns will contain a value of zero on those platforms which do not support a fast timing mechanism. If you are running on one of these platforms and you wish this column to reflect true wait times, you must set TIMED_STATISTICS to TRUE in the parameter file. Please remember that doing this will have a small negative effect on system performance.

 ________________________________________

SQL Syntax

This section lists new SQL syntax.

 ALTER CLUSTER

This command has a PARALLEL clause and a CACHE clause clause to support the parallel query option. See page 6 - 15 for more information on this command.

 ALTER DATABASE

This command has a RESET COMPATIBILITY option for compatibility control.

You must have ALTER DATABASE system privilege and your instance must have the database open for you to issue this command. See page 1-5 for more information on this command option.

The RECOVER option of this command has changed to include a PARALLEL clause for use with the parallel recovery feature. See page 4 - 5 for more information on this command option.

 ALTER SESSION

This command has a new SET FLAGGER option to support flagging of SQL extensions that go beyond the SQL92 standard for SQL. The SET FLAGGER option has four additional options: entry, intermediate, full, and off. See page 2-3 for more information on this command option.

This command also has a new option for closing cached cursors used by PL/SQL. Using the ALTER SESSION command with this option overrides the initialization parameter CLOSE_CACHED_OPEN_CURSORS for your current session. For more information about the initialization parameter, see page A - 2.

This command also has a new option for specifying the size of the session cursor cache. The syntax is:

ALTER SESSION SET SESSION_CACHED_CURSORS = integer

The integer specified can be any positive integer, but the maximum value is operating-system dependent.

See the Oracle7 Server SQL Language Reference Manual for more information on using the ALTER SESSION command.

 ALTER TABLE

This command has a PARALLEL clause and a CACHE clause to support the parallel query option. See page 6 - 12 for more information on this command option.

 ALTER TABLESPACE

This command has READ ONLY and READ WRITE options to support read-only tablespaces. See page 3 - 2 for more information about this command option.

 ANALYZE

The ANALYZE command is documented incorrectly in the Oracle7 Server SQL Language Reference Manual. The correct syntax is:

ANALYZE

{  INDEX	[schema.]index

|  TABLE	[schema.]table

|  CLUSTER	[schema.]cluster  }

{  {  COMPUTE STATISTICS

   |  ESTIMATE STATISTICS

[ SAMPLE integer { ROWS | PERCENT } ]

   |  DELETE STATISTICS  }

|  VALIDATE STRUCTURE [ CASCADE ]

|  LIST CHAINED ROWS [ INTO [schema.]table ] }

Note: The placement of the SAMPLE clause is after the ESTIMATE STATISTICS keywords.

 CREATE CLUSTER

This command has a PARALLEL clause and a CACHE clause to support the parallel query option.

See page 6 - 14 for more information on this command option.

 CREATE INDEX

This command has a PARALLEL clause to support the parallel query option. See page 6 - 24 for more information on this command option.

 CREATE TABLE

This command has a PARALLEL clause and a CACHE clause to support the parallel query option. See page 6 - 10 for more information on this command option.

 SELECT

There is new syntax and functionality in the following parts of the SELECT command:

•	SELECT list

•	ORDER BY clause

See page 2 - 2 for more information on these command options.

 SELECT List

Column aliases in the SELECT list can optionally be separated from their expressions by the new AS keyword, as in this example:

SELECT empno, ename AS name

FROM emp

 ORDER BY Clause

The ORDER BY clause can now reference column expression aliases defined in the SELECT list. These column expression aliases effectively rename the SELECT list items for the duration of the expression.

 ________________________________________

Changes to Supplied Packages

The distribution mechanism for supplied packages described in Appendix A of the Oracle7 Server Application Developer's Guide has changed in release 7.1. The public package specifications continue to reside in the DBMS*.SQL files, for example DBMSPIPE.SQL. The package bodies now reside in separate files which follow a similar naming convention to the package specification file. The package body files are generally named PRVT*.SQL, for example PRVTPIPE.SQL.

However, the following packages have public package specifications, but the bodies are stored in binary format, which has the extension .PLB:

•	DBMSSQL.SQL and PRVTSQL.PLB

•	DBMSPEXP.SQL and PRVTPEXP.PLB

•	DBMSJOB.SQL and PRVTJOB.PLB

•	DBMSDFRD.SQL and PRVTDFRD.PLB

•	DBMSSNAP.SQL and PRVTSNAP.PLB

To create a supplied package, you must run both of these file against your database. These scripts should be run automatically for you when you create a new database or upgrade an existing database.

Additional Information:  Refer to your platform-specific Oracle documentation for more information about the location and installation of these packages.

 DBMS_SESSION Package

The DBMS_SESSION supplied package has a new procedure to support the new ALTER SESSION option CLOSE_CACHED_OPEN_CURSORS. The procedure and its arguments are as follows:

 SET_CLOSE_CACHED_OPEN_CURSORS Procedure

procedure set_close_cached_open_cursors  	(close_cursors in boolean);

The procedure takes TRUE or FALSE as an argument and executes the ALTER SESSION SET CLOSE_CACHED_OPEN_CURSORS statement with the given argument. For more information about the effect of setting CLOSE_CACHED_OPEN CURSORS, refer to the description of the initialization parameter on page A - 2.


APPENDIX B. CHANGES TO RELEASE 7.0

This appendix describes features in release 7.0 of the Oracle7 Server and Trusted Oracle7 Server that were omitted from the Oracle7 documentation set. The topics described in this chapter include the following:

•	Changes and additions to the supplied packages

•	Additional information about stored procedures and triggers

•	Changes to SQL syntax

•	Additional security information

•	Additions and modifications to the Trusted Oracle7 Server documentation

 ________________________________________

New Packages and Packaged Procedures

This section describes new procedures and packages introduced in release 7.0. Refer to Appendix A of the Oracle7 Server Application Developer's Guide for additional information about supplied packages.

 DBMS_TRANSACTION.PURGE_MIXED

The PURGE_MIXED procedure is in the DBMS_TRANSACTION supplied package. Manually forcing in-doubt transactions (rather than allowing automatic recovery) can compromise these transactions; some sites may commit while other sites may roll back. The transaction is said to be of mixed outcome. Such inconsistencies cannot be resolved by Oracle, but the Server notes the in-doubt transactions in DBA_2PC_PENDING, where the MIXED column is set to YES. Oracle never deletes information about in-doubt transactions that are of mixed outcome. When the DBA (or application) is sure all inconsistencies resulting from the mixed outcome are resolved, the DBMS_TRANSACTION.PURGE_MIXED procedure can purge the information.

dbms_transaction.purge_mixed(xid IN NUMBER)

Input Parameter:

XID

This must be set to the value of the LOCAL_TRAN_ID column in the DBA_2PC_PENDING table.

 DBMS_UTILITY.NAME_RESOLVE

The NAME_RESOLVE procedure is in the supplied package DBMS_UTILITY. This routine performs name resolution with any necessary synonym translation and authorization checking. It is primarily for internal use, but it can be used to determine if an object is local or remote.

dbms_utility.name_resolve(name IN VARCHAR2,

                          context IN NUMBER,

                          schema OUT VARCHAR2,

                          part1 OUT VARCHAR2,

                          part2 OUT VARCHAR2,

                          dblink OUT VARCHAR2,

                          part1_type OUT NUMBER,

                          object_number OUT NUMBER)

Input Parameters:

Name

The object name to be resolved. This can be in the form:

[[a.]b.]c[@d]

where a, b, and c are SQL identifiers and d is a dblink. Syntax checking is not performed on the dblink (see output parameter DBLINK). a, b, and c may be delimited identifiers and may contain single and multibyte NLS characters.

Note: If a name of the format a.b is specified (where "a" is a package name in the current schema), then this is the name that will be resolved, even if an object exists in schema a with name b. In a planned future enhancement, the context will be used to force the resolution to choose the object in schema a if it is a table or view.

Context

Not currently used, but should be set to 1 for future compatibility.

Output Parameters:

Schema

The object schema name. If no schema is specified in the input NAME, then the schema name is determined by resolving NAME.

PART1

 The first part of the object name. The type of this name is specified by the output PART1_TYPE parameter described below.

PART2

If this is not NULL, then the object is a procedure within the package indicated by PART1.

DBLINK

If this is not NULL, then a dblink is specified in input NAME, or NAME is a synonym and the name resolution found a dblink.

PART1_TYPE

The object's type:

5 = synonym

7 = procedure

8 = function (top level)

9 = package (top level)

If the object type indicates a synonym, then the translation of input NAME found a dblink. If further translation is required, you must call DBMS_UTILITY.NAME_RESOLVE on the remote node indicated by the dblink.

OBJECT_NUMBER

If not NULL, then input NAME was successfully resolved and OBJECT_NUMBER is the resolved object number.

 DBMS_SHARED_POOL Package

The DBMSPOOL.SQL and PRVTSPOOL.SQL procedure scripts create the package specification and package body for a utility package, DBMS_SHARED_POOL. This package allows objects to be kept in shared memory, so that they will not be aged out with the normal LRU mechanism.

The procedures provided with the DBMS_SHARED_POOL package may be useful when loading large PL/SQL objects, such as the STANDARD and DIUTIL packages. When large PL/SQL objects are loaded, users' response time is affected because of the large number of smaller objects that need to be aged out from the shared pool to make room (due to memory fragmentation). In some cases, there may be insufficient memory to load the large objects.

By using the DBMS_SHARED_POOL package and by loading these objects early (before memory fragmentation occurs), the large objects can be kept in memory, instead of aging out with the normal LRU mechanism. This procedure ensures that memory is available and prevents sudden, seemingly inexplicable slowdowns in user response time that occur when they are accessed after aging out.

 Usage Instructions

To use the DBMS_SHARED_POOL package to pin a package, complete the following steps.

1.	Decide which packages/cursors you would like pinned in memory.

2.	Startup the database.

3.	Make a reference to the objects that causes them to be loaded. To pin a package, you can reference a dummy procedure defined in the package, or you can reference a package variable. To pin the cursor allocated for a SQL statement, execute the statement.

4.	Make the call to DBMSPOOL.KEEP to pin it.

This procedure ensures that the object is already loaded; otherwise, pinning may not be very useful. It also ensures that your system has not run out of the shared memory before the object is loaded. Finally, by pinning the object early in the life of the instance, this procedure prevents the memory fragmentation that could result from pinning a large chunk of memory in the middle of the shared pool.

 Syntax

The procedures provided with the DBMS_SHARED_POOL package are described below.

 DBMS_SHARED_POOL.SIZES

This procedure shows the objects in the shared pool that are larger than the specified size.

dbms_shared_pool.sizes(minsize IN NUMBER)

Input Parameter:

minsize

Display objects in shared pool larger than this size, where size is measured in kilobytes.

Output Parameters:

To display the results of this procedure, before calling this procedure issue the following command using SQL*DBA or SQL*Plus:

SET SERVEROUTPUT ON SIZE minsize

You can use the results of this command as arguments to the KEEP or UNKEEP procedures.

Example	To show the objects in the shared pool that are larger than 100 you would issue the following SQL*DBA or SQL*Plus commands:

SQL> SET SERVEROUTPUT ON SIZE 100

SQL> EXECUTE DBMS_SHARED_POOL.SIZE(100);

 DBMS_SHARED_POOL.KEEP

This procedure lets you keep an object in the shared pool. This procedure may not be supported in future releases.

dbms_shared_pool.keep(object IN VARCHAR2,

                      [type IN CHAR DEFAULT P])

Input Parameters:

object

Either the parameter name or the cursor address of the object that you want kept in the shared pool. This value is the value displayed when you call the SIZES procedure.

type

The type of the object that you want kept in the shared pool. The types recognized are listed below.

P	the object is a procedure

C	the object is a cursor

 DBMS_SHARED_POOL.UNKEEP

This procedure allows an object that you have requested to be kept in the shared pool to now be aged out of the shared pool. This procedure may not be supported in the future.

dbms_shared_pool.unkeep(object IN VARCHAR2,

                      [type IN CHAR DEFAULT P])

Input Parameters:

object

Either the parameter name or the cursor address of the object that you no longer want kept in the shared pool. This value is the value displayed when you call the SIZES procedure.

type

The type of the object that you want aged out of the shared pool. The types recognized are listed below.

P	the object is a procedure

C	the object is a cursor

 ________________________________________

Changes to Supplied Packages

This section contains information about supplied packages that was omitted from the Oracle7 Server documentation set.

 DBMS_SESSION.SET_ROLE

Stored procedures cannot call the DBMS_SESSION.SET_ROLE supplied procedure. It can only be called from an anonymous block. This restriction prevents a stored procedure from changing its security domain during its execution. A stored procedure executes under the security domain of the creator of the procedure. This restriction should be added to the list of restrictions on page A-2 of the Oracle7 Server Application Developer's Guide. The following text further clarifies the use of the SET_ROLE procedure.

DBMS_SESSION.SET_ROLE is only callable from anonymous PL/SQL blocks. Since PL/SQL does the security check on SQL when the block is compiled, SET_ROLE will not affect the security role (that is, will not affect the roles enabled) for embedded SQL statements or procedure calls.

For example, if you have a role named ACCT that has been granted privileges that allow you to select from table FINANCE in the JOE schema, the following block will fail:

DECLARE

n NUMBER

BEGIN

SYS.DBMS_SESSION.SET_ROLE('acct');

SELECT empno INTO n FROM JOE.FINANCE;

END;

/

This block fails because the security check that verifies that you have the SELECT privilege on table JOE.FINANCE happens at compile time. At compile time, you do not have the ACCT role enabled yet. The role is not enabled until the block is executed.

The DBMS_SQL package provided with release 7.1, however, is not subject to this restriction. When using this package, the security checks occur at run time. Thus, a call to SET_ROLE would affect the SQL executed using calls to the DBMS_SQL package. The following block would therefore be successful.

DECLARE

n NUMBER

BEGIN

SYS.DBMS_SESSION.SET_ROLE('acct');

SYS.DBMS_SQL.PARSE

('SELECT empno INTO n FROM JOE.FINANCE');

...

<other calls to SYS.DBMS_SQL>

...

END;

/

 Enabling the Server Output Option

The syntax for enabling the server output option in SQL*DBA or SQL*Plus follows.

SET SERVEROUTPUT ON

You should correct this on page 7-15 and page A-25 of the Oracle7 Server Application Developer's Guide.

 Database Alerters

The following restriction should be added to page A-15 of the Oracle7 Server Application Developer's Guide:

Because database alerters issue COMMITs, they cannot be used with SQL*Forms. For more information on restrictions on calling stored procedures while SQL*Forms (Runform) is active, refer to Appendix C.

 Trusted Oracle7 and Alerters

Alerters have different behavior in DBMS MAC mode than they do in OS MAC mode or standard Oracle7. When an alert is signaled, it sends a message to a pipe. In DBMS MAC mode, this message is labeled at the session label of either a trigger or a user, depending on how the alert was signaled. Because alert messages may be at different labels, the process that polls and receives the alert message must have the correct label and privileges to access the messages in the pipe. See Appendix A of the Oracle7 Server Application Developer's Guide for more information about using alerters.

In OS MAC mode, alerters behave the same as they do in standard Oracle7.

 Trusted Oracle7 and Pipes

Pipes have different behavior in DBMS MAC mode than they do in OS MAC mode or standard Oracle7. In DBMS MAC mode, a pipe is visible to all sessions allowed to access it. The actual messages in the pipe are labeled at the sender's DBMS session label. In addition, because the receiving procedure is a read and write operation, the session receiving the message must have write access to the message (the session's DBMS label must match the message's creation label or the session must have sufficient MAC privileges).

See the Trusted Oracle7 Server Administrator's Guide for more information about MAC privileges. See Appendix A of the Oracle7 Server Application Developer's Guide for more information about using pipes.

In OS MAC mode, pipes behave the same as they do in standard Oracle7.

 ________________________________________

Notes and Changes to Triggers and Stored Procedures

This section contains information about triggers and stored procedures that was omitted from the Oracle7 Server documentation set.

 Remote Procedure Calls and Parameter Values

You must explicitly pass values to all remote procedure parameters even if there are defaults. You cannot access remote package variables and constants.

 Keeping the Message Stack and RAISE_APPLICATION_ERROR

There is a third argument for the RAISE_APPLICATION_ERROR procedure that allows you to keep the message stack. For example:

raise_application_error (-20010, 'your message here', TRUE)

This parameter prints the current message stack before printing your message. The default is FALSE, which means that the message stack is not saved. See the Oracle7 Server Application Developer's Guide for more information about the default behavior of the procedure RAISE_APPLICATION_ERROR.

 Triggers and Database Links

To ensure predictable behavior, Oracle7 enforces certain restrictions on triggers that access tables related by referential integrity constraints. Because declarative referential integrity constraints are currently not supported between tables on different nodes of a distributed database, these restrictions do not apply to triggers that access remote nodes. See the Oracle7 Server Application Developer's Guide for more information on trigger restrictions.

These restrictions are also not enforced among tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear to be remote by defining a SQL*Net path back to the database that contains the link.

You should not use loop-back database links to circumvent the trigger restrictions. Such applications might behave unpredictably. Trigger restrictions, which were implemented to eliminate inconsistencies due to row order, may be enforced on loop-back database links in future releases of Oracle.

 Triggers and Handling Remote Exceptions

A trigger that accesses a remote site cannot do remote exception handling if the network link is unavailable. For example:

CREATE TRIGGER example

AFTER INSERT ON emp

FOR EACH ROW

BEGIN

  INSERT INTO emp@remote         -- <- compilation fails here

  VALUES ('x');                  --    when dblink is inaccessible

EXCEPTION

  WHEN OTHERS THEN

    INSERT INTO emp_log

    VALUES ('x');

END;

A trigger is compiled the first time it is called. Thus, if a dblink is down when the trigger must compile, Oracle cannot validate the statement accessing the remote database, and the compilation fails. The example exception statement cannot execute because the trigger does not complete compilation.

Because stored procedures are stored in a compiled form, the work-around for the above example is as follows:

CREATE TRIGGER example

AFTER INSERT ON emp

FOR EACH ROW

BEGIN

   insert_row_proc;

END;

CREATE PROCEDURE insert_row_proc

BEGIN

  INSERT INTO emp@remote

    VALUES ('x');

EXCEPTION

   WHEN OTHERS THEN

       INSERT INTO emp_log

       VALUES ('x');

END;

The trigger in this example compiles successfully and calls the stored procedure, which already has a validated statement for accessing the remote database; thus, when the remote INSERT statement fails because the link is down, the exception is caught.

 ________________________________________

Notes and Changes to SQL

This section describes changes or additions to SQL syntax and the cost based optimizer that were introduced in release 7.0.

 Optimizer Operations

The following operations should be added to the table of SQL statement operations on page B-13 of the Oracle7 Server Application Developer's Guide.

OPERATION	OPTION	Description

------------------------------------------------------------------------

COUNT	An operation that counts the number

of rows selected from a table.

STOPKEY	A count operation where the number

of rows returned is limited by the

ROWNUM expression in the

WHERE clause.

SORT	AGGREGATE	A retrieval of a single row that is the

result of applying a group function to

a group of selected rows.

Table B - 1.  OPERATION  and  OPTION  Values Produced by the EXPLAIN PLAN  Command

 CREATE DATABASE

The database name may only contain the alphabetic characters A...Z, numbers 0..9, _, $, and #.

 ALTER and CREATE SNAPSHOT and Index Storage

The creation of a simple snapshot automatically causes an index to be created. The CREATE and ALTER SNAPSHOT commands now support a USING INDEX clause to allow users to specify the tablespace and storage parameters to use for this index. If the USING INDEX clause is not specified, the index is created with the same tablespace and storage parameters as the snapshot.

The new syntax for the CREATE SNAPSHOT command is as follows:

The new syntax for the ALTER SNAPSHOT command is as follows:

 Using DECODE in Expr

The following paragraph clarifies decoded expressions, as described on p. 3-62 of the Oracle7 Server SQL Language Reference Manual, part number 778-70-1292.

When using the special DECODE syntax, the search, result, and default values can be expressions.

 Outer Joins

Oracle7 is more stringent than Oracle release 6.0 in rejecting two types of semantically undefined outer join queries:

•	The OR logical operator cannot combine two conditions if either contains the outer join operator (+). Also, a condition cannot compare a column marked with the (+) operator to another expression using the IN comparison operator. Error 1719, outer join operator (+) not allowed in operand of OR or IN, is returned for such invalid conditions. If you have applications that issue queries with such conditions, replace them with equivalent queries that use the UNION or UNION ALL set operators instead.

•	If a condition compares a column marked with the (+) operator to a subquery, Oracle7 returns the following message.

ORA-01799: a column may not be outer-joined to a subquery

Oracle version 6 ignored the (+) operator in such conditions. If you have applications that issue queries with such conditions, remove the (+) from them and they will behave in Oracle7 as they did in Oracle version 6.

 Trusted Oracle7 Server

The following SQL command prerequisites supersede the prerequisites documented in the Oracle7 Server SQL Language Reference Manual. These prerequisites are documented correctly in the Trusted Oracle7 Server Administrator's Guide.

 INSERT

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the creation label of the table or view.

•	If the creation label of the table or view is higher than your DBMS label, you must have READUP and WRITEUP privileges.

•	If the creation label of your table or view is noncomparable to your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN privileges.

 CREATE TABLESPACE

If you are using Trusted Oracle7 in DBMS MAC mode, you cannot reuse the name of a tablespace that previously existed at a different label. You can reuse the name of a tablespace that previously existed at the same label. See the Trusted Oracle7 Server Administrator's Guide for information on creating tablespaces.

Usage Notes     If you are using Trusted Oracle7 in either DBMS MAC or OS MAC mode, tablespaces are automatically labeled at your current DBMS label.

 ALTER TABLESPACE

If you are using Trusted Oracle7 in DBMS MAC mode, you must have write access to the tablespace (your DBMS label must match the object's creation label or you must have sufficient MAC privileges). See the Trusted Oracle7 Server Administrator's Guide for information on MAC privileges. If you are adding a datafile, your DBMS label must match the creation label of the tablespace.

The Trusted Oracle7 Server Administrator's Guide, page 3-6, should indicate that changing tablespace quotes also require appropriate MAC privileges or labels. The corrected text is as follows.

ALTER USER       "To alter a user's password, default or temporary tablespace, tablespace quota, or user profile, your label must match the user's creation label, or you must have the appropriate MAC privilege to allow you to both read and write to the user's creation label."

 ________________________________________

SQL*Net and the Multi-Threaded Server

Oracle7 fully supports SQL*Net version 1, either as a client or a server. Therefore, clients running SQL*Net version 1 can connect to Oracle7 Servers running SQL*Net version 1 for client/server access. Furthermore, Oracle7 Servers can communicate with one another for distributed queries, and (with the distributed option) for distributed update, two-phase commit and snapshots using SQL*Net version 1. The multi-threaded server architecture in Oracle7 requires use of SQL*Net Version 2.

 ________________________________________

Oracle7 Server and CASE Dictionary

The DDL generator in CASE Dictionary V5.0.22 generates SQL DDL using Oracle version 6 syntax, which is slightly different from the syntax used by the Oracle7 Server. To account for this, SQL*Plus and SQL*DBA provide a setting to allow V6 syntax to be interpreted properly by the Oracle7 Server, including CHAR to VARCHAR2 translation. CASE*Dictionary 5.0 sets the mode automatically when these scripts are run from within the CASE*Dictionary environment. If you wish to execute your CASE-generated DDL scripts outside of CASE*Dictionary, you must first set your SQL*Plus or SQL*DBA session to V6 compatibility mode by issuing the following command:

SQL> set compatibility v6

To create true Oracle7 CHAR columns (fixed length, blank padded), the DDL scripts must be executed outside of the CASE environment without setting V6 compatibility mode. See page A-27 of the Oracle7 Server SQL Language Reference Manual for more information on Oracle version 6 compatibility mode.

Full support for the generation of DDL syntax and objects introduced in the Oracle7 Server will be available in CASE*Dictionary 5.1.

 ________________________________________

Security Information

These sections contain additional information about security and the Oracle7 Server.

 Privileges Required to Create Users

The note on page 11-8 of the Oracle7 Server Administrator's Guide incorrectly states the privileges required for a new user to connect to the database. A newly-created user cannot connect to the database until he or she is granted the CREATE SESSION system privilege, either directly or through a role.

 Using Operating System Role Management

The following information clarifies the information provided on page 12-24 of the Oracle7 Server Administrator's Guide.

When you use operating system managed roles, it is important to note that database roles are being granted to an operating system user. Any database user to which the OS user is able to connect will have the authorized database roles enabled. For this reason, you should consider defining all Oracle users as IDENTIFIED EXTERNALLY if you are using OS_ROLES = TRUE, so that the database accounts are tied to the OS account that was granted privileges.

 Using Indirectly Granted Roles

The Oracle7 Server Application Developer's Guide and the Oracle7 Server SQL Language Reference Manual do not clearly document the behavior of indirectly granted roles, that is, roles that are granted to other roles that are directly granted to users.

When a role that contains other roles is enabled, all the indirectly granted roles are explicitly enabled.

For example, SMITH is granted the PAYROLL_MANAGER role. The PAYROLL_MANAGER role has been granted the HR_CLERK and PAYROLL_CLERK role. When SMITH enables the PAYROLL_MANAGER role, his session now has the PAYROLL_MANAGER, PAYROLL_CLERK, and HR_CLERK roles enabled.

Each indirectly granted role can be explicitly enabled or disabled for a user.

Continuing the example above, SMITH could now explicitly disable the HR_CLERK role, even though this role was not directly granted to him. He could also issue the command SET ROLE PAYROLL_CLERK, causing the PAYROLL_CLERK role to be the only role enabled.

 Auditing

The following sections clarify the information provided on pages 13-4 and 13-5 of the Oracle7 Server Administrator's Guide and in Chapter 19 of the Oracle7 Server Concepts Manual.

 Audit Trail Information

Depending on the events audited and the auditing options set, the audit trail records can contain different types of information. The following information is always included in each audit trail record, provided that the information is meaningful to the particular audit action:

•	the user name

•	the session identifier

•	the terminal identifier

•	the name of the object accessed

•	the operation performed or attempted

•	the completion code of the operation

•	the date and time stamp

•	the system privileges (including MAC privileges for Trusted Oracle) used

•	the label of the user session (for Trusted Oracle only)

•	the label of the object accessed (for Trusted Oracle only)

Audit trail records written to the OS audit trail contain some encodings that are not human readable. These can be decoded as follows:

Action Code	This describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table contains a list of these codes and their descriptions.

Privileges Used	This describes any system privileges used to perform the operation. The SYSTEM_PRIVILEGE_MAP table lists all of these codes, and their descriptions.

Completion Code	This describes the result of the attempted operation. Successful operations return a value of zero, while unsuccessful operations return the Oracle error code describing why the operation was unsuccessful. These codes are listed in the Oracle7 Server Messages and Codes Manual.

 Events Always Audited to the Operating System Audit Trail

Regardless of whether or not database auditing is enabled, the Oracle Server will always audit certain database-related actions into the operating system audit trail. These events include the following:

Instance startup	An audit record is generated that details the OS user starting the instance, his terminal identifier, the date and time stamp, and whether database auditing was enabled or disabled. This is audited into the OS audit trail because the database audit trail is not available until after startup has successfully completed. Recording the state of database auditing at startup further prevents an administrator from restarting a database with database auditing disabled so that they are able to perform unaudited actions.

Instance shutdown	An audit record is generated that details the OS user shutting down the instance, her terminal identifier, the date and time stamp.

Connections to the

as

An audit record is generated that details the OS user connecting to Oracle as INTERNAL. This provides accountability of users connected as INTERNAL.

On operating systems that do not make an audit trail accessible to Oracle, these audit trail records are placed in an Oracle audit trail file in the same directory as background process trace files.

 Setting Audit Options on User Tables

The Oracle7 Server Administrator's Guide and Oracle7 Server Concepts Manual do not clearly document that users can set auditing options on any objects that they own. All of the object auditing syntax documented in the Oracle7 Server SQL Language Reference Manual, and in the Oracle7 Server Administrator's Guide (page 13-10) can be set by normal users on objects that they own.

Users can query the audit trail to look at activities on their objects by querying from the USER_AUDIT_xxxx data dictionary views. The Oracle7 Server Administrator's Guide Appendix B correctly describes these views.

 When Audit Options Take Effect

Although the Oracle7 Server SQL Language Reference Manual does correctly identify when audit options take effect, the Oracle7 Server Administrator's Guide should also detail this.

A new database session picks up auditing options from the data dictionary when the session is created. These auditing options remain in force for the duration of the database connection. Setting new system or object auditing options causes all subsequent database sessions to use these options; existing sessions will continue using the audit options in place at session creation.

Auditing Operations By Access

The Oracle7 Server Concepts Manual states that auditing BY ACCESS inserts one audit record into the audit trail each time an audited statement is executed. This incompletely describes the functionality of Oracle7 release 7.0.

Setting audit BY ACCESS inserts one audit record into the audit trail for each auditable event the first time that a cursor containing an audited statement is executed. Subsequent re-execution of the cursor does not cause an audit trail record to be generated. Events that cause cursors to be reused include the following:

•	an application, such as SQL*Forms, holding a cursor open for reuse

•	subsequent execution of a cursor using new bind variables

•	statements executed within PL/SQL loops where the PL/SQL engine optimizes the statements to reuse a single cursor

Note that auditing is NOT affected by whether a cursor is shared; each user creates her or his own audit trail records on first execution of the cursor.

This functionality has been updated in release 7.1 of the Oracle Server to audit each time the statement is executed. This functionality is described on page 9 - 11.

 Auditing to the OS Audit Trail

Both Oracle7 and Trusted Oracle7 allow audit trail records to be directed to an operating system audit trail, on platforms where the OS makes such an audit trail available to Oracle. On some other operating systems, these audit records are written to a file outside the database, with a format similar to other Oracle trace files.

Additional Information:  See your platform-specific Oracle documentation to see if this feature has been implemented on your operating system.

Trusted Oracle and Oracle allow certain actions that are always audited to continue even when the operating system audit trail, or the operating system file containing audit records, is unable to record the audit record. The normal cause of this is that the operating system audit trail, or the file system, is full and unable to accept new records.

When configured with OS auditing, system administrators should ensure that the audit trail or the file system does not fill completely. Most operating systems provide extensive measures to provide administrators with sufficient information and warning to ensure this does not occur. Furthermore, configuring auditing to use the database audit trail removes this vulnerability, as the Oracle Server prevents audited events from occurring if the audit trail is unable to accept the audit record for the statement.

 Identifying and Authenticating Users

In Version 6 of Oracle, every account always had an associated password so that a user could always connect to the database by supplying a valid username and password. In addition, if the database account had a username equal to an operating system username prefixed with the string OPS$, then that operating system user could connect to the database without providing the database password.

So, in Version 6 of Oracle, a user could connect to Oracle account OPS$SCOTT, using either of the following two connect strings (assuming TIGER was the password for user OPS$SCOTT):

CONNECT OPS$SCOTT/TIGER (regardless of the current o/s user)

CONNECT /               (only when logged into o/s as user SCOTT)

Oracle7 provides two ways to define users, depending on the way that you want their identity to be authenticated before they are allowed to create a database session. This information is included in the documents listed below but is further clarified in the following text.

•	Oracle7 Server Concepts Manual, page 17-3

•	Oracle7 Server Administrator's Guide, page 11-6

•	SQL*Net V2 Administrators Guide, page 2-10

You can configure Oracle7 so that it performs both identification and authentication of users, or only the identification of users (leaving authentication up to the operating system or network security service). Consider these two CREATE USER commands:

CREATE USER scott IDENTIFIED BY tiger;    -- DBMS ident + authent

CREATE USER scott IDENTIFIED EXTERNALLY;  -- DBMS ident only

In Oracle7, the initialization parameter OS_AUTHENT_PREFIX can be used to specify the equivalent of the Oracle Version 6 string OPS$ applied to operating system usernames. The value of OS_AUTHENT_PREFIX may be NULL, but defaults to OPS$. Oracle7 users IDENTIFIED EXTERNALLY do not supply a password and may connect to the database only from an operating system account corresponding to the Oracle username. Therefore, if the OS_AUTHENT_PREFIX is set to XX, then operating system user SCOTT can connect to Oracle only if XXSCOTT is a valid Oracle user IDENTIFIED EXTERNALLY, and SCOTT does not specify a password when connecting to Oracle. That is, for users IDENTIFIED EXTERNALLY, CONNECT / works when issued from the appropriate operating system account, and there is no possibility of specifying an Oracle password.

Oracle7 users that are not IDENTIFIED EXTERNALLY (that is, those Oracle users IDENTIFIED BY a password) normally can connect to Oracle from any valid operating system account provided they supply the proper password. In addition, for upward compatibility with Oracle Version 6, if the database username starts with OPS$ and is defined IDENTIFIED BY a password, and the OS_AUTHENT_PREFIX initialization parameter is set to OPS$, its default value, CONNECT / works when issued from the appropriate operating system account.

The system administrator should ensure that the combination of OS_AUTHENT_PREFIX and any valid operating system username do not match any database username defined with a password to remove this vulnerability.

Release 7.0.16 of Oracle7 and Trusted Oracle7  and beyond restrict this upward compatibility behavior so that accounts defined with passwords can only be accessed by CONNECT / if both of the following conditions are true:

•	OS_AUTHENT_PREFIX must be set to OPS$ (its default value)

•	the database username must start with OPS$

That is, if OS_AUTHENT_PREFIX is OPS$, Oracle7 release 7.0.16 or greater permits the operating system user SCOTT to connect to Oracle as OPS$SCOTT without supplying a password, even if OPS$SCOTT was created IDENTIFIED BY a password. Connection is refused to any Oracle usernames not starting with OPS$ that are created IDENTIFIED BY a password unless the proper password is supplied.

This change reduces the possibility of improperly authorized Oracle connections and retains upward compatibility with Oracle version 6 for OPS$username accounts.

 ________________________________________

Additions to Trusted Oracle7 Server Documentation

This section contains information that should be added to the Trusted Oracle Server Administrator's Guide. All page references refer to the Trusted Oracle Server Administrator's Guide, part number 6610-70-0193, unless otherwise indicated.

 Labeling of Label Definitions (page 1-8)

While some secure operating systems do not classify the existence of labels, there are other operating systems (for example, all Compartmented Mode Workstations) that protect information about the existence of labels by classifying the label definition as the label value. Thus, the definition of the TOP SECRET:ALPHA label is itself labeled at TOP SECRET:ALPHA.

In general, Trusted Oracle does protect labels at their own classification. However, when configured in DBMS MAC mode, several mechanisms exist that allow a user to infer that a particular label is a valid label that has been defined in the operating system. System administrators should be aware that users can infer the existence of labels that are classified outside the user's clearance range. Trusted Oracle configured in OS MAC mode does protect label definitions at their own classification.

 Creating and Setting Up a Trusted Oracle7 Database (page 2-9)

Step 7: Restart the Database at DBHIGH.

Login to the operating system at the operating system equivalent of DBHIGH. Restart the database using the standard STARTUP command. The database is now functioning in DBMS MAC mode.

 Tablespace Creation Labels (page 2-12)

Like the creation label of other objects, you cannot alter the creation label of a tablespace. Note that if the tablespace creation label is the special label DBLOW, resetting the value of DBLOW by use of the ALTER DATABASE SET DBLOW=`label' command will cause any tablespaces created at DBLOW to be lowered to the new DBLOW. However, any other tablespaces created at a label equivalent to DBLOW will not be lowered. See the warnings on pages 2-9 and 3-10 concerning the effect on creating user objects or database rows labeled at the special label DBLOW.

 Lowering DBLOW (page 2-10)

The documentation correctly explains how to reset DBLOW inside a Trusted Oracle7 database running in DBMS MAC mode. However, if you are resetting DBLOW on a database that already contains data or in a system where the Oracle software was installed at the old value of DBLOW, there are several other actions that you will need to perform to reset DBLOW and there are some problems that may prevent you from being able to do this in certain circumstances. These are outlined below.

Most data dictionary objects are themselves labeled in the data dictionary at the special labels DBLOW or DBHIGH. When DBLOW or DBHIGH are reset, these data dictionary objects are effectively relabeled to the new values of DBLOW or DBHIGH. Only objects created while connected as INTERNAL and with the DBMS session label explicitly set to DBHIGH or DBLOW are labeled with these special labels. All other objects created by all users, including SYSTEM and SYS (if not connected as INTERNAL), are created at normal labels that do not change when DBLOW and DBHIGH are altered.

Resetting DBLOW to a lower label means that users connecting at the new low value cannot see objects created at the old lowest label. In a normal installation, this means that the following objects are not available to users below the DBLOW set at installation time:

•	All tablespaces except SYSTEM.

•	All tables required by Oracle tools, including SQL*Forms, SQL*ReportWriter, SQL*Menu, and SQL*Plus.

•	All user defined objects created at the old DBLOW.

These problems cannot be solved by dropping user tablespaces created at the label equivalent to the old DBLOW, and then creating identically named tablespaces at the new DBLOW (or equivalent) because tablespace names cannot be reused at a lower label in an existing database (for reasons of recoverability). This frequently means that recreating the database, as recommended below, provides the best solution if you lower DBLOW.

Note that any objects created at the special labels DBHIGH and DBLOW will be reset to the real labels equivalent to DBHIGH and DBLOW if the objects are exported using EXPORT, and subsequently recreated by the IMPORT utility.

In addition, if you are moving DBLOW below the label at which you installed the Oracle software in your operating system, you will need to relabel all operating system files that were labeled at the old DBLOW to the new DBLOW. You will need appropriate OS privileges to downgrade all of these files. Take care not to downgrade any files that were labeled at DBHIGH, such as the database files, or any user files containing classified material that might be in the Oracle directories.

Recommendation: Lowering DBLOW of a database that already contains objects other than the data dictionary is complicated and not recommended by Oracle Corporation. In most installations, the software will have been installed at the lowest label in the OS, making this process unnecessary. However, if you do have an existing database to which you must lower DBLOW, we would recommend that you reinstall software at the new DBLOW, as outlined below, rather than reset DBLOW of the existing system:

1.	Do a full export of your system. Shut the database down, and make a full backup of your OS file system.

2.	Relabel all files labeled at DBLOW and created by the Oracle installation to the new value of DBLOW.

3.	Delete the database files, control files, redo logs.

4.	Create a new database at the new DBLOW.

5.	Reinstall the system tables required by any tools (For example, SQL*Forms, etc.)

6.	Pre-create any user objects and tablespaces that are required with creation labels lower than the old DBLOW.

7.	Import the data using the export file created in step 1 above.

Please call Oracle Technical Support for more advice on planning this move before you start.

 Switching from OS MAC to DBMS MAC Mode (page 2-17) and Switching from DBMS MAC to OS MAC Mode (page 10-18)

(Add this paragraph to page 2-17)

It is possible to change the mode of a database from OS MAC to DBMS MAC mode (and vice versa), even if the database contains existing labeled data. It is not intended that this should be performed on a database containing existing data in the normal course of operation, although there may be times when this is necessary for long term reconfiguration of your system or if you are attempting to start up the database with an invalid DBHIGH or DBLOW (see page 2-11).

(Add the following section to page 10-18)

If you have existing multilevel data in a DBMS MAC mode database, all rows will appear to be uniformly labeled at the database label if the database is altered to OS MAC mode. This results in all rows appearing to have been upgraded to the label equivalent to DBHIGH. However, the existing rowlabels are maintained on the database rows. Any new rows are labeled at the database label. If the database mode is subsequently altered to return it to DBMS MAC mode, the rowlabel on each row reverts to its original value when the database was originally in DBMS MAC mode.

Any new rows inserted while the database was in OS MAC mode will be labeled at the inserting user's OS label, and will retain this classification when the database reverts to DBMS MAC mode. Existing rows inserted into the database when it was originally in DBMS MAC mode can be displayed and updated while in OS MAC mode, and appear to be labeled at the database label (equivalent to DBHIGH). Note however that the row, and any changes applied to the row while the database was in OS MAC mode, will revert to its original label when the database returns to DBMS MAC mode.

Therefore, when a populated database that has formerly been used in DBMS MAC mode is now configured in OS MAC mode, you should always update the rowlabel column to your session label in addition to any other columns that you change. Otherwise, changes to data made while the database is in OS MAC mode may be made available to lower level users when the database mode is returned to DBMS MAC mode. For example,

update emp

set sal=10000, rowlabel=userenv('label')

where empno=100;

Note: Changing MAC modes on a database containing existing data is not recommended by Oracle Corporation in normal operation of Trusted Oracle, with the exception of fixing an invalid value of DBHIGH or DBLOW as described on page 2-11. In all other cases, please contact Oracle Technical Support for further advice.

 Operating System Authentication (page 3-2)

Page 3-2 advises that you should not use operating system authentication when using Trusted Oracle7 in a distributed configuration. In environments where you are connecting Trusted Oracle7 to other Trusted Oracle7 Servers or to clients, and all machines are connected using a secure protocol (TNET or MaxSix or TSIX (RE)), this warning can be ignored if all connected client and server machines are securely administered.

 DBMS Clearance (page 3-8)

DBMS Clearance is defined elsewhere in the documentation in an inconsistent fashion. The What is DBMS Clearance? Section should be modified for clarification. Replace original paragraph 1 with the following paragraph:

When you login to Trusted Oracle, your DBMS clearance is based on your operating system clearance. Within your primary database, you can access data and create DBMS sessions at any label within your OS clearance that is within the range of DBLOW to DBHIGH for that database. For example, if you are cleared to see UNCLASSIFIED to TRULY_SENSITIVE data in the operating system and the range of your primary database is SENSITIVE to TRULY_SENSITIVE:ALPHA, you will be able to gain access to data in your primary database labeled from SENSITIVE to TRULY_SENSITIVE. Of course, you will not be able to gain access to data labeled at UNCLASSIFIED in your primary database because there is no such data in your primary database. You will not be able to gain access to data labeled with category ALPHA, since it is outside your OS clearance.

Paragraph 2 remains. Insert a new paragraph 3 as below:

MAC privileges, described on page 6-7, can only be exercised within your primary DBMS MAC mode database and not any secondary databases. Furthermore, the effects of MAC privileges are limited to that portion of the range from DBLOW to DBHIGH of your primary database which is within your OS clearance range. See page 6-8 for more information.

 Requirements for Raising or Lowering Your DBMS Label (page 3-9)

The existing documentation does not correctly document the privileges required to alter a user's DBMS label if the session label has already been altered so that DBMS label and OS label are not equal. The following conditions replace the bulleted list on page 3-9 and 3-10.

To alter those portions of your label that are used in policy enforcement (for example, the sensitivity and, in some cases, the integrity portions), you must meet the following requirements:

•	To alter your DBMS label to a label higher than your OS label, you must have the READUP and WRITEUP MAC privileges.

•	To alter your DBMS label to a label lower than your OS label, you must have the WRITEDOWN MAC privilege.

When a user has already changed his DBMS session label one or more times, the MAC privileges required to change it again depend on the initial DBMS label (the OS label), and not on the current DBMS label.

See MAC Privileges on page 6-7 and in this appendix for more information about MAC privileges.

 Trigger Execution (page 4-15)

The documentation correctly describes the label at which a trigger fires and the trigger's MAC and DAC domains. Note that all rows inserted into the database by a trigger will be labeled at the trigger execution label, unless the trigger fires with MAC privileges that are used to label any inserted rows at a different label.

 When do Privilege Revokes Take Effect? (DBMS MAC mode - page 6-7) (OS MAC mode - page 14-5)

The Oracle7 Server Administrator's Guide (page 12-21) describes that grants and revokes of object privileges and non-MAC system privileges take effect as follows:

•	All grants/revokes of privileges (system and object) to anything (users, roles, and PUBLIC) are immediately observed.

•	All grants/revokes of roles to anything (users, other roles, PUBLIC) are only observed when a current user session issues a SET ROLE statement to re-enable the role after the grant/revoke, or when a new user session is created after the grant/revoke.

Note that in DBMS MAC mode, the MAC privilege changes caused by direct grants/revokes of the READUP, WRITEUP, and WRITEDOWN MAC privileges do not take effect until your next database connection.

In addition, use of these privileges is controlled by checking that the user has equivalent privileges granted to their OS process, as documented on page 6-8. Note that granting/revoking these privileges from a user in the OS does not change the user's database MAC privileges until the user reconnects to the database. In fact, on most operating systems, revoking OS privileges from an OS user has no effect on existing OS processes, and consequently are not reflected in any database sessions initiated from existing OS processes.

The READUP, WRITEUP, and WRITEDOWN MAC privileges can be enabled and disabled by a user dynamically if they are granted to the user indirectly though a role. Enabling and disabling the role will take immediate effect.

In addition to the considerations described above, when configured in OS MAC mode, privilege changes do not take effect for users connecting to a database as a secondary databases (that is, reading down to the database) until the next checkpoint event occurs. See the discussion on page 10-17 and in the section titled LOG_CHECKPOINT_TIMEOUT Parameter and Privilege Propagation later in this appendix.

Note also that shutting down a lower level database from the instance mounting the database as its primary database does not cause instances running at higher labels to also shutdown or dismount the lower labeled database. If you want to prevent access to a database by all users, you must shutdown its primary instance and either dismount the database from higher labeled instances or shutdown those higher labeled instances.

For example, you are the administrator of an UNCLASSIFIED database named UNCLAS. You wish to prevent users from accessing this database from all labels. You should shutdown the instance at UNCLASSIFIED using the SHUTDOWN command. You must then connect to each higher level instance that has the database mounted as a secondary database, and either shutdown the higher level instance with the SHUTDOWN command, or dismount the UNCLAS secondary database using the SHUTDOWN UNCLAS command.

 MAC Privileges (page 6-7)

The description of Trusted Oracle7 MAC privileges given on page 6-7 does not adequately describe the use of these privileges when dealing with non-comparable labels. The definitions on 6-7 should be replaced with the following:

WRITEUP	allows the user to perform write operations on data at all labels dominating their DBMS label and dominated by the greatest lower bound of:

a) DBHIGH of their primary database, and  b) the top bound of their OS clearance range

WRITEDOWN	allows the user to perform write operations on data at all labels dominated by their DBMS label and dominating the least upper bound of:

a) DBLOW of their primary database, and b) the lower bound of their OS clearance range

WRITEUP and

when used together allow users to write to data at all labels dominating the least upper bound of:

a) DBLOW of their primary database, and b) the lower bound of their OS clearance range

and dominated by the greatest lower bound of:

a) DBHIGH of their primary database, and  b) the top bound of their OS clearance range

READUP	allows the user to read data at all labels dominated by the greatest lower bound of:

a) DBHIGH of their primary database, and  b) the top bound of their OS clearance range.

Note that MAC privileges, as described above, are privileges to selectively bypass the MAC policy. They do not allow you to perform operations outside your clearance range.

Note that MAC privileges are granted to the DBA role with effect from Trusted Oracle7 release 7.0.12. You can, of course, remove these privileges from the DBA role if you want by the following statement, issued when connected as internal at DBLOW:

REVOKE READUP,WRITEUP,WRITEDOWN FROM DBA;

 Requirements for Using Privileges (page 6-10)

The documentation correctly defines the effects of MAC privileges used to exercise a privilege granted to you at a higher label, but this is misleadingly clarified by comparing the effect of privileges on accessing your own objects with creation labels above your session label. Trusted Oracle7 release 7.0 behaves as follows:

•	You can use the READUP privilege to see an object that you own and which is labeled above or at a non-comparable label to your DBMS label.

•	You cannot use the READUP privilege to see an object that you do not own but which has been granted at a higher or non- comparable label to you, to your roles, or to PUBLIC.

Note that this behavior will change in a future release of Trusted Oracle7, so that you can use the READUP system privilege to see both objects and grants that were made to you at a higher or non-comparable label.

 Selecting Rows from Higher or Non-Comparable Labels (page 7-3)

Following from the enhanced definition of the READUP privilege given in MAC Privileges, earlier in this appendix, the following describes selecting data at higher or non-comparable labels.

According to MAC policy, you can only select rows from your label and below. However, if you have the READUP MAC privilege, you can also retrieve rows at all higher or non-comparable labels that are within your clearance.

Operation	MAC Privilege Required

------------------------------------------------------------------------

Select from a row at the user's DBMS 	none

label and below

Select from a row at a higher label 	READUP

within the user's clearance

Select from a row at a non-compara-	READUP

ble label within the user's clearance

Table B - 2.  Privileges Necessary for Selecting Rows at Different Labels

 Inserting, Updating, and Deleting Rows at Different Labels (page 7-9)

The privileges shown in Table 7-1 for manipulating rows at non-comparable labels is inaccurate. The following table should replace Table 7-1:

Operation	MAC Privilege Required

Insert or delete a row at a higher label	WRITEUP

Update a row at a higher label	READUP, WRITEUP

Insert, update, or delete a row at a lower label	WRITEDOWN

Insert a row at a non-comparable label within 	WRITEUP, WRITEDOWN

the user's clearance

Update or delete a row at a non-comparable 	READUP, WRITEUP,

label within the user's clearance	WRITEDOWN

Table B - 3.  Privileges Necessary for Manipulating Rows at Different Labels

 Requirements for Inserting, Updating, and Deleting Rows below the Table's Creation Label (page 7-9)

A table created at a label strictly dominating DBLOW can potentially contain rows below, or non-comparable to, the table creation label.

The conditions listed on page 7-9 and 7-10 correctly define the conditions required to insert, update, and delete rows labeled below the table creation label. In addition, the following conditions apply for rows labeled at non-comparable labels to the table's creation label:

To insert, update or delete rows, you must meet both of the following criteria:

•	You must be able to see the creation label of the table (as defined in the sections Requirements for Using Privileges, and Selecting Rows from Higher or Non-Comparable Labels, earlier in this appendix).

•	You must have the appropriate privilege to insert, update or delete the row (as defined in Table 7.1, above).

 Auditing Covert Channels (page 8-7)

Add the following entries into table 8-1.

Operation	Potential Covert Channel	Audit Option

PASSWORD 	If a user changes his password 	AUDIT

CHANGE and 	in a high session, the user will 	CREATE_SESSION

other ALTER 	be unable to connect to the 	and AUDIT ALTER

USER OPTIONS	database at a lower label	USER catches sender

and receiver

INSERTING 	A lower label user may detect 	AUDIT INSERT on all

ROWS	whether a higher labeled user 	tables

has inserted a row in the same

table by observing the assigned

ROWID of a new row inserted at

the row label

Table B - 4.  Auditing Covert Channels

 Setting Up Database Links to Lower Level OS MAC Mode databases (page 10-12)

The syntax described on page 10-11 is correctly used to define a public database link that is used to access secondary databases. When specifying the control file name, you should specify the full path and file name of the control file for the secondary database to which you want to read down. If, as recommended by Oracle, your installation has multiple control files spread over different disk devices, you can specify any one of these control files in the database link definition. For example:

CREATE PUBLIC DATABASE LINK unclassified

   CONNECT TO judy IDENTIFIED BY aloha

   USING 'ROM:$ORACLE_HOME/dbs/ctrl1sid.ctl,READ SECURE'

 LOG_CHECKPOINT_TIMEOUT Parameter and Privilege Propagation (page 10-16)

All changes made to an OS MAC mode database can only be seen by users at higher labels after modified database buffers have been written to the data files. Blocks are written out to disk by the DBWR process as a result of either the LRU algorithm making space for new blocks or by a checkpoint event. This is described on pages 10-15 and 10-16.

Note that the frequency of checkpointing affects data dictionary information, such as privileges, in addition to user data. For this reason, you should consider how rapidly you require privilege grants and revokes to take effect for higher labeled users when you set the value of LOG_CHECKPOINT_TIMEOUT.

When shutting down a lower label database, you should also attempt to shut the database down cleanly (that is, by use of SHUTDOWN NORMAL and not SHUTDOWN IMMEDIATE or SHUTDOWN ABORT) to ensure that a checkpoint occurs before shutdown.

 ________________________________________

Additions to the Oracle Parallel Server Documentation

This section contains information that should be added to Chapter 5 of the Oracle7 Parallel Server Administrator's Guide.

 Beginning and Ending Online Backups

You can begin an online backup of a tablespace from one instance and end the backup from another instance of a Parallel Server, using the following SQL statements:

ALTER TABLESPACE tablespacename BEGIN BACKUP;

ALTER TABLESPACE tablespacename END BACKUP;

It does not matter which instance issues each of these statements, but they must be issued whenever you make an online backup. Oracle uses these statements to record information about the control file and redo log files. The BEGIN BACKUP option has no effect on users' access to the tablespace.

For an online backup to be valid, you will need to back up all the redo logs from those that are current when the ALTER TABLESPACE BEGIN BACKUP command was issued, to those redo logs that are current when you issue the ALTER TABLESPACE END BACKUP command.

For a description of the ALTER TABLESPACE statement, refer to the Oracle7 Server SQL Language Reference Manual.


APPENDIX C. OPERATING SYSTEM- SPECIFIC INFORMATION

This addendum occasionally refers to other Oracle manuals that contain detailed information for using Oracle on a specific operating system. These Oracle manuals are generically referred to as platform-specific Oracle documentation, as the exact name varies on different operating systems.

This appendix lists all the references in this addendum to operating system-specific Oracle manuals, and lists the operating system-dependent initialization parameters. If you are using Oracle on multiple operating systems, this appendix can help you ensure that your applications are portable across these operating systems.

Operating system-specific topics are listed alphabetically with page numbers of sections that discuss these topics.

 ________________________________________

Topics

Connecting as SYSOPER/SYSDBA

Using OS Authentication, 5 - 12

Operating System Audit Trail, B - 19

Password Files

Installing, 5 - 7

Name of file (ORA_sid_PWFILE), 5 - 8

Platform-specific password files, 5 - 7

Password File Encryption

ORA_ENCRYPT_LOGIN environment variable, 5 - 3

Secure Connections using SQL*Net, 5 - 6

Supplied Packages, A - 25