V$PARAMETER displays information about the initialization parameters
that are currently in effect for the session. A new session inherits
parameter values from the instance-wide values displayed by the
V$SYSTEM_PARAMETER view.
V$PARAMETER2 displays information about the initialization parameters that are currently in effect for the session, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values displayed in the V$SYSTEM_PARAMETER2 view.
Presenting the list parameter values in this format enables you to quickly determine the values for a list parameter. For example, if a parameter value is a, b, then the V$PARAMETER view does not tell you if the parameter has two values (both a and b) or one value (a, b). V$PARAMETER2 makes the distinction between the list parameter values clear.
SQL> select count(*) from X$KSPPI;
COUNT(*)
----------
2394
SQL> select count(*) from v$parameter;
COUNT(*)
----------
341
SQL>
SQL> select a.ksppinm name, b.ksppstvl value,b.ksppstdf deflt,
decode (a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type, a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where a.indx = b.indx
and
a.ksppinm like '\_%' escape '\'
order by name
We can also check the hidden and non-hidden parameter from memory by creating pfile .The command for creating the pfile is
SQL> Create pfile='C:\pfile.ora' from memory;
V$PARAMETER2 displays information about the initialization parameters that are currently in effect for the session, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values displayed in the V$SYSTEM_PARAMETER2 view.
Presenting the list parameter values in this format enables you to quickly determine the values for a list parameter. For example, if a parameter value is a, b, then the V$PARAMETER view does not tell you if the parameter has two values (both a and b) or one value (a, b). V$PARAMETER2 makes the distinction between the list parameter values clear.
SQL> select count(*) from X$KSPPI;
COUNT(*)
----------
2394
SQL> select count(*) from v$parameter;
COUNT(*)
----------
341
SQL>
SQL> select a.ksppinm name, b.ksppstvl value,b.ksppstdf deflt,
decode (a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type, a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where a.indx = b.indx
and
a.ksppinm like '\_%' escape '\'
order by name
We can also check the hidden and non-hidden parameter from memory by creating pfile .The command for creating the pfile is
SQL> Create pfile='C:\pfile.ora' from memory;
SQL> select name from v$parameter where isdeprecated!='TRUE' order by name; NAME ---------------------------------------- O7_DICTIONARY_ACCESSIBILITY aq_tm_processes archive_lag_target asm_diskgroups asm_diskstring asm_power_limit asm_preferred_read_failure_groups audit_file_dest audit_sys_operations audit_syslog_level audit_trail background_core_dump backup_tape_io_slaves bitmap_merge_area_size blank_trimming cell_offload_compaction cell_offload_decryption cell_offload_parameters cell_offload_plan_display cell_offload_processing cell_partition_large_extents circuits client_result_cache_lag client_result_cache_size cluster_database cluster_database_instances cluster_interconnects commit_logging commit_point_strength commit_wait 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 db_16k_cache_size db_2k_cache_size db_32k_cache_size db_4k_cache_size db_8k_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_1 db_create_online_log_dest_2 db_create_online_log_dest_3 db_create_online_log_dest_4 db_create_online_log_dest_5 db_domain db_file_multiblock_read_count db_file_name_convert db_files db_flash_cache_file db_flash_cache_size db_flashback_retention_target db_keep_cache_size db_lost_write_protect 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 deferred_segment_creation dg_broker_config_file1 dg_broker_config_file2 dg_broker_start diagnostic_dest disk_asynch_io dispatchers distributed_lock_timeout dml_locks dst_upgrade_insert_conv enable_ddl_logging event fal_client fal_server fast_start_mttr_target fast_start_parallel_rollback file_mapping fileio_network_adapters filesystemio_options fixed_date gcs_server_processes global_names global_txn_processes hash_area_size hi_shared_memory_address hs_autoregister ifile 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 license_max_sessions license_max_users license_sessions_warning listener_networks local_listener lock_sga log_archive_config log_archive_dest log_archive_dest_1 log_archive_dest_10 log_archive_dest_11 log_archive_dest_12 log_archive_dest_13 log_archive_dest_14 log_archive_dest_15 log_archive_dest_16 log_archive_dest_17 log_archive_dest_18 log_archive_dest_19 log_archive_dest_2 log_archive_dest_20 log_archive_dest_21 log_archive_dest_22 log_archive_dest_23 log_archive_dest_24 log_archive_dest_25 log_archive_dest_26 log_archive_dest_27 log_archive_dest_28 log_archive_dest_29 log_archive_dest_3 log_archive_dest_30 log_archive_dest_31 log_archive_dest_4 log_archive_dest_5 log_archive_dest_6 log_archive_dest_7 log_archive_dest_8 log_archive_dest_9 log_archive_dest_state_1 log_archive_dest_state_10 log_archive_dest_state_11 log_archive_dest_state_12 log_archive_dest_state_13 log_archive_dest_state_14 log_archive_dest_state_15 log_archive_dest_state_16 log_archive_dest_state_17 log_archive_dest_state_18 log_archive_dest_state_19 log_archive_dest_state_2 log_archive_dest_state_20 log_archive_dest_state_21 log_archive_dest_state_22 log_archive_dest_state_23 log_archive_dest_state_24 log_archive_dest_state_25 log_archive_dest_state_26 log_archive_dest_state_27 log_archive_dest_state_28 log_archive_dest_state_29 log_archive_dest_state_3 log_archive_dest_state_30 log_archive_dest_state_31 log_archive_dest_state_4 log_archive_dest_state_5 log_archive_dest_state_6 log_archive_dest_state_7 log_archive_dest_state_8 log_archive_dest_state_9 log_archive_duplex_dest log_archive_format 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_dispatchers max_dump_file_size 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_time_format nls_time_tz_format nls_timestamp_format nls_timestamp_tz_format object_cache_max_size_percent object_cache_optimal_size olap_page_pool_size 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_degree_limit parallel_degree_policy parallel_execution_message_size parallel_force_local parallel_instance_group parallel_max_servers parallel_min_percent parallel_min_servers parallel_min_time_threshold parallel_servers_target parallel_threads_per_cpu permit_92_wrap_format pga_aggregate_target plscope_settings plsql_ccflags plsql_code_type plsql_optimize_level 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_roles replication_dependency_tracking resource_limit 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 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 sql92_security sqltune_category standby_file_management star_transformation_enabled statistics_level streams_pool_size tape_asynch_io thread timed_os_statistics timed_statistics trace_enabled tracefile_identifier transactions transactions_per_rollback_segment undo_management undo_retention undo_tablespace use_indirect_data_buffers utl_file_dir workarea_size_policy xml_db_events 317 rows selected. SQL> spool off | SQL> select name from v$parameter2 where isdeprecated!='TRUE' order by name; NAME ---------------------------------------- O7_DICTIONARY_ACCESSIBILITY aq_tm_processes archive_lag_target asm_diskgroups asm_diskstring asm_power_limit asm_preferred_read_failure_groups audit_file_dest audit_sys_operations audit_syslog_level audit_trail background_core_dump backup_tape_io_slaves bitmap_merge_area_size blank_trimming cell_offload_compaction cell_offload_decryption cell_offload_parameters cell_offload_plan_display cell_offload_processing cell_partition_large_extents circuits client_result_cache_lag client_result_cache_size cluster_database cluster_database_instances cluster_interconnects commit_logging commit_point_strength commit_wait 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 db_16k_cache_size db_2k_cache_size db_32k_cache_size db_4k_cache_size db_8k_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_1 db_create_online_log_dest_2 db_create_online_log_dest_3 db_create_online_log_dest_4 db_create_online_log_dest_5 db_domain db_file_multiblock_read_count db_file_name_convert db_files db_flash_cache_file db_flash_cache_size db_flashback_retention_target db_keep_cache_size db_lost_write_protect 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 deferred_segment_creation dg_broker_config_file1 dg_broker_config_file2 dg_broker_start diagnostic_dest disk_asynch_io dispatchers distributed_lock_timeout dml_locks dst_upgrade_insert_conv enable_ddl_logging event fal_client fal_server fast_start_mttr_target fast_start_parallel_rollback file_mapping fileio_network_adapters filesystemio_options fixed_date gcs_server_processes global_names global_txn_processes hash_area_size hi_shared_memory_address hs_autoregister ifile 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 license_max_sessions license_max_users license_sessions_warning listener_networks local_listener lock_sga log_archive_config log_archive_dest log_archive_dest_1 log_archive_dest_10 log_archive_dest_11 log_archive_dest_12 log_archive_dest_13 log_archive_dest_14 log_archive_dest_15 log_archive_dest_16 log_archive_dest_17 log_archive_dest_18 log_archive_dest_19 log_archive_dest_2 log_archive_dest_20 log_archive_dest_21 log_archive_dest_22 log_archive_dest_23 log_archive_dest_24 log_archive_dest_25 log_archive_dest_26 log_archive_dest_27 log_archive_dest_28 log_archive_dest_29 log_archive_dest_3 log_archive_dest_30 log_archive_dest_31 log_archive_dest_4 log_archive_dest_5 log_archive_dest_6 log_archive_dest_7 log_archive_dest_8 log_archive_dest_9 log_archive_dest_state_1 log_archive_dest_state_10 log_archive_dest_state_11 log_archive_dest_state_12 log_archive_dest_state_13 log_archive_dest_state_14 log_archive_dest_state_15 log_archive_dest_state_16 log_archive_dest_state_17 log_archive_dest_state_18 log_archive_dest_state_19 log_archive_dest_state_2 log_archive_dest_state_20 log_archive_dest_state_21 log_archive_dest_state_22 log_archive_dest_state_23 log_archive_dest_state_24 log_archive_dest_state_25 log_archive_dest_state_26 log_archive_dest_state_27 log_archive_dest_state_28 log_archive_dest_state_29 log_archive_dest_state_3 log_archive_dest_state_30 log_archive_dest_state_31 log_archive_dest_state_4 log_archive_dest_state_5 log_archive_dest_state_6 log_archive_dest_state_7 log_archive_dest_state_8 log_archive_dest_state_9 log_archive_duplex_dest log_archive_format 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_dispatchers max_dump_file_size 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_time_format nls_time_tz_format nls_timestamp_format nls_timestamp_tz_format object_cache_max_size_percent object_cache_optimal_size olap_page_pool_size 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_degree_limit parallel_degree_policy parallel_execution_message_size parallel_force_local parallel_instance_group parallel_max_servers parallel_min_percent parallel_min_servers parallel_min_time_threshold parallel_servers_target parallel_threads_per_cpu permit_92_wrap_format pga_aggregate_target plscope_settings plsql_ccflags plsql_code_type plsql_optimize_level 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_roles replication_dependency_tracking resource_limit 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 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 sql92_security sqltune_category standby_file_management star_transformation_enabled statistics_level | Hidden Parameters in 11G ============================== # Oracle init.ora parameter file generated by instance testdb1 on 05/20/2012 09:52:05 __db_cache_size=3104M __java_pool_size=16M __large_pool_size=16M __oracle_base='C:\app\snojha' # ORACLE_BASE set from environment __pga_aggregate_target=2624M __sga_target=3904M __shared_io_pool_size=0 __shared_pool_size=720M __streams_pool_size=0 _aggregation_optimization_settings=0 _always_anti_join='CHOOSE' _always_semi_join='CHOOSE' _and_pruning_enabled=TRUE _b_tree_bitmap_plans=TRUE _bloom_filter_enabled=TRUE _bloom_folding_enabled=TRUE _bloom_pruning_enabled=TRUE _complex_view_merging=TRUE _compression_compatibility='11.2.0.0.0' _connect_by_use_union_all='TRUE' _convert_set_to_join=FALSE _cost_equality_semi_join=TRUE _cpu_to_io=0 _dimension_skip_null=TRUE _eliminate_common_subexpr=TRUE _enable_type_dep_selectivity=TRUE _fast_full_scan_enabled=TRUE _first_k_rows_dynamic_proration=TRUE _gby_hash_aggregation_enabled=TRUE _generalized_pruning_enabled=TRUE _globalindex_pnum_filter_enabled=TRUE _gs_anti_semi_join_allowed=TRUE _improved_outerjoin_card=TRUE _improved_row_length_enabled=TRUE _index_join_enabled=TRUE _ksb_restart_policy_times='0' _ksb_restart_policy_times='60' _ksb_restart_policy_times='120' _ksb_restart_policy_times='240' # internal update to set default _left_nested_loops_random=TRUE _local_communication_costing_enabled=TRUE _minimal_stats_aggregation=TRUE _mmv_query_rewrite_enabled=TRUE _new_initial_join_orders=TRUE _new_sort_cost_estimate=TRUE _nlj_batching_enabled=1 _optim_adjust_for_part_skews=TRUE _optim_enhance_nnull_detection=TRUE _optim_new_default_join_sel=TRUE _optim_peek_user_binds=TRUE _optimizer_adaptive_cursor_sharing=TRUE _optimizer_better_inlist_costing='ALL' _optimizer_cbqt_no_size_restriction=TRUE _optimizer_coalesce_subqueries=TRUE _optimizer_complex_pred_selectivity=TRUE _optimizer_compute_index_stats=TRUE _optimizer_connect_by_combine_sw=TRUE _optimizer_connect_by_cost_based=TRUE _optimizer_connect_by_elim_dups=TRUE _optimizer_correct_sq_selectivity=TRUE _optimizer_cost_based_transformation='LINEAR' _optimizer_cost_hjsmj_multimatch=TRUE _optimizer_cost_model='CHOOSE' _optimizer_dim_subq_join_sel=TRUE _optimizer_distinct_agg_transform=TRUE _optimizer_distinct_elimination=TRUE _optimizer_distinct_placement=TRUE _optimizer_eliminate_filtering_join=TRUE _optimizer_enable_density_improvements=TRUE _optimizer_enable_extended_stats=TRUE _optimizer_enhanced_filter_push=TRUE _optimizer_extend_jppd_view_types=TRUE _optimizer_extended_cursor_sharing='UDO' _optimizer_extended_cursor_sharing_rel='SIMPLE' _optimizer_extended_stats_usage_control=224 _optimizer_fast_access_pred_analysis=TRUE _optimizer_fast_pred_transitivity=TRUE _optimizer_filter_pred_pullup=TRUE _optimizer_fkr_index_cost_bias=10 _optimizer_group_by_placement=TRUE _optimizer_improve_selectivity=TRUE _optimizer_join_elimination_enabled=TRUE _optimizer_join_factorization=TRUE _optimizer_join_order_control=3 _optimizer_join_sel_sanity_check=TRUE _optimizer_max_permutations=2000 _optimizer_mode_force=TRUE _optimizer_multi_level_push_pred=TRUE _optimizer_native_full_outer_join='FORCE' _optimizer_new_join_card_computation=TRUE _optimizer_null_aware_antijoin=TRUE _optimizer_or_expansion='DEPTH' _optimizer_order_by_elimination_enabled=TRUE _optimizer_outer_to_anti_enabled=TRUE _optimizer_push_down_distinct=0 _optimizer_push_pred_cost_based=TRUE _optimizer_rownum_bind_default=10 _optimizer_rownum_pred_based_fkr=TRUE _optimizer_skip_scan_enabled=TRUE _optimizer_sortmerge_join_inequality=TRUE _optimizer_squ_bottomup=TRUE _optimizer_star_tran_in_with_clause=TRUE _optimizer_system_stats_usage=TRUE _optimizer_table_expansion=TRUE _optimizer_transitivity_retain=TRUE _optimizer_try_st_before_jppd=TRUE _optimizer_undo_cost_change='11.2.0.1' _optimizer_unnest_corr_set_subq=TRUE _optimizer_unnest_disjunctive_subq=TRUE _optimizer_use_cbqt_star_transformation=TRUE _optimizer_use_feedback=TRUE _or_expand_nvl_predicate=TRUE _ordered_nested_loop=TRUE _parallel_broadcast_enabled=TRUE _partition_view_enabled=TRUE _pga_max_size=537380K _pivot_implementation_method='CHOOSE' _pre_rewrite_push_pred=TRUE _pred_move_around=TRUE _push_join_predicate=TRUE _push_join_union_view=TRUE _push_join_union_view2=TRUE _px_minus_intersect=TRUE _px_pwg_enabled=TRUE _px_ual_serial_input=TRUE _query_rewrite_setopgrw_enable=TRUE _remove_aggr_subquery=TRUE _replace_virtual_columns=TRUE _right_outer_hash_enable=TRUE _selfjoin_mv_duplicates=TRUE _smm_max_size=268690 _smm_min_size=1024 _smm_px_max_size=1343488 _sql_model_unfold_forloops='RUN_TIME' _sqltune_category_parsed='DEFAULT' # parsed sqltune_category _subquery_pruning_enabled=TRUE _subquery_pruning_mv_enabled=FALSE _table_scan_cost_plus_one=TRUE _union_rewrite_for_gs='YES_GSET_MVS' _unnest_subquery=TRUE _use_column_stats_for_function=TRUE |