Oracle8 Server Reference

Audience

Knowledge Assumed of the Reader

Installation and Migration Information

Information Database Administration

Application Design Information

How Oracle8 Server Reference Is Organized

Conventions Used in This Manual

Text of the Manual

Examples of Commands and Statements

Your Comments Are Welcome

Initialization Parameter File

Specifying Values in the Parameter File

Rules

Using Special Characters in Parameter Values

Using the Escape Character

Using Quotes

Changing Parameter Values

Dynamic Parameters

Displaying Current Parameter Values

Uses of Parameters

Types of Parameters

Derived Parameters

Global Cache Parameters with Prefix GC

Operating System-Dependent Parameters

Variable Parameters

Heterogeneous Services Parameters

Parameters You Should Not Specify in the Parameter File

When Parameters Are Set Incorrectly

Reading the Parameter Descriptions

PARAMETER_NAME

Parameter Descriptions

O7_DICTIONARY_ACCESSIBILITY

ALLOW_PARTIAL_SN_RESULTS

BACKUP_DISK_IO_SLAVES

BACKUP_TAPE_IO_SLAVES

BITMAP_MERGE_AREA_SIZE

BLANK_TRIMMING

CACHE_SIZE_THRESHOLD

CLEANUP_ROLLBACK_ENTRIES

CLOSE_CACHED_OPEN_CURSORS

COMMIT_POINT_STRENGTH

COMPATIBLE

COMPATIBLE_NO_RECOVERY

CONTROL_FILE_RECORD_KEEP_TIME

CONTROL_FILES

CORE_DUMP_DEST

CPU_COUNT

CREATE_BITMAP_AREA_SIZE

CURSOR_SPACE_FOR_TIME

DB_BLOCK_BUFFERS

DB_BLOCK_CHECKPOINT_BATCH

DB_BLOCK_CHECKSUM

DB_BLOCK_LRU_EXTENDED_STATISTICS

DB_BLOCK_LRU_LATCHES

DB_BLOCK_LRU_STATISTICS

DB_BLOCK_MAX_DIRTY_TARGET

DB_BLOCK_SIZE

DB_DOMAIN

DB_FILES

DB_FILE_DIRECT_IO_COUNT

DB_FILE_MULTIBLOCK_READ_COUNT

DB_FILE_NAME_CONVERT

DB_FILE_SIMULTANEOUS_WRITES

DB_NAME

DBLINK_ENCRYPT_LOGIN

DBWR_IO_SLAVES

DELAYED_LOGGING_BLOCK_CLEANOUTS

DISCRETE_TRANSACTIONS_ENABLED

DISK_ASYNCH_IO

DISTRIBUTED_LOCK_TIMEOUT

DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME

DISTRIBUTED_TRANSACTIONS

FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY

HASH_MULTIBLOCK_IO_COUNT

LICENSE_SESSIONS_WARNING

LOG_ARCHIVE_BUFFER_SIZE

LOG_ARCHIVE_BUFFERS

LOG_ARCHIVE_DEST

LOG_ARCHIVE_DUPLEX_DEST

LOG_ARCHIVE_FORMAT

LOG_ARCHIVE_MIN_SUCCEED_DEST

LOG_ARCHIVE_START

LOG_BLOCK_CHECKSUM

LOG_BUFFER

LOG_CHECKPOINT_INTERVAL

LOG_CHECKPOINT_TIMEOUT

LOG_CHECKPOINTS_TO_ALERT

LOG_FILE_NAME_CONVERT

LOG_FILES

LOG_SIMULTANEOUS_COPIES

LOG_SMALL_ENTRY_MAX_SIZE

MAX_COMMIT_PROPAGATION_DELAY

MAX_DUMP_FILE_SIZE

MAX_ENABLED_ROLES

MAX_ROLLBACK_SEGMENTS

MAX_TRANSACTION_BRANCHES

MTS_MULTIPLE_LISTENERS

NLS_NUMERIC_CHARACTERS

NLS_SORT

NLS_TERRITORY

OBJECT_CACHE_MAX_SIZE_PERCENT

OBJECT_CACHE_OPTIMAL_SIZE

OPEN_CURSORS

OPEN_LINKS

OPEN_LINKS_PER_INSTANCE

OPS_ADMIN_GROUP

OPTIMIZER_MODE

OPTIMIZER_PERCENT_PARALLEL

OPTIMIZER_SEARCH_LIMIT

ORACLE_TRACE_COLLECTION_NAME

ORACLE_TRACE_COLLECTION_PATH

ORACLE_TRACE_COLLECTION_SIZE

ORACLE_TRACE_ENABLE

ORACLE_TRACE_FACILITY_NAME

ORACLE_TRACE_FACILITY_PATH

OS_AUTHENT_PREFIX

OS_ROLES

PARALLEL_DEFAULT_MAX_INSTANCES

PARALLEL_INSTANCE_GROUP

PARALLEL_MAX_SERVERS

PARALLEL_MIN_MESSAGE_POOL

PARALLEL_MIN_PERCENT

PARALLEL_MIN_SERVERS

PARALLEL_SERVER

PARALLEL_SERVER_IDLE_TIME

PARALLEL_TRANSACTION_RESOURCE_TIMEOUT

PARTITION_VIEW_ENABLED

PLSQL_V2_COMPATIBILITY

REMOTE_DEPENDENCIES_MODE

REMOTE_LOGIN_PASSWORDFILE

REMOTE_OS_AUTHENT

REMOTE_OS_ROLES

REPLICATION_DEPENDENCY_TRACKING

SEQUENCE_CACHE_ENTRIES

SEQUENCE_CACHE_HASH_BUCKET

SERIAL _REUSE

SESSION_CACHED_CURSORS

SESSION_MAX_OPEN_FILES

SESSIONS

SHADOW_CORE_DUMP

SHARED_POOL_RESERVED_MIN_ALLOC

SHARED_POOL_RESERVED_SIZE

SHARED_POOL_SIZE

SORT_AREA_RETAINED_SIZE

SORT_WRITE_BUFFER_SIZE

STAR_TRANSFORMATION_ENABLED

TAPE_ASYNCH_IO

TEMPORARY_TABLE_LOCKS

TRANSACTIONS_PER_ROLLBACK_SEGMENT

USE_ISM

USER_DUMP_DEST

UTL_FILE_DIR

Data Dictionary Views

ALL_PART_COL_STATISTICS

ALL_REGISTERED_SNAPSHOTS

ALL_REPGROUPED_COLUMN

ALL_REPKEY_COLUMNS

ALL_REPOBJECT

ALL_REPPARAMETER_COLUMN

ALL_REPPRIORITY

ALL_REPPRIORITY_GROUP

ALL_REPPROP

ALL_REPRESOLUTION

ALL_REPRESOL_STATS_CONTROL

ALL_REPRESOLUTION_METHOD

ALL_REPRESOLUTION_STATISTICS

ALL_TAB_COL_STATISTICS

ALL_UPDATABLE_COLUMNS

DBA_FREE_SPACE_COALESCED

DBA_PART_COL_STATISTICS

DBA_REGISTERED_SNAPSHOT_GROUPS

DBA_REGISTERED_SNAPSHOTS

DBA_REPGROUPED_COLUMN

DBA_REPKEY_COLUMNS

DBA_REPOBJECT

DBA_REPPARAMETER_COLUMN

DBA_REPPRIORITY

DBA_REPPRIORITY_GROUP

DBA_REPPROP

DBA_REPRESOLUTION

DBA_REPRESOLUTION_METHOD

DBA_REPRESOL_STATS_CONTROL

DBA_TAB_COL_STATISTICS

DBA_UPDATABLE_COLUMNS

HS_EXTERNAL_OBJECT_PRIVILEGES

HS_EXTERNAL_OBJECTS

HS_EXTERNAL_USER_PRIVILEGES

LOADER_CONSTRAINT_INFO

NLS_DATABASE_PARAMETERS

NLS_INSTANCE_PARAMETERS

NLS_SESSION_PARAMETERS

PRODUCT_COMPONENT_VERSION

STMT_AUDIT_OPTION_MAP

TS_PITR_OBJECTS_TO_BE_DROPPED

USER_PART_COL_STATISTICS

USER_PART_HISTOGRAMS

USER_PART_KEY_COLUMNS

USER_REFRESH_CHILDREN

USER_REPGROUPED_COLUMN

USER_REPKEY_COLUMNS

USER_REPOBJECT

USER_REPPARAMETER_COLUMN

USER_REPPRIORITY

USER_REPPRIORITY_GROUP

USER_REPPROP

USER_REPRESOLUTION

USER_REPRESOL_STATS_CONTROL

USER_REPRESOLUTION_METHOD

USER_REPRESOLUTION_STATISTICS

USER_TAB_COL_STATISTICS

USER_UPDATABLE_COLUMNS

USER_USERS

USER_VIEWS

Dynamic Performance Views

V$ Views

GV$ Views

Restrictions on GV$ Views

Access to the Dynamic Performance Tables

V$CONTROLFILE_RECORD_SECTION

V$FIXED_VIEW_DEFINITION

V$GLOBAL_TRANSACTION

V$INDEXED_FIXED_COLUMN

V$LOCKS_WITH_COLLISIONS

V$RECOVERY_FILE_STATUS

V$SESSION_CONNECT_INFO

V$SESSION_CURSOR_CACHE

V$SESSION_EVENT

V$SESSION_LONGOPS

V$SESSION_OBJECT_CACHE

V$SHARED_POOL_RESERVED

V$SQLTEXT_WITH_NEWLINES

V$STATNAME

V$SUBCACHE

V$SYSSTAT

V$SYSTEM_CURSOR_CACHE

V$TRANSACTION_ENQUEUE

V$TYPE_SIZE

V$VERSION

V$WAITSTAT

What Does National Language Support Provide?

Oracle Server NLS Architecture

Background Information

Character Encoding Schemes

Restrictions on Character Sets Used to Express Names and Text

Single-Byte 7-Bit Encoding Schemes

Single-Byte 8-Bit Encoding Schemes

Varying-Width Multi-Byte Encoding Schemes

Fixed-Width Multi-Byte Encoding Schemes

UTF8 Encoding

Arabic/Hebrew Display Character Set Support

Choosing Character Sets for Database Character Set and
National Character Set

Uses for the Database Character Set

Uses for the National Character Set

Guidelines for Choosing Character Sets

Specifying Language-Dependent Behavior

Specifying Language-Dependent Behavior for a Session

NLS_LANG

Specifying NLS_LANG

Client/Server Architecture

Overriding Language and Territory Specifications

Specifying Language-Dependent Application Behavior

Language-Dependent Functions

Messages and Text

Number Format

Date Format, Currency Symbols, and First Day of the Week

Sorting Character Data

Binary Sorts

Linguistic Sorts

Linguistic Special Cases

Specifying Default Language-Dependent Behavior

NLS_LANGUAGE

NLS_TERRITORY

Runtime Loadable NLS Data

Date Formats and Partition Bound Expressions

NLS_DATE_ LANGUAGE

NLS_ISO_CURRENCY

NLS_NUMERIC_CHARACTERS

NLS_SORT

Specifying Character Sets

Supported Character Sets

Character Set Conversion

The Concatenation Operator

Storing Data in Multi-Byte Character Sets

Loadable Character Sets

Date and Number Formats

Date Formats

Week and Day Number Conventions

Number Formats

Additional NLS Environment Variables

Client-Only Environment Variables

NLS_MONETARY_CHARACTERS

NLS_NCHAR

Using NLS Parameters in SQL Functions

Default Specifications

Specifying Parameters

Unacceptable Parameters

CONVERT Function

NLSSORT Function

String Comparisons in a WHERE Clause

Other SQL Functions

Partitioned Tables and Indexes

Controlling an ORDER BY Clause

Obsolete NLS Data

Unicode (UTF-8) Support

NLS Data

Supported Languages

Supported Territories

Storage Character Sets

Arabic/Hebrew Display Character Sets

Linguistic Definitions

Calendar Systems

Database Limits

Creating the Data Dictionary

Creating Additional Data Dictionary Structures

Parameter Descriptions

KOLF: Register LFI close

KOLF: Register LFI exists

KOLF: Register LFI isopen

KOLF: Register LFI length

KOLF: Register LFI lfimkm

KOLF: Register LFI lfimkpth

KOLF: Register LFI open

KOLF: Register LFI read

KOLF: Register LFI seek

PL/SQL lock timer

SQL*Net break/reset to client

SQL*Net break/reset to dblink

SQL*Net message from client

SQL*Net message from dblink

SQL*Net message to client

SQL*Net message to dblink

SQL*Net more data from client

SQL*Net more data from dblink

SQL*Net more data to client

SQL*Net more data to dblink

WMON goes to sleep

alter system set mts_dispatchers

batched allocate scn lock request

buffer busy waits

buffer deadlock

buffer for checkpoint

buffer latch

buffer read retry

checkpoint completed

checkpoint range buffer not saved

control file parallel write

control file sequential read

control file single write

conversion file read

db file parallel read

db file parallel write

db file scattered read

db file sequential read

duplicate cluster key

free global transaction table entry

free process state object

global cache freelist wait

global cache lock busy

global cache lock cleanup

global cache lock null to s

global cache lock null to x

global cache lock open null

global cache lock open s

global cache lock open ss

global cache lock open x

global cache lock s to x

inactive session

inactive transaction branch

index block split

instance recovery

instance state change

library cache load lock

library cache lock

library cache pin

lock manager wait for remote message

log buffer space

log file parallel write

log file sequential read

log file single write

log file switch (archiving needed)

log file switch (checkpoint incomplete)

log file switch (clearing log file)

log file switch completion

log file sync

log switch/archive

on-going SCN fetch to complete

parallel query create server

parallel query dequeue wait

parallel query qref latch

parallel query server shutdown

parallel query signal server

pending ast

pending global transaction(s)

rdbms ipc message block

switch logfile command

timer in sksawat

transaction

unbound tx

undo segment extension

undo segment recovery

undo segment tx slot

virtual circuit status

write complete waits

writes stopped by instance recovery

Introduction

Statistics Descriptions

CPU used by this session

CR blocks created

Current blocks converted for CR

DBWR Flush object call found no dirty buffers

DBWR Flush object cross instance calls

DBWR buffers scanned

DBWR checkpoints

DBWR cross instance writes

DBWR free buffers found

DBWR lru scans

DBWR make free requests

DBWR summed scan depth

DBWR timeouts

DDL statements parallelized

DML statements parallelized.

PX local messages recv'd

PX local messages sent

PX remote messages recv'd

PX remote messages sent

SQL*Net roundtrips to/from client

SQL*Net roundtrips to/from dblink

Unnecessary process cleanup for SCN batching

background checkpoints completed

background checkpoints started

bytes received via SQL*Net from client

bytes received via SQL*Net from dblink

bytes sent via SQL*Net to client

bytes sent via SQL*Net to dblink

calls to get snapshot scn: kcmgss

change write time

cluster key scan block gets

cluster key scans

commit cleanout failures: block lost

commit cleanout failures: buffer being written

commit cleanout failures: callback failure

commit cleanout failures: cannot pin

commit cleanout failures: hot backup in progress

commit cleanout failures: write disabled

commit cleanouts

commit cleanouts successfully completed

consistent changes

consistent gets

cross instance CR read

db block changes

db block gets

dirty buffers inspected

free buffer inspected

free buffer requested

global cache defers

global cache freelist waits

global cache hash latch waits

global lock convert time

global lock converts (async)

global lock converts (non async)

global lock get time

global lock gets (async)

global lock gets (non async)

global lock release time

global lock releases

kcmccs called get current scn

kcmccs read scn without going to DLM

kcmgss waited for batching

lock element waits

logons cumulative

logons current

next scns gotten without going to DLM

opened cursors cumulative

opened cursors current

opens of replaced files

opens requiring cache replacement

recovery array read time

redo entries linearized

redo log space requests

redo log space wait time

redo log switch interrupts

redo writer latching time

redo writes

remote instance undo block writes

remote instance undo header writes

remote instance undo requests

serializable aborts

session connect time

session cursor cache count

session cursor cache hits

session logical reads

session pga memory

session pga memory max

session stored procedure space

session uga memory

session uga memory max

sorts (disk)

sorts (memory)

sorts (rows)

summed dirty queue length

table fetch by rowid

table fetch continued row

table scan blocks gotten

table scan rows gotten

table scans (cache partitions)

table scans (direct read)

table scans (long tables)

table scans (rowid ranges)

table scans (short tables)

Contents

Title and Copyright Information

Preface

Chapter 1 Initialization Parameters

Chapter 2 Static Data Dictionary Views

Chapter 3 Dynamic Performance (V$) Views

Chapter 4 National Language Support

Chapter 5 Database Limits

Chapter 6 SQL Scripts

Appendix A Oracle Wait Events

Appendix B Enqueue and Lock Names

Appendix C Statistics Descriptions

Index