7
SQL Statements (continued)

ADVISE  

sends advice to a remote database to force a distributed transaction. The advice appears in the ADVICE column of the DBA_2PC_PENDING view on the remote database (the value 'C' for COMMIT, 'R' for ROLLBACK, and ' ' for NOTHING). If the transaction becomes in doubt, the administrator of that database can use this advice to decide whether to commit or roll back the transaction.

You can send different advice to different remote databases by issuing multiple ALTER SESSION statements with the ADVISE clause in a single transaction. Each such statement sends advice to the databases referenced in the following statements in the transaction until another such statement is issued. For more information on distributed transactions and how to decide whether to commit or roll back in-doubt distributed transactions, see Oracle8i Distributed Database Systems.  

CLOSE DATABASE LINK  

closes the database link dblink. When you issue a statement that uses a database link, Oracle creates a session for you on the remote database using that link. The connection remains open until you end your local session or until the number of database links for your session exceeds the value of the initialization parameter OPEN_LINKS. If you want to reduce the network overhead associated with keeping the link open, use this clause to close the link explicitly if you do not plan to use it again in your session. You must first close all cursors that use the link and then end your current transaction if it uses the link.  

ENABLE | DISABLE COMMIT IN PROCEDURE  

Procedures and stored functions written in PL/SQL can issue COMMIT and ROLLBACK statements. If your application would be disrupted by a COMMIT or ROLLBACK statement not issued directly by the application itself, use the DISABLE form of this clause to prevent procedures and stored functions called during your session from issuing these statements.

You can subsequently allow procedures and stored functions to issue COMMIT and ROLLBACK statements in your session by issuing the ENABLE form of this clause.

Some applications (such as SQL*Forms) automatically prohibit COMMIT and ROLLBACK statements in procedures and stored functions. Refer to your application documentation.  

Note: This statement does not apply to database triggers. Triggers can never issue COMMIT or ROLLBACK statements.  

PARALLEL DML | DDL  

specifies whether all subsequent DML or DDL transactions in the session will be considered for parallel execution. This clause enables you to override the degree of parallelism of tables during the current session without changing the tables themselves. You can execute this clause only between committed transactions. Uncommitted transactions must either be committed or rolled back prior to executing this clause.  

ENABLE  

executes subsequent statements in the session in parallel. This is the default for DDL statements.  

• DML: executes the session's DML statements in parallel mode if a parallel hint or a parallel clause is specified.

• DDL: executes the session's DDL statements in parallel mode if a parallel clause is specified.

Restriction: You cannot specify the optional PARALLEL integer with ENABLE.  

DISABLE  

specifies that subsequent statements will be executed serially. This is the default for DML statements.  

• DML: executes the session's DML statements serially.

• DDL: executes the session's DDL statements serially.

Restriction: You cannot specify the optional PARALLEL integer with DISABLE.  

FORCE  

forces parallel execution of subsequent statements in the session if no parallel DML restrictions are violated (see below). If no parallel clause or hint is specified, then a default degree of parallelism is used. This clause overrides any parallel_clause specified in subsequent statements in the session, but is overridden by a parallel hint.  

Using FORCE automatically causes all tables created in this session to be created with a default level of parallelism. The effect is the same as if you had specified the parallel_clause (with default degree) with the CREATE TABLE statement.  

• DML: executes subsequent DML statements in the session with the default degree of parallelism, unless a specific degree is specified in this clause.

• DDL: executes subsequent DDL statements in the session with the default degree of parallelism, unless a specific degree is specified in this clause. Resulting database objects will have associated with them the prevailing degree of parallelism.

• PARALLEL integer: explicitly specifies a degree of parallelism, which overrides any parallel_clause specified in a subsequent DDL statement in the session, but is overridden by any parallel hint specified in a subsequent DML statement.

The following types of DML operations are not parallelized regardless of this clause:

• operations on clustered tables

• operations with embedded functions that either write or read database or package states

• operations on tables with triggers that could fire

• operations on tables or schema objects containing object types, or LONG or LOB datatypes.

For a detailed description of parallel DML features and hints, see Oracle8i Tuning.  

set_clause  

sets the session parameters that follow. You can set values for multiple parameters in the same set_clause.  

CAUTION: Unless otherwise indicated, the parameters described here are initialization parameters, and the descriptions indicate only the general nature of the parameters. Before changing the values of initialization parameters, please refer to their full description in Oracle8i Reference or Oracle8i National Language Support Guide.  

CONSTRAINT{S} = {IMMEDIATE | DEFERRED | DEFAULT }  

determines when conditions specified by a deferrable constraint are enforced. CONSTRAINT{S} is a session parameter only, not an initialization parameter.  

• IMMEDIATE indicates that the conditions specified by the deferrable constraint are checked immediately after each DML statement. This setting is equivalent to issuing the SET CONSTRAINTS ALL IMMEDIATE statement at the beginning of each transaction in your session. See the IMMEDIATE parameter of "SET CONSTRAINT(S)".

• DEFERRED indicates that the conditions specified by the deferrable constraint are checked when the transaction is committed. This setting is equivalent to issuing the SET CONSTRAINTS ALL DEFERRED statement at the beginning of each transaction in your session. See the DEFERRED parameter of "SET CONSTRAINT(S)".

• DEFAULT restores all constraints at the beginning of each transaction to their initial state of DEFERRED or IMMEDIATE.

CREATE_STORED_OUTLINES = { TRUE | FALSE | 'category_name' }  

determines whether Oracle should automatically create and store an outline for each query submitted during the session. CREATE_STORED_OUTLINES is not an initialization parameter.  

• TRUE enables automatic outline creation for subsequent queries in the same session. These outlines receive a unique system-generated name and are stored in the DEFAULT category. If a particular query already has an outline defined for it in the DEFAULT category, that outline will remain and a new outline will not be created.

• FALSE disables automatic outline creation during the session. This is the default.

• category_name has the same behavior as TRUE except that any outline created during the session is stored in the category_name category.

CURRENT_SCHEMA = schema  

changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement.  

This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give you any additional system or object privileges for the session. For more information on this parameter, see Oracle8i Application Developer's Guide - Fundamentals.  

DB_BLOCK_CHECKING = {TRUE | FALSE}  

controls whether data block checking is done. The default is FALSE, for compatibility with earlier releases where block checking is disabled as a default.  

DB_FILE_MULTIBLOCK_READ_COUNT = integer  

specifies with integer the maximum number of blocks read in one I/O operation during a sequential scan. The default is 8.  

FAST_START_IO_TARGET  

specifies the target number of IOs (reads and writes) to and from buffer cache that Oracle should perform upon crash or instance recovery. Oracle continuously calculates the actual number of IOs that would be needed for recovery and compares that number against the target. If the actual number is greater than the target, Oracle attempts to write additional dirty buffers to advance the checkpoint, while minimizing the affect on performance.

For information on how to tune this parameter, see Oracle8i Tuning.  

FLAGGER = { ENTRY | INTERMEDIATE | FULL | OFF }  

specifies FIPS flagging, which causes an error message to be generated when a SQL statement issued is an extension of ANSI SQL92. In Oracle, there is currently no difference between Entry, Intermediate, or Full level flagging. Once flagging is set in a session, a subsequent ALTER SESSION SET FLAGGER statement will work, but generates the message, ORA-00097. This allows FIPS flagging to be altered without disconnecting the session. OFF turns off flagging.  

GLOBAL_NAMES = { TRUE | FALSE }  

When you start an instance, Oracle determines whether to enforce global name resolution for remote objects accessed in SQL statements based on the value of the initialization parameter GLOBAL_NAMES. This parameter enables or disables global name resolution for the duration of the session. TRUE enables the enforcement of global names. FALSE disables the enforcement of global names. You can also enable or disable global name resolution for your instance with the GLOBAL_NAMES parameter of the ALTER SYSTEM statement.

Oracle recommends that you enable global name resolution if you use or plan to use distributed processing. For more information on global name resolution and how Oracle enforces it, see "Referring to Objects in Remote Databases" and Oracle8i Distributed Database Systems.  

HASH_AREA_SIZE = integer  

specifies in bytes the amount of memory to use for hash join operations. The default is twice the value of the SORT_AREA_SIZE initialization parameter.  

HASH_JOIN_ENABLED = {TRUE | FALSE}  

enables or disables the use of the hash join operation in queries. The default is TRUE, which enables hash joins.  

HASH_MULTIBLOCK_IO_COUNT = integer  

specifies the number of data blocks to read and write during a hash join operation. The value multiplied by the DB_BLOCK_SIZE initialization parameter should not exceed 64 K. The default value for this parameter is 1. If the multi-threaded server is used, the value is always 1, and any value specified here is ignored.  

INSTANCE = integer  

in a parallel server, accesses database files as if the session were connected to the instance specified by integer. INSTANCE is a session parameter only, not an initialization parameter. For optimum performance, each instance of a parallel server uses its own private rollback segments, freelist groups, and so on. In a parallel server, you normally connect to a particular instance and access data that is partitioned primarily for your use. If you must connect to another instance, the data partitioning can be lost. Setting this parameter lets you access an instance as if you were connected to your own instance.  

ISOLATION_LEVEL = { SERIALIZABLE | READ COMMITTED }  

specifies how transactions containing database modifications are handled. ISOLATION_LEVEL is a session parameter only, not an initialization parameter.  

• SERIALIZABLE indicates that transactions in the session use the serializable transaction isolation mode as specified in SQL92. That is, if a serializable transaction attempts to execute a DML statement that updates rows currently being updated by another uncommitted transaction at the start of the serializable transaction, then the DML statement fails. A serializable transaction can see its own updates.

• READ COMMITTED indicates that transactions in the session will use the default Oracle transaction behavior. Thus, if the transaction contains DML that requires row locks held by another transaction, then the DML statement will wait until the row locks are released.

LOG_ARCHIVE_DEST_n = {null_string | {LOCATION=pathname | SERVICE=servicename}

[MANDATORY | OPTIONAL] [REOPEN[=retry_time_in_seconds]]}  

specifies up to five session-specific valid operating system pathnames or Oracle service names (plus other related options) as destinations for archive redo log file groups (n = integers 1 through 5). For a description of the options, refer to Oracle8i Reference.

Restrictions: If you set a value for this parameter,

• You cannot have definitions for the parameters LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST in your initialization parameter file, nor can you set values for those parameters with the ALTER SYSTEM statement.

• You cannot start archiving to a specific location using the ALTER SYSTEM ARCHIVE LOG TO location statement.

LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER}  

specifies the session-specific state associated with the corresponding LOG_ARCHIVE_DEST_n parameter.

• ENABLE specifies that any associated valid destination can be used for archiving. This is the default.

• DEFER specifies that Oracle will not consider for archiving any destination associated with the corresponding LOG_ARCHIVE_DEST_n parameter.

LOG_ARCHIVE_MIN_SUCCEED_DEST = integer  

specifies the session-specific minimum number of destinations that must succeed in order for the online log file to be available for reuse.  

MAX_DUMP_FILE_SIZE = { size | UNLIMITED }  

specifies the upper limit of trace dump file size. Specify the maximum size as either a nonnegative integer that represents the number of blocks, or as UNLIMITED. If you specify UNLIMITED, no upper limit is imposed.  

NLS parameters: When you start an instance, Oracle establishes support based on the values of initialization parameters that begin with "NLS". You can query the dynamic performance table V$NLS_PARAMETERS to see the current NLS attributes for your session. For more information about NLS parameters, see Oracle8i National Language Support Guide.  

NLS_CALENDAR = 'text'  

explicitly specifies a new calendar type.  

NLS_COMP = 'text'  

specifies that linguistic comparison is to be used according to the NLS_SORT parameter. This parameter obviates the need to specify NLS_SORT in SQL statements.  

NLS_CURRENCY = 'text'  

explicitly specifies a new value for the L number format element (the local currency symbol). The symbol cannot exceed 10 characters.  

NLS_DATE_FORMAT = 'fmt'  

explicitly specifies a new default date format. The 'fmt' value must be a date format model as specified in the section "Date Format Elements".  

NLS_DATE_LANGUAGE = language  

explicitly changes the language for names and abbreviations of days and months, and for spelled-out values of other date format elements.  

NLS_ISO_CURRENCY = territory  

explicitly specifies the territory whose ISO currency symbol should be used. That territory's currency symbol then becomes the value of the C number format element.  

NLS_LANGUAGE = language  

changes the language in which Oracle returns errors and other messages. This parameter also implicitly specifies new values for these items:  

• language for day and month names and abbreviations and spelled values of other elements

• linguistic sort sequences or binary sorts

• B.C. and A.D. indicators

• A.M. and P.M. meridian indicators

NLS_NUMERIC_CHARACTERS = 'text'  

explicitly specifies a new decimal character and group separator. The 'text' value must have this form:

'dg'

where: d is the new decimal character, and g is the new group separator.  

The decimal character and the group separator must be two different single-byte characters, and cannot be a numeric value or any of the following characters: plus sign ("+"), minus sign or hyphen ("-" ), less-than sign ("<"), or greater-than sign (">").  

If the decimal character is not a period (.), you must use single quotation marks to enclose all number values that appear in expressions in your SQL statements. When not using a period for the decimal point, use the TO_NUMBER function to ensure that a valid number is retrieved.  

NLS_SORT = { sort | BINARY}  

changes the sequence into which Oracle sorts character values. sort specifies the name of a linguistic sort sequence. BINARY specifies a binary sort. The default is BINARY.  

NLS_TERRITORY = territory  

implicitly specifies new values for these items:  

• default date format

• decimal character and group separators

• local currency symbol

• ISO currency symbol

• first day of the week for D date format element

NLS_DUAL_CURRENCY = 'text'  

explicitly specifies a new "Euro" (or other) dual currency symbol. The value of text is returned by the number format element U (see "Number Format Elements"); text cannot exceed 10 characters.  

OBJECT_CACHE_MAX_SIZE_PERCENT = integer  

specifies the percentage of the optimal cache size that the session object cache can grow beyond the optimal size. The default is 10.  

OBJECT_CACHE_OPTIMAL_SIZE = integer  

specifies (in kilobytes) the size to which the session object cache is reduced when it exceeds maximum size. The default is 100.  

OPTIMIZER_INDEX_CACHING = integer  

lets you tune the optimizer to favor nested loops joins. The value of integer indicates the percentage of the index blocks assumed to be in the cache.  

OPTIMIZER_INDEX_COST_ADJ = integer  

let you tune optimizer behavior for access path selection to make the optimizer more likely to select an index access path than a full table scan. The value of integer is a percentage indicating the importance the optimizer attaches to the index path compared with "normal". The default is 100 (indicating 100%), which makes the optimizer cost index access paths at the regular cost.  

OPTIMIZER_MAX_PERMUTATIONS = integer  

lets you limit the amount of work the optimizer expends on optimizing queries with large joins. The value of integer is the number of permutations of the tables the optimizer will consider with large joins.  

OPTIMIZER_MODE = { ALL_ROWS | FIRST_ROWS | RULE | CHOOSE }  

specifies the approach and mode of the optimizer for your session. For information on how to choose a goal for the cost-based approach based on the characteristics of your application, see Oracle8i Concepts and Oracle8i Tuning.  

• ALL_ROWS specifies the cost-based approach and optimizes for best throughput.

• FIRST_ROWS specifies the cost-based approach and optimizes for best response time.

• RULE specifies the rule-based approach. (The rule-based optimizer does not use function-based indexes.)

• CHOOSE causes the optimizer to choose an optimization approach based on the presence of statistics in the data dictionary.

OPTIMIZER_PERCENT_PARALLEL = integer  

specifies the amount of parallelism the optimizer uses in its cost functions. The default is 0 (no parallelism).  

OPTIMIZER_SEARCH_LIMIT = integer  

specifies the search limit for the optimizer. The default is 5.  

PARALLEL_BROADCAST_ENABLED = { TRUE | FALSE }  

lets you enhance performance during hash and merge joins.  

PARALLEL_INSTANCE_GROUP = ' text '  

identifies the parallel instance group to be used for spawning parallel query slaves. The default is all active instances. Set this parameter only if you are running Oracle Parallel Server in parallel mode.  

PARALLEL_MIN_PERCENT = integer  

specifies the minimum percent of threads required for parallel query. The default is 0 (no parallelism).  

PARTITION_VIEW_ENABLED = { TRUE | FALSE }  

When set to TRUE, this parameter causes the optimizer to skip unnecessary table accesses in a partition view.  

Note: For important information on partition views, see "Partition Views".  

PLSQL_V2_COMPATIBILITY = { TRUE | FALSE }  

if TRUE, modifies the compile-time behavior of PL/SQL programs to allow language constructs that are illegal in Oracle8 and Oracle8i (PL/SQL V3), but were legal in Oracle7 (PL/SQL V2). FALSE disallows illegal Oracle7 PL/SQL V2 constructs. This is the default.

See the PL/SQL User's Guide and Reference and Oracle8i Reference for more information about this session parameter.  

REMOTE_DEPENDENCIES_MODE = { TIMESTAMP | SIGNATURE }  

specifies how dependencies of remote stored procedures are handled by the session. For more information, refer to Oracle8i Application Developer's Guide - Fundamentals.  

QUERY_REWRITE_ENABLED = { TRUE | FALSE }  

enables or disables query rewrite on all materialized views that have not been explicitly disabled. Query rewrite is disabled by default. It is also disabled by rule-based optimization (that is, if the OPTIMIZER_MODE parameter is set to RULE).  

This parameter has the following additional effect on the use of function-based indexes:

• If this parameter is set to TRUE, Oracle will use function-based indexes to derive values of SQL expressions. If in addition the QUERY_REWRITE_INTEGRITY parameter is set to any value other than ENFORCED, Oracle will derive such values even if the index is based on a user-defined (rather than SQL) function.

• If this parameter is set to FALSE, Oracle will not use function-based indexes to derive values of SQL expressions, but it will use such indexes to obtain values of real columns in the index.

Enabling or disabling query rewrite does not affect descending indexes.

For more information on query rewrite, see Oracle8i Tuning.  

QUERY_REWRITE_INTEGRITY = { ENFORCED | TRUSTED | STALE_TOLERATED }  

sets the minimum consistency level for query rewrite. The following values are permitted:  

• ENFORCED is the safest level. It relies only on system-enforced relationships so that data integrity and correctness can be guaranteed. This level ensures that query rewrite will not use any function-based index or any materialized view that includes a call to a user-defined function.

  In addition, this level ensures that query rewrite will not use any dimensional information or any constraints enabled with the RELY keyword.

• TRUSTED specifies that materialized views created with the ON PREBUILT TABLE clause are supported, and trusted but unenforced join relationships are accepted. Query rewrite uses join information from dimensions and enables unenforced constraints with the RELY keyword.

• STALE_TOLERATED specifies that any stale, usable materialized view may be used.

This parameter does not affect descending indexes.

For more information on query rewrite integrity level, see Oracle8i Tuning. For information on dimensions, see "CREATE DIMENSION". For information on constraints enabled with the RELY keyword, see "constraint_clause".  

SESSION_CACHED_CURSORS = integer  

specifies the number of frequently used cursors that can be retained in the cache. The cursors can be open or closed, which is particularly useful for Oracle tools that close all session cursors associated with a form when switching to another form. In such cases, frequently used cursors do not have to be reparsed. A least recently used algorithm ages out entries in the cache to make room for new entries when needed. For more information on session cursor caching, see Oracle8i Tuning.  

SKIP_UNUSABLE_INDEXES = { TRUE | FALSE }  

controls the use and reporting of tables with unusable indexes or index partitions.  

• TRUE disables error reporting of indexes marked UNUSABLE. Allows inserts, deletes, and updates to tables with unusable indexes or index partitions.

• FALSE enables error reporting of indexes marked UNUSABLE. Does not allow inserts, deletes, and updates to tables with unusable indexes or index partitions. This is the default.

SORT_AREA_RETAINED_SIZE = integer  

specifies (in bytes) the maximum amount of memory that each sort operation will retain after the first fetch is done, until the cursor ends. The default is the value of the SORT_AREA_SIZE parameter.  

SORT_AREA_SIZE = integer  

specifies (in bytes) the maximum amount of memory to use for each sort operation. The default is OS-dependent.  

SORT_MULTIBLOCK_READ_COUNT = integer  

specifies the number of database blocks to read each time a sort performs a read from temporary segments. The default is 2.  

SQL_TRACE = { TRUE | FALSE }  

The SQL trace facility generates performance statistics for the processing of SQL statements. When you begin a session, Oracle enables or disables the SQL trace facility based on the value of this parameter. You can subsequently enable or disable the SQL trace facility for your own session with the SQL_TRACE parameter of the ALTER SESSION statement. TRUE enables the SQL trace facility. FALSE disables it.

For more information on the SQL trace facility, including how to format and interpret its output, see Oracle8i Tuning.  

STAR_TRANSFORMATION_ENABLED = { TRUE | FALSE }  

determines whether a cost-based query transformation will be applied to star queries. The default is FALSE.  

TIMED_STATISTICS = {TRUE | FALSE }  

specifies whether the server requests the time from the operating system when generating time-related statistics. The default is FALSE.  

USE_STORED_OUTLINES = { TRUE | FALSE | 'category_name' }  

determines whether the optimizer will use stored outlines to generate execution plans. USE_STORED_OUTLINES is not an initialization parameter.  

• TRUE causes the optimizer to use outlines stored in the DEFAULT category when compiling requests.

• FALSE specifies that the optimizer should not use stored outlines. This is the default.

• category_name causes the optimizer to use outlines stored in the category_name category when compiling requests.

archive_log_clause  

manually archives redo log files or enables or disables automatic archiving. To use this clause, your instance must have the database mounted. The database can be either open or closed unless otherwise noted.  

Notes:

• You can also manually archive redo log file groups with the ARCHIVE LOG SQL*Plus statement. For information on this statement, see the SQL*Plus User's Guide and Reference.

• You can also have Oracle archive redo log files groups automatically. For information on automatic archiving, see Oracle8i Administrator's Guide. You can always manually archive redo log file groups regardless of whether automatic archiving is enabled.

THREAD  

specifies the thread containing the redo log file group to be archived. Set this parameter only if you are using Oracle with the Parallel Server option in parallel mode.  

SEQUENCE  

manually archives the online redo log file group identified by the log sequence number integer in the specified thread. If you omit the THREAD parameter, Oracle archives the specified group from the thread assigned to your instance.  

CHANGE  

manually archives the online redo log file group containing the redo log entry with the system change number (SCN) specified by integer in the specified thread. If the SCN is in the current redo log file group, Oracle performs a log switch. If you omit the THREAD parameter, Oracle archives the groups containing this SCN from all enabled threads. You can use this clause only when your instance has the database open.  

CURRENT  

manually archives the current redo log file group of the specified thread, forcing a log switch. If you omit the THREAD parameter, Oracle archives all redo log file groups from all enabled threads, including logs previous to current logs. You can use this clause only when your instance has the database open.  

Note: If you specify a redo log file group for archiving with the CHANGE or CURRENT clause, and earlier redo log file groups are not yet archived, Oracle archives all unarchived groups up to and including the specified group.  

GROUP  

manually archives the online redo log file group with the GROUP value specified by integer. You can determine the GROUP value for a redo log file group by examining the data dictionary view DBA_LOG_FILES. If you specify both the THREAD and GROUP parameters, the specified redo log file group must be in the specified thread.  

LOGFILE  

manually archives the online redo log file group containing the redo log file member identified by 'filename'. If you specify both the THREAD and LOGFILE parameters, the specified redo log file group must be in the specified thread.  

Restriction: You must archive redo log file groups in the order in which they are filled. If you specify a redo log file group for archiving with the LOGFILE parameter, and earlier redo log file groups are not yet archived, Oracle returns an error.  

NEXT  

manually archives the next online redo log file group from the specified thread that is full but has not yet been archived. If you omit the THREAD parameter, Oracle archives the earliest unarchived redo log file group from any enabled thread.  

ALL  

manually archives all online redo log file groups from the specified thread that are full but have not been archived. If you omit the THREAD parameter, Oracle archives all full unarchived redo log file groups from all enabled threads.  

START  

enables automatic archiving of redo log file groups.

Restriction: You can enable automatic archiving only for the thread assigned to your instance.  

TO 'location'  

specifies the primary location to which the redo log file groups are archived. The value of this parameter must be a fully specified file location following the conventions of your operating system. If you omit this parameter, Oracle archives the redo log file group to the location specified by the initialization parameters LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_n.  

Note: You can enhance recovery reliability by setting the related archive parameters LOG_ARCHIVE_DEST_DUPLEX and LOG_ARCHIVE_MIN_SUCCEED_DEST.  

STOP  

disables automatic archiving of redo log file groups. You can disable automatic archiving only for the thread assigned to your instance.  

CHECKPOINT  

explicitly forces Oracle to perform a checkpoint, ensuring that all changes made by committed transactions are written to datafiles on disk. You can specify this clause only when your instance has the database open. Oracle does not return control to you until the checkpoint is complete.  

GLOBAL  

in an Oracle Parallel Server environment, performs a checkpoint for all instances that have opened the database. This is the default.  

LOCAL  

in an Oracle Parallel Server environment, performs a checkpoint only for the thread of redo log file groups for your instance.  

For more information on checkpoints, see Oracle8i Concepts.  

CHECK DATAFILES  

in a distributed database system, such as an Oracle Parallel Server environment, updates an instance's SGA from the database control file to reflect information on all online datafiles.  

GLOBAL  

performs this synchronization for all instances that have opened the database. This is the default.  

LOCAL  

performs this synchronization only for the local instance.  

Your instance should have the database open. For more information, see Oracle8i Parallel Server Concepts and Administration.  

DISCONNECT SESSION ... POST_TRANSACTION  

disconnects the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a multi-threaded server). This clause allows ongoing transactions to complete before the session is disconnected, in contrast to the KILL SESSION clause.To use this clause, your instance must have the database open.  

If system parameters are appropriately configured, application failover will take effect. For more information about application failover see Oracle8i Tuning and Oracle8i Parallel Server Concepts and Administration. You must identify the session with both of the following values from the V$SESSION view:  

integer1  

is the value of the SID column.  

integer2  

is the value of the SERIAL# column.  

DISTRIBUTED RECOVERY  

specifies whether or not distributed recovery is enabled. To use this clause, your instance must have the database open.  

ENABLE  

enables distributed recovery. In a single-process environment, you must use this clause to initiate distributed recovery.  

You may need to issue the ENABLE DISTRIBUTED RECOVERY statement more than once to recover an in-doubt transaction if the remote node involved in the transaction is not accessible. In-doubt transactions appear in the data dictionary view DBA_2PC_PENDING. For more information about distributed transactions and distributed recovery, see Oracle8i Distributed Database Systems.  

DISABLE  

disables distributed recovery.  

RESTRICTED SESSION  

specifies whether logon to Oracle is restricted  

ENABLE  

allows only users with RESTRICTED SESSION system privilege to log on to Oracle. Existing sessions are not terminated.  

DISABLE  

reverses the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle. This is the default.  

You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.  

FLUSH SHARED_POOL  

clears all data from the shared pool in the system global area (SGA). The shared pool stores

• cached data dictionary information and

• shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.

This statement does not clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.  

KILL SESSION  

terminates a session, rolls back ongoing transactions, releases all session locks, and frees all session resources. To use this clause, your instance must have the database open. You must identify the session with both of the following values from the V$SESSION view:  

integer1  

is the value of the SID column.  

integer2  

is the value of the SERIAL# column.  

If the session is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, Oracle waits for this activity to complete, kills the session, and then returns control to you. If the waiting lasts a minute, Oracle marks the session to be killed and returns control to you with a message that the session is marked to be killed. Oracle then kills the session when the activity is complete.

Restriction: You can kill a session only on the same instance as your current session.  

SWITCH LOGFILE  

explicitly forces Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. When you force a log switch, Oracle begins to perform a checkpoint. Oracle returns control to you immediately rather than when the checkpoint is complete. To use this clause, your instance must have the database open.  

SUSPEND  

suspends all I/O (datafile, control file, and file header) as well as queries, in all instances, enabling you to make copies of the database without having to handle ongoing transactions.  

Restrictions:

• Do not use this clause unless you have put the database tablespaces in hot backup mode.

• If you start a new instance while the system is suspended, that new instance will not be suspended.

RESUME  

makes the database available once again for queries and I/O.

For more information on the SUSPEND clause and RESUME clause, refer to Oracle8i Backup and Recovery Guide.  

set_clause  

sets the system parameters that follow. You can set values for multiple parameters in the same set_clause.

The DEFERRED keyword sets or modifies the value of the parameter for future sessions that connect to the database.  

CAUTION: Unless otherwise noted, these parameters are initialization parameters, and the descriptions provided here indicate only the general nature of the parameters. Before changing the values of initialization parameters, please refer to their full description in Oracle8i Reference and Oracle8i National Language Support Guide.  

AQ_TM_PROCESSES = integer  

is an Advanced Queuing parameter that specifies whether a time manager process is created. Accepted values are 1 (creates one time manager process to monitor messages) and 0 (does not create a time manager process).  

BACKGROUND_DUMP_DEST = 'text'  

specifies the pathname for a directory where debugging trace files for the background processes are written during Oracle operations.  

BACKUP_TAPE_IO_SLAVES = {TRUE | FALSE} [DEFERRED]  

specifies whether I/O slaves are used by the Recovery Manager to back up, copy, or restore data to tape.  

CONTROL_FILE_RECORD_KEEP_TIME = integer [DEFERRED]  

specifies (in days) the minimum age of a record in a reusable control file section at which the record can be reused.  

CREATE_STORED_OUTLINES = { TRUE | FALSE | 'category_name' } [NOOVERRIDE]  

determines whether Oracle should automatically create and store an outline for each query submitted on the system. CREATE_STORED_OUTLINES is not an initialization parameter.  

• TRUE enables automatic outline creation for subsequent queries in the system. These outlines receive a unique system-generated name and are stored in the DEFAULT category. If a particular query already has an outline defined for it in the DEFAULT category, that outline will remain and a new outline will not be created.

• FALSE disables automatic outline creation for the system. This is the default.

• category_name has the same behavior as TRUE except that any outline created in the system is stored in the category_name category.

• NOOVERRIDE specifies that this system setting will not override the setting for any session in which this parameter was explicitly set. If you do not specify NOOVERRIDE, this setting takes effect in all sessions.

DB_BLOCK_CHECKING = {TRUE | FALSE} DEFERRED  

controls whether data block checking is done. The default is FALSE, for compatibility with earlier releases where block checking is disabled as a default.  

DB_BLOCK_CHECKSUM = {TRUE | FALSE}  

specifies whether the database writer background process and the direct loader will calculate a checksum and store it in the cache header of every data lock when writing to disk.  

DB_BLOCK_MAX_DIRTY_TARGET = integer  

limits to integer the number of dirty buffers in the cache and the number of buffers that will need to be read during crash or instance recovery. This parameter does not relate to media recovery. A value of 0 disables this parameter. The minimum accepted value to enable the parameter is 1000.  

DB_FILE_MULTIBLOCK_READ_COUNT = integer  

specifies the maximum number of blocks read in one I/O operation during a sequential scan.  

FAST_START_IO_TARGET  

specifies the target number of IOs (reads and writes) to and from buffer cache that Oracle should perform upon crash or instance recovery. Oracle continuously calculates the actual number of IOs that would be needed for recovery and compares that number against the target. If the actual number is greater than the target, Oracle attempts to write additional dirty buffers to advance the checkpoint, while minimizing the affect on performance.

For information on how to tune this parameter, see Oracle8i Tuning.  

FAST_START_PARALLEL_ROLLBACK = { FALSE | LOW | HIGH}  

specifies the number of processes spawned to perform parallel recovery.

• FALSE specifies no parallel recovery. SMON will serially recover dead transactions.

• LOW specifies that the number of recovery servers may not exceed twice the value of the CPU_COUNT parameter.

• HIGH specifies that the number of recovery servers may not exceed four times the value of the CPU_COUNT parameter.

FIXED_DATE = { 'DD_MM_YY' | 'YYYY_MI_DD_HH24_MI-SS' }  

specifies a constant date for SYSDATE instead of the current date.  

GC_DEFER_TIME = integer  

specifies the time (in hundredths of seconds) that Oracle waits before responding to forced-write requests from other instances.  

GLOBAL_NAMES = {TRUE | FALSE}  

When you start an instance, Oracle determines whether to enforce global name resolution for remote objects accessed in SQL statements based on the value of the initialization parameter GLOBAL_NAMES. This system parameter enables or disables global name resolution while your instance is running. TRUE enables the enforcement of global names. FALSE disables the enforcement of global names. You can also enable or disable global name resolution for your session with the GLOBAL_NAMES parameter of the ALTER SESSION statement.

Oracle recommends that you enable global name resolution if you use or plan to use distributed processing. For more information on global name resolution and how Oracle enforces it, see "Referring to Objects in Remote Databases" and Oracle8i Distributed Database Systems.  

HASH_MULTIBLOCK_IO_COUNT = integer  

specifies the number of data blocks to read and write during a hash join operation. The value multiplied by the DB_BLOCK_SIZE initialization parameter should not exceed 64 K. The default value for this parameter is 1. If the multi-threaded server is used, the value is always 1, and any value given here is ignored.  

HS_AUTOREGISTER = {TRUE | FALSE}  

enables or disables automatic self-registration of non-Oracle system characteristics in the Oracle server's data dictionary by Heterogeneous Services agents. For more information on accessing non-Oracle systems through Heterogeneous Services, see Oracle8i Distributed Database Systems.  

JOB_QUEUE_PROCESSES = integer  

specifies the number of job queue processes per instance (SNPn, where n is 0 to 9 followed by A to Z). Set this parameter to 1 or higher if you wish to have your snapshots updated automatically. One job queue process is usually sufficient unless you have many snapshots that refresh simultaneously.

Oracle also uses job queue processes to process requests created by the DBMS_JOB package. For more information on managing table snapshots, see Oracle8i Replication.  

LICENSE_MAX_SESSIONS = integer  

resets (for the current instance) the value of the initialization parameter LICENSE_MAX_SESSIONS, which establishes the concurrent usage licensing limit, or the limit for concurrent sessions. Once this limit is reached, only users with RESTRICTED SESSION system privilege can connect. A value of 0 disables the limit.  

If you reduce the limit on sessions below the current number of sessions, Oracle does not end existing sessions to enforce the new limit. However, users without RESTRICTED SESSION system privilege can begin new sessions only when the number of sessions falls below the new limit.  

Do not disable or raise session limits unless you have appropriately upgraded your Oracle license. For more information, contact your Oracle sales representative.  

LICENSE_MAX_USERS = integer  

resets (for the current instance) the value of the initialization parameter LICENSE_MAX_USERS, which establishes the limit for users connected to your database. Once this limit is reached, more users cannot connect. A value of 0 disables the limit.  

Restriction: You cannot reduce the limit on users below the current number of users created for the database.

Do not disable or raise user limits unless you have appropriately upgraded your Oracle license. For more information, contact your Oracle sales representative.  

LICENSE_SESSIONS_WARNING = integer  

resets (for the current instance) the value of the initialization parameter LICENSE_SESSIONS_WARNING, which establishes a warning threshold for concurrent usage. Once this threshold is reached, Oracle writes warning messages to the database ALERT file for each subsequent session. Also, users with RESTICTED SESSION system privilege receive warning messages when they begin subsequent sessions. A value of 0 disables the warning threshold.  

If you reduce the warning threshold for sessions below the current number of sessions, Oracle writes a message to the ALERT file for all subsequent sessions.  

LOG_ARCHIVE_DEST = string  

specifies a valid operating system pathname as the primary destination for all archive redo log file groups.  

Restrictions: If you set a value for this parameter:

• You cannot have a value for LOG_ARCHIVE_DEST_n in your initialization parameter file, nor can you set a value for that parameter using the ALTER SESSION or ALTER SYSTEM statement.

• You cannot set a value for the parameter LOG_ARCHIVE_MIN_SUCCEED_DEST using the ALTER SESSION statement.

LOG_ARCHIVE_DEST_n = null_string

                 |{LOCATION=pathname | SERVICE=servicename}
                 [MANDATORY | OPTIONAL]
                 [REOPEN[=retry_time_in_seconds]]

specifies up to five valid operating system pathnames or Oracle service names (plus other related options) as destinations for archive redo log file groups (n = integers 1 through 5). For a description of the options, refer to Oracle8i Reference.  

Restrictions: If you set a value for this parameter:

• You cannot have definitions for the parameters LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST in your initialization parameter file, nor can you set values for those parameters using the ALTER SYSTEM statement.

• You cannot start archiving to a specific location using the ALTER SYSTEM ARCHIVE LOG TO location statement.

LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER}  

specifies the state associated with the corresponding LOG_ARCHIVE_DEST_n parameter.

• ENABLE specifies that any associated valid destination can be used for archiving. This is the default.

• DEFER specifies that Oracle will not consider for archiving any destination associated with the corresponding LOG_ARCHIVE_DEST_n parameter.

LOG_ARCHIVE_DUPLEX_DEST = string  

specifies a valid operating system pathname as the secondary destination for all archive redo log file groups.  

Restriction: If you set a value for this parameter:

• You must have a definition for LOG_ARCHIVE_DEST.

• You cannot have a value for the parameter LOG_ARCHIVE_DEST_n in your initialization parameter file, nor can you set a value for that parameter using the ALTER SYSTEM or ALTER SESSION statement.

• You cannot set a value for the parameter LOG_ARCHIVE_MIN_SUCCEED_DEST using the ALTER SESSION statement.

LOG_ARCHIVE_MAX_PROCESSES = integer  

specifies the number of archiver processes that are invoked. Permitted values are integers 1 through 10, inclusive. The default is 1.  

LOG_ARCHIVE_MIN_SUCCEED_DEST = integer  

specifies the minimum number of destinations that must succeed in order for the online log file to be available for reuse.  

LOG_CHECKPOINT_INTERVAL = integer  

limits to integer the number of redo blocks that can exist between an incremental checkpoint and the last block written to the redo log.  

LOG_CHECKPOINT_TIMEOUT = integer  

limits the incremental checkpoint to be at the position where the last write to the redo log (sometimes called the "tail of the log") was integer seconds ago, and signifies that no buffer will remain dirty (in the cache) for more than integer seconds. The default is 1800 seconds.  

MAX_DUMP_FILE_SIZE = { size | 'UNLIMITED'} [DEFERRED]  

specifies the trace dump file size upper limit for all user sessions. Specify the maximum size as either a nonnegative integer that represents the number of blocks, or as 'UNLIMITED'. If you specify 'UNLIMITED', no upper limit is imposed.  

Multi-Threaded Server Parameters: When you start your instance, Oracle creates shared server processes and dispatcher processes for the multi-threaded server architecture based on the values of the MTS_SERVERS and MTS_DISPATCHERS initialization parameters. You can set the MTS_SERVERS and MTS_DISPATCHERS session parameters to perform one of the following operations while the instance is running:  

• Create additional shared server processes by increasing the minimum number of shared server processes.

• Terminate existing shared server processes after their current calls finish processing.

• Create more dispatcher processes for a specific protocol, up to a maximum across all protocols specified by the initialization parameter MTS_MAX_DISPATCHERS.

• Terminate existing dispatcher processes for a specific protocol after their current user processes disconnect from the instance.

For more information on multi-threaded server architecture, see Oracle8i Concepts, Oracle8i Tuning, and Oracle8i Parallel Server Concepts and Administration.  

MTS_DISPATCHERS = 'dispatch_clause'  

dispatch_clause::=

(PROTOCOL = protocol) |
( ADDRESS = address) | 
(DESCRIPTION = description )
[options_clause]

options_clause::=

(DISPATCHERS = integer |
 SESSIONS = integer |
 CONNECTIONS = integer |
 TICKS = seconds |
 POOL = { 1 | ON | YES | TRUE | BOTH | ({IN|OUT} = ticks) | 
          0 | OFF | NO | FALSE | ticks} | 
 MULTIPLEX = {1 | ON | YES | TRUE | 0 | OFF | NO | 
              FALSE | BOTH | IN | OUT} |
 LISTENER = tnsname |
 SERVICE = service |
 PRESENTATION = { TTC | RO | GIOP | ejb_presentation_class } |
 INDEX = integer)

modifies or creates the configuration of dispatcher processes.  

You can specify multiple MTS_DISPATCHERS parameters in a single statement for multiple network protocols. For more information on this parameter, see Net8 Administrator's Guide and Oracle8i Administrator's Guide.  

MTS_SERVERS = integer  

specifies a new minimum number of shared server processes.  

QUERY_REWRITE_ENABLED = { TRUE | FALSE } [DEFERRED | NOOVERRIDE]  

enables or disables query rewrite on all materialized views that have not been explicitly disabled. By default, TRUE enables query rewrite for all sessions immediately. Query rewrite is superseded and disabled by rule-based optimization (that is, if the OPTIMIZER_MODE parameter is set to RULE). Also enables or disables use of any function-based indexes defined on the materialized view.  

• DEFERRED specifies that query rewrite is enabled or disabled only for future sessions.

• NOOVERRIDE specifies that query rewrite is enabled or disabled for all sessions that have not explicitly set this parameter using ALTER SESSION.

Note: Enabling or disabling query rewrite does not affect queries that have already been compiled, even if they are reissued. Enabling or disabling query rewrite does not affect descending indexes. For more information on query rewrite, see Oracle8i Tuning.  

OBJECT_CACHE_MAX_SIZE_PERCENT = integer [DEFERRED]  

specifies the percentage of the optimal cache size that the session object cache can grow past the optimal size.  

OBJECT_CACHE_OPTIMAL_SIZE = integer [DEFERRED]  

specifies (in kilobytes) the size to which the session object cache is reduced if it exceeds the maximum size.  

PARALLEL_ADAPTIVE_MULTI_USER = {TRUE | FALSE}  

specifies that Oracle should vary the degree of parallelism based on the total perceived load on the system.  

PARALLEL_INSTANCE_GROUP = 'text'  

specifies the name of the Oracle Parallel Server instance group to be used for spawning parallel query slaves.  

PARALLEL_THREADS_PER_CPU = integer  

used to compute the degree of parallelism for parallel operations where the degree of parallelism is unset. The default is operating system dependent.  

PLSQL_V2_COMPATIBILITY = {TRUE | FALSE} [DEFERRED]  

modifies the compile-time behavior of PL/SQL programs to allow language constructs that are illegal in Oracle8 and Oracle8i (PL/SQL V3), but were legal in Oracle7 (PL/SQL V2). See PL/SQL User's Guide and Reference and Oracle8i Reference for more information about this system parameter.  

TRUE  

enables Oracle8i PL/SQL V3 programs to execute Oracle7 PL/SQL V2 constructs.  

FALSE  

disallows illegal Oracle7 PL/SQL V2 constructs. This is the default.  

REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE}  

specifies how dependencies of remote stored procedures are handled by the server. For more information, see Oracle8i Application Developer's Guide - Fundamentals.  

RESOURCE_LIMIT = {TRUE | FALSE}  

When you start an instance, Oracle enforces resource limits assigned to users based on the value of the RESOURCE_LIMIT initialization parameter. This system parameter enables or disables resource limits for subsequent sessions. TRUE enables resource limits. FALSE disables resource limits.  

Enabling resource limits only causes Oracle to enforce the resource limits already assigned to users. To choose resource limit values for a user, you must create a profile and assign that profile to the user. For more information, see "CREATE PROFILE" and "CREATE USER".  

RESOURCE_MANAGER_PLAN = plan_name  

specifies the name of the resource plan Oracle should use to allocate system resources among resource consumer groups. For information on resource consumer groups and resource plans, refer to Oracle8i Administrator's Guide.  

SORT_AREA_RETAINED_SIZE = integer DEFERRED  

specifies (in bytes) the maximum amount of memory that each sort operation will retain after the first fetch is done, until the cursor ends. The default is the value of the SORT_AREA_SIZE parameter.  

SORT_AREA_SIZE = integer DEFERRED  

specifies (in bytes) the maximum amount of memory to use for each sort operation. The default is operating system dependent.  

SORT_MULTIBLOCK_READ_COUNT = integer DEFERRED  

specifies the number of database blocks to read each time a sort performs a read from temporary segments. The default is 2.  

STANDBY_ARCHIVE_DEST = string  

specifies a valid operating system pathname as the standby database destination for the archive redo log files.  

TIMED_STATISTICS = {TRUE | FALSE}  

specifies whether the server requests the time from the operating system when generating time-related statistics. The default is FALSE.  

TIMED_OS_STATISTICS = integer  

specifies that operating system statistics will be collected when a request is made from a client to the server or when a request completes.  

TRANSACTION_AUDITING = {TRUE | FALSE} DEFERRED  

specifies whether the transaction layer generates a special redo record containing session and user information.  

USE_STORED_OUTLINES = { TRUE | FALSE | 'category_name' } [NOOVERRIDE]  

determines whether the optimizer will use stored outlines to generate execution plans. USE_STORED_OUTLINES is not an initialization parameter.  

• TRUE causes the optimizer to use outlines stored in the DEFAULT category when compiling requests.

• FALSE specifies that the optimizer should not use stored outlines. This is the default.

• category_name causes the optimizer to use outlines stored in the category_name category when compiling requests.

• NOOVERRIDE specifies that this system setting will not override the setting for any session in which this parameter was explicitly set. If you do not specify NOOVERRIDE, this setting takes effect in all sessions.

USER_DUMP_DEST = 'directory_name'  

specifies the pathname where Oracle will write debugging trace files on behalf of a user process.