p. 1

oracle® database reference 11g release 1 11.1 b28320-01 july 2007

[close]

p. 2

oracle database reference 11g release 1 11.1 b28320-01 copyright © 2002 2007 oracle all rights reserved primary author tony morales kathy rich contributing author contributors nipun agarwal rick anderson mark bauer neerja bhatt tudor bosman george candea wilson chan sumanta chatterjee eugene chong connie dialeris green harvey eneman bruce ernst ira greenberg steve harris thuvan hoang lilian hobbs namit jain hakkan jakobsson bob jenkins mark johnson jonathan klein sushil kumar tirthankar lahiri bill lee yunrui li juan loaiza rich long diana lorentz catherine luu neil macnaughton ari mozes gopal mulagund subramanian muralidhar ravi murthy karuna muthiah arvind nithrakashyap ronald obermarck kant patel christopher racicot mark ramacher shankar raman suvendu ray ann rhee vivian schupmann 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 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 except to the extent required to obtain interoperability with other independently created software or as specified by law is prohibited the information contained in this document is subject to change without notice if you find any problems in the documentation please report them to us in writing this document is not warranted to be 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 if the programs are delivered to the united states government or anyone licensing or using the programs on behalf of the united states government the following notice is applicable u.s government rights programs software databases and related documentation and technical data delivered to u.s government customers are commercial computer software or commercial technical data pursuant to the applicable federal acquisition regulation and agency-specific supplemental regulations as such use duplication disclosure modification and adaptation of the programs including documentation and technical data shall be subject to the licensing restrictions set forth in the applicable oracle license agreement and to the extent applicable the additional rights set forth in far 52.227-19 commercial computer software restricted rights june 1987 oracle usa inc 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 we disclaim liability for any damages caused by such use of the programs oracle jd edwards peoplesoft and siebel are registered trademarks of oracle corporation and/or its affiliates other names may be trademarks of their respective owners the programs may provide links to web sites and access to content products and services from third parties oracle is not responsible for the availability of or any content provided on third-party web sites you bear all risks associated with the use of such content if you choose to purchase any products or services from a third party the relationship is directly between you and the third party oracle is not responsible for a the quality of third-party products or services or b fulfilling any of the terms of the agreement with the third party including delivery of products or services and warranty obligations related to purchased products or services oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party.

[close]

p. 3

contents preface li audience li documentation accessibility li related documents lii conventions liii what s new in oracle database reference lv oracle database 11g release 1 11.1 new features lv part i 1 initialization parameters initialization parameters uses of initialization parameters 1-1 types of initialization parameters 1-2 derived parameters 1-2 operating system-dependent parameters 1-2 variable parameters 1-2 basic initialization parameters 1-3 parameter files 1-3 server parameter files 1-3 initialization parameter files 1-4 specifying values in an initialization parameter file 1-4 changing parameter values in a parameter file 1-6 parameters by functional category 1-6 modifiable parameters 1-12 displaying current parameter values 1-17 parameters you should not specify in the parameter file 1-17 when parameters are set incorrectly 1-17 reading the parameter descriptions 1-17 initialization parameter descriptions 1-18 active_instance_count 1-19 aq_tm_processes 1-19 archive_lag_target 1-20 asm_diskgroups 1-21 asm_diskstring 1-21 iii

[close]

p. 4

asm_power_limit asm_preferred_read_failure_groups audit_file_dest audit_sys_operations audit_syslog_level audit_trail background_core_dump background_dump_dest backup_tape_io_slaves bitmap_merge_area_size blank_trimming circuits client_result_cache_lag client_result_cache_size cluster_database cluster_database_instances cluster_interconnects commit_logging commit_point_strength commit_wait commit_write compatible control_file_record_keep_time control_files control_management_pack_access core_dump_dest cpu_count create_bitmap_area_size create_stored_outlines cursor_sharing cursor_space_for_time db_nk_cache_size db_block_buffers db_block_checking db_block_checksum db_block_size db_cache_advice db_cache_size db_create_file_dest db_create_online_log_dest_n db_domain db_file_multiblock_read_count db_file_name_convert db_files db_flashback_retention_target db_keep_cache_size db_lost_write_protect 1-22 1-22 1-23 1-23 1-24 1-24 1-25 1-26 1-26 1-27 1-27 1-28 1-28 1-29 1-29 1-30 1-30 1-31 1-31 1-32 1-32 1-33 1-33 1-34 1-34 1-35 1-36 1-36 1-37 1-37 1-38 1-39 1-39 1-40 1-41 1-42 1-42 1-43 1-44 1-44 1-45 1-45 1-46 1-47 1-48 1-48 1-49 iv

[close]

p. 5

db_name db_recovery_file_dest db_recovery_file_dest_size db_recycle_cache_size db_securefile db_ultra_safe db_unique_name db_writer_processes dbwr_io_slaves ddl_lock_timeout dg_broker_config_filen dg_broker_start diagnostic_dest disk_asynch_io dispatchers distributed_lock_timeout dml_locks event fal_client fal_server fast_start_mttr_target fast_start_parallel_rollback file_mapping fileio_network_adapters filesystemio_options fixed_date gc_files_to_locks gcs_server_processes global_context_pool_size global_names global_txn_processes hash_area_size hi_shared_memory_address hs_autoregister ifile instance_groups instance_name instance_number instance_type java_jit_enabled java_max_sessionspace_size java_pool_size java_soft_sessionspace_limit job_queue_processes large_pool_size ldap_directory_access ldap_directory_sysauth 1-49 1-50 1-51 1-51 1-52 1-52 1-53 1-54 1-54 1-55 1-55 1-55 1-56 1-57 1-57 1-60 1-61 1-61 1-62 1-62 1-62 1-63 1-63 1-64 1-64 1-64 1-65 1-66 1-66 1-67 1-67 1-68 1-68 1-69 1-69 1-70 1-70 1-71 1-72 1-72 1-72 1-73 1-73 1-74 1-75 1-75 1-76 v

[close]

p. 6

license_max_sessions license_max_users license_sessions_warning local_listener lock_name_space lock_sga log_archive_config log_archive_dest log_archive_dest_n log_archive_dest_state_n log_archive_duplex_dest log_archive_format log_archive_local_first log_archive_max_processes log_archive_min_succeed_dest log_archive_trace log_buffer log_checkpoint_interval log_checkpoint_timeout log_checkpoints_to_alert log_file_name_convert max_commit_propagation_delay max_dispatchers max_dump_file_size max_enabled_roles max_shared_servers memory_max_target memory_target nls_calendar nls_comp nls_currency nls_date_format nls_date_language nls_dual_currency nls_iso_currency nls_language nls_length_semantics nls_nchar_conv_excp nls_numeric_characters nls_sort nls_territory nls_timestamp_format nls_timestamp_tz_format o7_dictionary_accessibility object_cache_max_size_percent object_cache_optimal_size olap_page_pool_size 1-76 1-77 1-77 1-78 1-79 1-79 1-79 1-80 1-81 1-85 1-86 1-86 1-87 1-88 1-89 1-90 1-91 1-91 1-92 1-93 1-93 1-94 1-95 1-95 1-96 1-96 1-97 1-97 1-98 1-99 1-99 1-100 1-100 1-101 1-101 1-102 1-102 1-103 1-103 1-103 1-104 1-105 1-105 1-106 1-106 1-107 1-107 vi

[close]

p. 7

open_cursors open_links open_links_per_instance optimizer_capture_sql_plan_baselines optimizer_dynamic_sampling optimizer_features_enable optimizer_index_caching optimizer_index_cost_adj optimizer_mode optimizer_secure_view_merging optimizer_use_invisible_indexes optimizer_use_pending_statistics optimizer_use_sql_plan_baselines os_authent_prefix os_roles parallel_adaptive_multi_user parallel_automatic_tuning parallel_execution_message_size parallel_instance_group parallel_io_cap_enabled parallel_max_servers parallel_min_percent parallel_min_servers parallel_threads_per_cpu pga_aggregate_target plscope_settings plsql_ccflags plsql_code_type plsql_debug plsql_native_library_dir plsql_native_library_subdir_count plsql_optimize_level plsql_v2_compatibility plsql_warnings pre_page_sga processes query_rewrite_enabled query_rewrite_integrity rdbms_server_dn read_only_open_delayed recovery_parallelism recyclebin redo_transport_user remote_dependencies_mode remote_listener remote_login_passwordfile remote_os_authent 1-108 1-108 1-109 1-109 1-110 1-110 1-111 1-112 1-112 1-113 1-114 1-114 1-115 1-115 1-116 1-116 1-117 1-117 1-118 1-118 1-119 1-119 1-120 1-121 1-121 1-122 1-123 1-123 1-124 1-124 1-125 1-125 1-126 1-126 1-127 1-128 1-129 1-129 1-130 1-130 1-131 1-131 1-132 1-132 1-133 1-133 1-134 vii

[close]

p. 8

remote_os_roles replication_dependency_tracking resource_limit resource_manager_cpu_allocation resource_manager_plan result_cache_max_result result_cache_max_size result_cache_mode result_cache_remote_expiration resumable_timeout rollback_segments sec_case_sensitive_logon sec_max_failed_login_attempts sec_protocol_error_further_action sec_protocol_error_trace_action sec_return_server_release_banner serial_reuse service_names session_cached_cursors session_max_open_files sessions sga_max_size sga_target shadow_core_dump shared_memory_address shared_pool_reserved_size shared_pool_size shared_server_sessions shared_servers skip_unusable_indexes smtp_out_server sort_area_retained_size sort_area_size spfile sql_trace sql92_security sqltune_category standby_archive_dest standby_file_management star_transformation_enabled statistics_level streams_pool_size tape_asynch_io thread timed_os_statistics timed_statistics trace_enabled 1-134 1-135 1-135 1-136 1-136 1-137 1-137 1-138 1-138 1-138 1-139 1-140 1-140 1-141 1-141 1-142 1-142 1-143 1-144 1-144 1-145 1-146 1-146 1-147 1-147 1-148 1-148 1-149 1-150 1-150 1-151 1-151 1-152 1-153 1-153 1-154 1-154 1-155 1-155 1-156 1-157 1-158 1-158 1-159 1-159 1-160 1-160 viii

[close]

p. 9

tracefile_identifier transactions transactions_per_rollback_segment undo_management undo_retention undo_tablespace use_indirect_data_buffers user_dump_dest utl_file_dir workarea_size_policy xml_db_events 1-161 1-162 1-162 1-163 1-163 1-164 1-164 1-165 1-166 1-166 1-167 part ii 2 static data dictionary views static data dictionary views all_all_tables to all_outlines about static data dictionary views 2-1 oracle replication views 2-2 oracle workspace manager views 2-3 recovery catalog views 2-4 static data dictionary view descriptions 2-5 all_all_tables 2-5 all_apply 2-8 all_apply_conflict_columns 2-9 all_apply_dml_handlers 2-9 all_apply_enqueue 2-10 all_apply_error 2-10 all_apply_execute 2-11 all_apply_key_columns 2-11 all_apply_parameters 2-12 all_apply_progress 2-12 all_apply_table_columns 2-13 all_arguments 2-13 all_assemblies 2-15 all_associations 2-16 all_audit_policies 2-16 all_audit_policy_columns 2-17 all_aw_ps 2-18 all_aws 2-18 all_base_table_mviews 2-19 all_capture 2-19 all_capture_extra_attributes 2-21 all_capture_parameters 2-21 all_capture_prepared_database 2-22 all_capture_prepared_schemas 2-22 all_capture_prepared_tables 2-23 all_catalog 2-24 ix

[close]

p. 10

all_change_propagation_sets all_change_propagations all_change_sets all_change_sources all_change_tables all_cluster_hash_expressions all_clusters all_col_comments all_col_pending_stats all_col_privs all_col_privs_made all_col_privs_recd all_coll_types all_cons_columns all_cons_obj_columns all_constraints all_context all_cube_attr_visibility all_cube_attributes all_cube_build_processes all_cube_calculated_members all_cube_dim_levels all_cube_dim_models all_cube_dim_view_columns all_cube_dim_views all_cube_dimensionality all_cube_dimensions all_cube_hier_levels all_cube_hier_view_columns all_cube_hier_views all_cube_hierarchies all_cube_measures all_cube_view_columns all_cube_views all_cubes all_db_links all_def_audit_opts all_dependencies all_dequeue_queues all_dim_attributes all_dim_child_of all_dim_hierarchies all_dim_join_key all_dim_level_key all_dim_levels all_dimensions all_directories 2-24 2-25 2-25 2-26 2-26 2-27 2-27 2-28 2-29 2-29 2-30 2-30 2-31 2-31 2-32 2-32 2-34 2-34 2-34 2-35 2-36 2-36 2-37 2-37 2-38 2-38 2-39 2-39 2-40 2-41 2-41 2-42 2-43 2-43 2-44 2-44 2-45 2-46 2-46 2-47 2-47 2-48 2-48 2-49 2-49 2-49 2-50 x

[close]

p. 11

all_encrypted_columns all_errors all_evaluation_context_tables all_evaluation_context_vars all_evaluation_contexts all_external_locations all_external_tables all_file_group_export_info all_file_group_files all_file_group_tables all_file_group_tablespaces all_file_group_versions all_file_groups all_identifiers all_ind_columns all_ind_expressions all_ind_partitions all_ind_pending_stats all_ind_statistics all_ind_subpartitions all_indexes all_indextype_arraytypes all_indextype_comments all_indextype_operators all_indextypes all_internal_triggers all_java_arguments all_java_classes all_java_compiler_options all_java_derivations all_java_fields all_java_implements all_java_inners all_java_layouts all_java_methods all_java_ncomps all_java_resolvers all_java_throws all_jobs all_join_ind_columns all_libraries all_lob_partitions all_lob_subpartitions all_lob_templates all_lobs all_log_group_columns all_log_groups 2-50 2-51 2-51 2-52 2-52 2-53 2-53 2-54 2-55 2-55 2-56 2-56 2-57 2-57 2-58 2-59 2-59 2-60 2-61 2-62 2-64 2-66 2-67 2-67 2-68 2-69 2-69 2-70 2-70 2-71 2-71 2-72 2-73 2-74 2-74 2-75 2-76 2-76 2-77 2-77 2-77 2-78 2-79 2-80 2-81 2-81 2-82 xi

[close]

p. 12

all_measure_folder_contents all_measure_folders all_method_params all_method_results all_mining_model_attributes all_mining_model_settings all_mining_models all_mview_aggregates all_mview_analysis all_mview_comments all_mview_detail_partition all_mview_detail_relations all_mview_detail_subpartition all_mview_joins all_mview_keys all_mview_logs all_mview_refresh_times all_mviews all_nested_table_cols all_nested_tables all_obj_colattrs all_object_tables all_objects all_opancillary all_oparguments all_opbindings all_operator_comments all_operators all_outline_hints all_outlines 2-82 2-83 2-83 2-84 2-85 2-86 2-86 2-87 2-88 2-89 2-90 2-90 2-91 2-91 2-92 2-92 2-93 2-94 2-97 2-99 2-99 2-100 2-102 2-103 2-103 2-103 2-104 2-105 2-105 2-105 3 static data dictionary views all_part_col_statistics to database properties all_part_col_statistics 3-1 all_part_histograms 3-2 all_part_indexes 3-2 all_part_key_columns 3-4 all_part_lobs 3-5 all_part_tables 3-7 all_partial_drop_tabs 3-9 all_pending_conv_tables 3-9 all_plsql_object_settings 3-10 all_policies 3-10 all_policy_contexts 3-12 all_policy_groups 3-12 all_procedures 3-12 all_propagation 3-13 xii

[close]

p. 13

all_published_columns all_queue_subscribers all_queue_tables all_queues all_refresh all_refresh_children all_refresh_dependencies all_refs all_registered_mviews all_registry_banners all_rewrite_equivalences all_rule_set_rules all_rule_sets all_rules all_scheduler_chain_rules all_scheduler_chain_steps all_scheduler_chains all_scheduler_credentials all_scheduler_global_attribute all_scheduler_job_args all_scheduler_job_classes all_scheduler_job_log all_scheduler_job_run_details all_scheduler_jobs all_scheduler_program_args all_scheduler_programs all_scheduler_remote_databases all_scheduler_remote_jobstate all_scheduler_running_chains all_scheduler_running_jobs all_scheduler_schedules all_scheduler_window_details all_scheduler_window_groups all_scheduler_window_log all_scheduler_windows all_scheduler_wingroup_members all_sec_relevant_cols all_secondary_objects all_sequences all_services all_source all_source_tables all_sqlj_type_attrs all_sqlj_type_methods all_sqlj_types all_sqlset all_sqlset_binds 3-14 3-14 3-15 3-16 3-16 3-17 3-18 3-18 3-19 3-19 3-19 3-20 3-21 3-21 3-22 3-22 3-23 3-24 3-24 3-25 3-25 3-26 3-27 3-28 3-31 3-32 3-33 3-33 3-34 3-35 3-36 3-37 3-37 3-38 3-38 3-39 3-40 3-40 3-41 3-41 3-42 3-42 3-42 3-43 3-44 3-45 3-45 xiii

[close]

p. 14

all_sqlset_plans all_sqlset_references all_sqlset_statements all_stat_extensions all_stored_settings all_streams_columns all_streams_global_rules all_streams_message_consumers all_streams_message_rules all_streams_newly_supported all_streams_rules all_streams_schema_rules all_streams_table_rules all_streams_transform_function all_streams_unsupported all_subpart_col_statistics all_subpart_histograms all_subpart_key_columns all_subpartition_templates all_subscribed_columns all_subscribed_tables all_subscriptions all_sumdelta all_sync_capture all_sync_capture_prepared_tabs all_sync_capture_tables all_synonyms all_tab_col_statistics all_tab_cols all_tab_columns all_tab_comments all_tab_histgrm_pending_stats all_tab_histograms all_tab_modifications all_tab_partitions all_tab_pending_stats all_tab_privs all_tab_privs_made all_tab_privs_recd all_tab_stat_prefs all_tab_statistics all_tab_stats_history all_tab_subpartitions all_tables all_trigger_cols all_trigger_ordering all_triggers 3-46 3-49 3-49 3-51 3-51 3-52 3-52 3-53 3-54 3-54 3-55 3-56 3-57 3-58 3-59 3-60 3-61 3-61 3-62 3-62 3-63 3-63 3-63 3-63 3-63 3-64 3-64 3-65 3-66 3-68 3-69 3-70 3-70 3-71 3-72 3-73 3-73 3-74 3-74 3-75 3-75 3-76 3-77 3-78 3-80 3-81 3-82 xiv

[close]

p. 15

all_type_attrs all_type_methods all_type_versions all_types all_unused_col_tabs all_updatable_columns all_users all_ustats all_varrays all_views all_warning_settings all_xml_indexes all_xml_schemas all_xml_tab_cols all_xml_tables all_xml_view_cols all_xml_views audit_actions cat catalog chained_rows change_propagation_sets change_propagations change_sets change_sources change_tables client_result_cache_stats clu col cols database_properties 3-83 3-84 3-84 3-85 3-86 3-86 3-87 3-87 3-88 3-88 3-89 3-90 3-91 3-91 3-92 3-93 3-93 3-94 3-94 3-94 3-94 3-95 3-95 3-95 3-95 3-95 3-95 3-96 3-96 3-96 3-96 4 static data dictionary views dba_2pc_neighbors to dba_mviews dba_2pc_neighbors 4-1 dba_2pc_pending 4-1 dba_addm_fdg_breakdown 4-2 dba_addm_findings 4-2 dba_addm_instances 4-3 dba_addm_tasks 4-4 dba_advisor_actions 4-6 dba_advisor_commands 4-7 dba_advisor_def_parameters 4-7 dba_advisor_definitions 4-8 dba_advisor_dir_definitions 4-8 dba_advisor_dir_instances 4-9 dba_advisor_dir_task_inst 4-9 dba_advisor_executions 4-10 xv

[close]