Oracle9i Database Reference

706 Pages·2001·5.865 MB·English

by  Morales T., Lorentz D.

Oracle9i
Database Reference
Release 1 (9.0.1)
July 2001
Part No. A90190-02
Oracle9i Database Reference, Release 1 (9.0.1)
Part No. A90190-02
Copyright © 1996, 2001, Oracle Corporation. All rights reserved.
Primary Authors: Tony Morales, Diana Lorentz
Contributors: Nipun Agarwal, Angela Amor, Rick Anderson, Lance Ashdown, Mark Bauer, Ruth
Baylis, Neerja Bhatt, George Candea, Wilson Chan, Sumanta Chatterjee, Eugene Chong, Michele Cyran,
MaryAnnDavidson,ConnieDialeris,HarveyEneman,BruceErnst,IraGreenberg,SteveHarris,Thuvan
Hoang, Lilian Hobbs, Namit Jain, Hakkan Jakobsson, Bob Jenkins, Mark Johnson, Jonathan Klein, Sushil
Kumar, Tirthankar Lahiri, Bill Lee, Nina Lewis, Juan Loaiza, Neil MacNaughton, Shankar Raman, Rita
Moran, Ari Mozes, Subramanian Muralidhar, Ravi Murthy, Karuna Muthiah, Arvind Nithrakashyap,
Ronald Obermarck, Cetin Ozbutun, Christopher Racicot, Mark Ramacher, Suvendu Ray, Ann Rhee,
Harlan Sexton, Helen Slattery, Ekrem Soylemez, Debbie Steiner, Seema Sundara, Juan Tellez, Alex
Tsukerman, Kothanda Umamageswaran, Randy Urbano, Steve Vivian, Eric Voss, Tak Wang, Steve
Wertheimer, Andy Witkowski, Daniel Wong, Graham Wood, Brian Wright, Aravind Yalamanchi, Qin Yu,
Mohamed Ziauddin
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and Oracle8i, Oracle9i, PL/SQL, and SQL*Plus are trademarks or
registered trademarks of Oracle Corporation. Other names may be trademarks of their respective
owners.
Contents
Send Us Your Comments............................................................................................................ xxxvii
Preface...................................................................................................................................................... xxxix
Audience................................................................................................................................................. xl
Organization........................................................................................................................................... xl
Related Documentation....................................................................................................................... xli
Conventions.......................................................................................................................................... xlii
Documentation Accessibility............................................................................................................ xliv
1 Initialization Parameters
Initialization Parameter File............................................................................................................. 1-2
Specifying Values in the Parameter File........................................................................................ 1-3
Rules Governing Parameter Files............................................................................................... 1-3
Using Special Characters in Parameter Values........................................................................ 1-5
Changing Parameter Values....................................................................................................... 1-6
Uses of Parameters..................................................................................................................... 1-10
Types of Parameters................................................................................................................... 1-10
Parameters You Should Not Specify in the Parameter File.................................................. 1-12
When Parameters Are Set Incorrectly..................................................................................... 1-12
Reading the Parameter Descriptions............................................................................................ 1-13
Parameter Descriptions.................................................................................................................... 1-14
ACTIVE_INSTANCE_COUNT...................................................................................................... 1-15
AQ_TM_PROCESSES..................................................................................................................... 1-15
ARCHIVE_LAG_TARGET............................................................................................................. 1-16
iii
AUDIT_FILE_DEST......................................................................................................................... 1-17
AUDIT_TRAIL.................................................................................................................................. 1-17
BACKGROUND_CORE_DUMP................................................................................................... 1-18
BACKGROUND_DUMP_DEST.................................................................................................... 1-18
BACKUP_TAPE_IO_SLAVES........................................................................................................ 1-19
BITMAP_MERGE_AREA_SIZE.................................................................................................... 1-20
BLANK_TRIMMING....................................................................................................................... 1-20
BUFFER_POOL_KEEP..................................................................................................................... 1-21
BUFFER_POOL_RECYCLE............................................................................................................. 1-22
CIRCUITS........................................................................................................................................... 1-23
CLUSTER_DATABASE................................................................................................................... 1-24
CLUSTER_DATABASE_INSTANCES.......................................................................................... 1-24
CLUSTER_INTERCONNECTS..................................................................................................... 1-25
COMMIT_POINT_STRENGTH.................................................................................................... 1-26
COMPATIBLE.................................................................................................................................... 1-27
CONTROL_FILE_RECORD_KEEP_TIME.................................................................................. 1-28
CONTROL_FILES............................................................................................................................. 1-28
CORE_DUMP_DEST....................................................................................................................... 1-29
CPU_COUNT..................................................................................................................................... 1-29
CREATE_BITMAP_AREA_SIZE................................................................................................... 1-30
CURSOR_SHARING....................................................................................................................... 1-31
CURSOR_SPACE_FOR_TIME....................................................................................................... 1-32
DB_nK_CACHE_SIZE..................................................................................................................... 1-33
DB_BLOCK_BUFFERS.................................................................................................................... 1-33
DB_BLOCK_CHECKING............................................................................................................... 1-35
DB_BLOCK_CHECKSUM.............................................................................................................. 1-35
DB_BLOCK_SIZE............................................................................................................................. 1-36
DB_CACHE_ADVICE..................................................................................................................... 1-37
DB_CACHE_SIZE............................................................................................................................. 1-38
DB_CREATE_FILE_DEST............................................................................................................... 1-38
DB_CREATE_ONLINE_LOG_DEST_n........................................................................................ 1-39
DB_DOMAIN.................................................................................................................................... 1-39
DB_FILE_MULTIBLOCK_READ_COUNT................................................................................. 1-40
DB_FILE_NAME_CONVERT......................................................................................................... 1-41
DB_FILES........................................................................................................................................... 1-43
iv
DB_KEEP_CACHE_SIZE................................................................................................................ 1-44
DB_NAME......................................................................................................................................... 1-44
DB_RECYCLE_CACHE_SIZE........................................................................................................ 1-45
DB_WRITER_PROCESSES............................................................................................................ 1-46
DBLINK_ENCRYPT_LOGIN......................................................................................................... 1-46
DBWR_IO_SLAVES......................................................................................................................... 1-47
DISK_ASYNCH_IO......................................................................................................................... 1-47
DISPATCHERS................................................................................................................................. 1-48
DISTRIBUTED_TRANSACTIONS.............................................................................................. 1-52
DML_LOCKS.................................................................................................................................... 1-52
DRS_START...................................................................................................................................... 1-53
ENQUEUE_RESOURCES............................................................................................................... 1-54
EVENT................................................................................................................................................ 1-55
FAL_CLIENT..................................................................................................................................... 1-55
FAL_SERVER..................................................................................................................................... 1-56
FAST_START_IO_TARGET........................................................................................................... 1-56
FAST_START_MTTR_TARGET.................................................................................................... 1-57
FAST_START_PARALLEL_ROLLBACK..................................................................................... 1-58
FIXED_DATE..................................................................................................................................... 1-58
GC_FILES_TO_LOCKS................................................................................................................... 1-59
GLOBAL_CONTEXT_POOL_SIZE.............................................................................................. 1-61
GLOBAL_NAMES............................................................................................................................ 1-61
HASH_AREA_SIZE......................................................................................................................... 1-62
HASH_JOIN_ENABLED................................................................................................................. 1-62
HI_SHARED_MEMORY_ADDRESS........................................................................................... 1-63
HS_AUTOREGISTER...................................................................................................................... 1-63
IFILE.................................................................................................................................................... 1-64
INSTANCE_GROUPS..................................................................................................................... 1-65
INSTANCE_NAME.......................................................................................................................... 1-65
INSTANCE_NUMBER.................................................................................................................... 1-66
JAVA_MAX_SESSIONSPACE_SIZE............................................................................................. 1-67
JAVA_POOL_SIZE............................................................................................................................ 1-67
JAVA_SOFT_SESSIONSPACE_LIMIT......................................................................................... 1-68
JOB_QUEUE_PROCESSES............................................................................................................. 1-68
LARGE_POOL_SIZE....................................................................................................................... 1-69
v
LICENSE_MAX_SESSIONS........................................................................................................... 1-70
LICENSE_MAX_USERS.................................................................................................................. 1-71
LICENSE_SESSIONS_WARNING............................................................................................... 1-71
LOCAL_LISTENER.......................................................................................................................... 1-72
LOCK_NAME_SPACE..................................................................................................................... 1-73
LOCK_SGA........................................................................................................................................ 1-73
LOG_ARCHIVE_DEST................................................................................................................... 1-74
LOG_ARCHIVE_DEST_n............................................................................................................... 1-75
LOG_ARCHIVE_DEST_STATE_n................................................................................................ 1-77
LOG_ARCHIVE_DUPLEX_DEST................................................................................................. 1-78
LOG_ARCHIVE_FORMAT............................................................................................................ 1-79
LOG_ARCHIVE_MAX_PROCESSES........................................................................................... 1-80
LOG_ARCHIVE_MIN_SUCCEED_DEST................................................................................... 1-81
LOG_ARCHIVE_START................................................................................................................. 1-82
LOG_ARCHIVE_TRACE................................................................................................................ 1-83
LOG_BUFFER.................................................................................................................................... 1-84
LOG_CHECKPOINT_INTERVAL................................................................................................. 1-85
LOG_CHECKPOINT_TIMEOUT.................................................................................................. 1-86
LOG_CHECKPOINTS_TO_ALERT.............................................................................................. 1-87
LOG_FILE_NAME_CONVERT..................................................................................................... 1-88
LOGMNR_MAX_PERSISTENT_SESSIONS.............................................................................. 1-89
MAX_COMMIT_PROPAGATION_DELAY................................................................................ 1-90
MAX_DISPATCHERS...................................................................................................................... 1-91
MAX_DUMP_FILE_SIZE................................................................................................................ 1-91
MAX_ENABLED_ROLES............................................................................................................... 1-92
MAX_ROLLBACK_SEGMENTS................................................................................................... 1-92
MAX_SHARED_SERVERS............................................................................................................. 1-93
MTS_CIRCUITS............................................................................................................................... 1-93
MTS_DISPATCHERS....................................................................................................................... 1-94
MTS_MAX_DISPATCHERS........................................................................................................... 1-98
MTS_MAX_SERVERS..................................................................................................................... 1-99
MTS_SERVERS................................................................................................................................. 1-99
MTS_SESSIONS............................................................................................................................. 1-100
NLS_CALENDAR........................................................................................................................... 1-101
NLS_COMP...................................................................................................................................... 1-102
vi
NLS_CURRENCY........................................................................................................................... 1-102
NLS_DATE_FORMAT................................................................................................................... 1-103
NLS_DATE_LANGUAGE............................................................................................................. 1-103
NLS_DUAL_CURRENCY............................................................................................................. 1-104
NLS_ISO_CURRENCY.................................................................................................................. 1-104
NLS_LANGUAGE.......................................................................................................................... 1-105
NLS_LENGTH_SEMANTICS...................................................................................................... 1-105
NLS_NCHAR_CONV_EXCP....................................................................................................... 1-106
NLS_NUMERIC_CHARACTERS............................................................................................... 1-107
NLS_SORT....................................................................................................................................... 1-107
NLS_TERRITORY.......................................................................................................................... 1-108
NLS_TIMESTAMP_FORMAT..................................................................................................... 1-109
NLS_TIMESTAMP_TZ_FORMAT.............................................................................................. 1-110
O7_DICTIONARY_ACCESSIBILITY......................................................................................... 1-110
OBJECT_CACHE_MAX_SIZE_PERCENT................................................................................ 1-111
OBJECT_CACHE_OPTIMAL_SIZE........................................................................................... 1-112
OPEN_CURSORS........................................................................................................................... 1-112
OPEN_LINKS.................................................................................................................................. 1-113
OPEN_LINKS_PER_INSTANCE................................................................................................. 1-114
OPTIMIZER_FEATURES_ENABLE........................................................................................... 1-115
OPTIMIZER_INDEX_CACHING............................................................................................... 1-116
OPTIMIZER_INDEX_COST_ADJ.............................................................................................. 1-117
OPTIMIZER_MAX_PERMUTATIONS...................................................................................... 1-117
OPTIMIZER_MODE...................................................................................................................... 1-118
ORACLE_TRACE_COLLECTION_NAME............................................................................... 1-119
ORACLE_TRACE_COLLECTION_PATH................................................................................. 1-120
ORACLE_TRACE_COLLECTION_SIZE................................................................................... 1-120
ORACLE_TRACE_ENABLE......................................................................................................... 1-121
ORACLE_TRACE_FACILITY_NAME....................................................................................... 1-122
ORACLE_TRACE_FACILITY_PATH......................................................................................... 1-123
OS_AUTHENT_PREFIX................................................................................................................ 1-123
OS_ROLES....................................................................................................................................... 1-124
PARALLEL_ADAPTIVE_MULTI_USER................................................................................... 1-125
PARALLEL_AUTOMATIC_TUNING........................................................................................ 1-126
PARALLEL_BROADCAST_ENABLED..................................................................................... 1-126
vii
PARALLEL_EXECUTION_MESSAGE_SIZE............................................................................ 1-127
PARALLEL_INSTANCE_GROUP............................................................................................... 1-128
PARALLEL_MAX_SERVERS....................................................................................................... 1-128
PARALLEL_MIN_PERCENT....................................................................................................... 1-129
PARALLEL_MIN_SERVERS........................................................................................................ 1-130
PARALLEL_THREADS_PER_CPU............................................................................................. 1-131
PARTITION_VIEW_ENABLED................................................................................................... 1-131
PGA_AGGREGATE_TARGET..................................................................................................... 1-132
PLSQL_COMPILER_FLAGS........................................................................................................ 1-133
PLSQL_NATIVE_C_COMPILER................................................................................................. 1-134
PLSQL_NATIVE_LIBRARY_DIR................................................................................................ 1-134
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT...................................................................... 1-135
PLSQL_NATIVE_LINKER............................................................................................................ 1-135
PLSQL_NATIVE_MAKE_FILE_NAME..................................................................................... 1-136
PLSQL_NATIVE_MAKE_UTILITY............................................................................................ 1-136
PLSQL_V2_COMPATIBILITY..................................................................................................... 1-137
PRE_PAGE_SGA............................................................................................................................. 1-137
PROCESSES..................................................................................................................................... 1-138
QUERY_REWRITE_ENABLED.................................................................................................... 1-139
QUERY_REWRITE_INTEGRITY................................................................................................ 1-140
RDBMS_SERVER_DN................................................................................................................... 1-140
READ_ONLY_OPEN_DELAYED................................................................................................ 1-141
RECOVERY_PARALLELISM....................................................................................................... 1-142
REMOTE_ARCHIVE_ENABLE................................................................................................... 1-142
REMOTE_DEPENDENCIES_MODE......................................................................................... 1-142
REMOTE_LISTENER..................................................................................................................... 1-143
REMOTE_LOGIN_PASSWORDFILE........................................................................................ 1-144
REMOTE_OS_AUTHENT............................................................................................................ 1-145
REMOTE_OS_ROLES................................................................................................................... 1-145
REPLICATION_DEPENDENCY_TRACKING......................................................................... 1-146
RESOURCE_LIMIT........................................................................................................................ 1-147
RESOURCE_MANAGER_PLAN................................................................................................. 1-147
ROLLBACK_SEGMENTS............................................................................................................. 1-148
ROW_LOCKING............................................................................................................................ 1-149
SERIAL _REUSE............................................................................................................................. 1-150
viii
SERVICE_NAMES......................................................................................................................... 1-151
SESSION_CACHED_CURSORS................................................................................................ 1-152
SESSION_MAX_OPEN_FILES.................................................................................................... 1-152
SESSIONS........................................................................................................................................ 1-153
SGA_MAX_SIZE............................................................................................................................. 1-154
SHADOW_CORE_DUMP............................................................................................................ 1-154
SHARED_MEMORY_ADDRESS................................................................................................ 1-155
SHARED_POOL_RESERVED_SIZE.......................................................................................... 1-155
SHARED_POOL_SIZE.................................................................................................................. 1-156
SHARED_SERVER_SESSIONS.................................................................................................. 1-157
SHARED_SERVERS...................................................................................................................... 1-157
SORT_AREA_RETAINED_SIZE................................................................................................. 1-157
SORT_AREA_SIZE......................................................................................................................... 1-159
SPFILE............................................................................................................................................... 1-160
SQL92_SECURITY......................................................................................................................... 1-161
SQL_TRACE.................................................................................................................................... 1-161
STANDBY_ARCHIVE_DEST...................................................................................................... 1-162
STANDBY_FILE_MANAGEMENT............................................................................................ 1-163
STANDBY_PRESERVES_NAMES.............................................................................................. 1-163
STAR_TRANSFORMATION_ENABLED................................................................................. 1-164
TAPE_ASYNCH_IO....................................................................................................................... 1-165
THREAD........................................................................................................................................... 1-165
TIMED_OS_STATISTICS............................................................................................................. 1-166
TIMED_STATISTICS..................................................................................................................... 1-167
TRACE_ENABLED......................................................................................................................... 1-168
TRACEFILE_IDENTIFIER............................................................................................................ 1-169
TRANSACTION_AUDITING..................................................................................................... 1-169
TRANSACTIONS........................................................................................................................... 1-170
TRANSACTIONS_PER_ROLLBACK_SEGMENT................................................................. 1-171
UNDO_MANAGEMENT............................................................................................................. 1-171
UNDO_RETENTION..................................................................................................................... 1-172
UNDO_SUPPRESS_ERRORS...................................................................................................... 1-173
UNDO_TABLESPACE................................................................................................................... 1-173
USE_INDIRECT_DATA_BUFFERS............................................................................................ 1-174
USER_DUMP_DEST...................................................................................................................... 1-174
ix
UTL_FILE_DIR................................................................................................................................ 1-175
WORKAREA_SIZE_POLICY....................................................................................................... 1-176
2 Static Data Dictionary Views
ALL_ALL_TABLES............................................................................................................................. 2-4
ALL_ARGUMENTS........................................................................................................................... 2-6
ALL_ASSOCIATIONS....................................................................................................................... 2-7
ALL_BASE_TABLE_MVIEWS......................................................................................................... 2-8
ALL_CATALOG.................................................................................................................................. 2-9
ALL_CLUSTER_HASH_EXPRESSIONS....................................................................................... 2-9
ALL_CLUSTERS............................................................................................................................... 2-10
ALL_COL_COMMENTS................................................................................................................. 2-11
ALL_COL_PRIVS............................................................................................................................. 2-11
ALL_COL_PRIVS_MADE............................................................................................................... 2-12
ALL_COL_PRIVS_RECD................................................................................................................ 2-13
ALL_COLL_TYPES........................................................................................................................... 2-13
ALL_CONS_COLUMNS................................................................................................................. 2-14
ALL_CONSTRAINTS...................................................................................................................... 2-15
ALL_CONTEXT................................................................................................................................. 2-16
ALL_DB_LINKS................................................................................................................................ 2-17
ALL_DEF_AUDIT_OPTS................................................................................................................ 2-17
ALL_DEPENDENCIES.................................................................................................................... 2-18
ALL_DIM_ATTRIBUTES................................................................................................................ 2-19
ALL_DIM_CHILD_OF..................................................................................................................... 2-19
ALL_DIM_HIERARCHIES............................................................................................................. 2-20
ALL_DIM_JOIN_KEY...................................................................................................................... 2-20
ALL_DIM_LEVEL_KEY................................................................................................................... 2-21
ALL_DIM_LEVELS.......................................................................................................................... 2-21
ALL_DIMENSIONS......................................................................................................................... 2-22
ALL_DIRECTORIES........................................................................................................................ 2-22
ALL_ERRORS.................................................................................................................................... 2-23
ALL_EXTERNAL_TABLES............................................................................................................. 2-23
ALL_HISTOGRAMS....................................................................................................................... 2-24
ALL_IND_COLUMNS..................................................................................................................... 2-24
ALL_IND_EXPRESSIONS.............................................................................................................. 2-25
x