西部数码网站管理助手 伪静态,电脑公司网站建设模板,深圳中高端网站建设怎么样,软件介绍网站模板查询初始化参数的方法很多#xff0c;比如SHOW PARAMETER#xff0c;或查询V$PARAMETER等#xff0c;这里简单总结一下。这一篇描述CREATE PFILE的方法检查初始化参数。前面介绍了很多种方法#xff0c;这些方法都是在数据库中查询初始化参数的设置。其实还有一种方法更加的… 查询初始化参数的方法很多比如SHOW PARAMETER或查询V$PARAMETER等这里简单总结一下。这一篇描述CREATE PFILE的方法检查初始化参数。前面介绍了很多种方法这些方法都是在数据库中查询初始化参数的设置。其实还有一种方法更加的一目了然就是CREATE PFILE的方式SQL show parameter spfileNAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string DATA/test/spfiletest.oraSQL create pfile /export/home/oracle/inittest1.ora from spfile;文件已创建。SQL host more /export/home/oracle/inittest1.oratest2.__db_cache_size541065216test1.__db_cache_size524288000test2.__java_pool_size4194304test1.__java_pool_size4194304test2.__large_pool_size4194304test1.__large_pool_size4194304test1.__oracle_base/data/oracle#ORACLE_BASE set from environmenttest2.__oracle_base/data/oracle#ORACLE_BASE set from environmenttest2.__pga_aggregate_target260046848test1.__pga_aggregate_target260046848test2.__sga_target775946240test1.__sga_target775946240test2.__shared_io_pool_size0test1.__shared_io_pool_size0test2.__shared_pool_size218103808test1.__shared_pool_size234881024test2.__streams_pool_size0test1.__streams_pool_size0*.audit_file_dest/data/oracle/admin/test/adump*.audit_traildb*.cluster_databasetrue*.cluster_database_instances3*.compatible11.1.0.0.0*.control_filesDATA/test/controlfile/current.529.684067899*.db_block_size8192*.db_create_file_destDATA*.db_domain*.db_nametest*.diagnostic_dest/data/oracletest1.instance_number1test2.instance_number2*.log_archive_config*.log_archive_dest_1LOCATION/data/oracle/oradata/test/archivelog*.open_cursors300test1.open_cursors500test2.open_cursors700*.pga_aggregate_target256901120*.processes150*.remote_login_passwordfileEXCLUSIVEtest1.remote_login_passwordfileSHARED*.sga_target772800512test1.thread1test2.thread2*.undo_tablespaceUNDOTBS1test2.undo_tablespaceUNDOTBS2使用了这个语句所有的SPFILE中设置的初始化参数设置都一目了然。除了CREATE PFILE FROM SPFILE外11g还增加了CREATE PFILE FROM MEMORY选项使得用户可以直接从数据库当前生效的参数来生成PFILE文件利用这个方法就可以解决上一篇文章最后介绍的PFILE中设置的实例级参数覆盖SPFILE中数据库级参数的情况SQL create pfile /export/home/oracle/inittest1.ora from memory;文件已创建。SQL host more /export/home/oracle/inittest1.ora# Oracle init.ora parameter file generated by instance test1 on 06/12/2009 15:18:46test1.__db_cache_size500Mtest2.__db_cache_size516M*.__java_pool_size4M*.__large_pool_size4M*.__oracle_base/data/oracle # ORACLE_BASE set from environment*.__pga_aggregate_target248M*.__sga_target740M*.__shared_io_pool_size0test1.__shared_pool_size224Mtest2.__shared_pool_size208M*.__streams_pool_size0*._always_anti_joinCHOOSE*._always_semi_joinCHOOSE*._b_tree_bitmap_plansTRUE*._bloom_filter_enabledTRUE*._bloom_pruning_enabledTRUE*._complex_view_mergingTRUE*._convert_set_to_joinFALSE*._cost_equality_semi_joinTRUE*._cpu_to_io0*._dimension_skip_nullTRUE*._eliminate_common_subexprTRUE*._enable_type_dep_selectivityTRUE*._fast_full_scan_enabledTRUE*._first_k_rows_dynamic_prorationTRUE*._gby_hash_aggregation_enabledTRUE*._generalized_pruning_enabledTRUE*._globalindex_pnum_filter_enabledTRUE*._gs_anti_semi_join_allowedTRUE*._improved_outerjoin_cardTRUE*._improved_row_length_enabledTRUE*._index_join_enabledTRUE*._ksb_restart_policy_times0*._ksb_restart_policy_times60*._ksb_restart_policy_times120*._ksb_restart_policy_times240 # internal update to set default*._left_nested_loops_randomTRUE*._local_communication_costing_enabledTRUE*._minimal_stats_aggregationTRUE*._mmv_query_rewrite_enabledTRUE*._new_initial_join_ordersTRUE*._new_sort_cost_estimateTRUE*._nlj_batching_enabled1*._optim_adjust_for_part_skewsTRUE*._optim_enhance_nnull_detectionTRUE*._optim_new_default_join_selTRUE*._optim_peek_user_bindsTRUE*._optimizer_adaptive_cursor_sharingTRUE*._optimizer_better_inlist_costingALL*._optimizer_cbqt_no_size_restrictionTRUE*._optimizer_complex_pred_selectivityTRUE*._optimizer_compute_index_statsTRUE*._optimizer_connect_by_combine_swTRUE*._optimizer_connect_by_cost_basedTRUE*._optimizer_correct_sq_selectivityTRUE*._optimizer_cost_based_transformationLINEAR*._optimizer_cost_hjsmj_multimatchTRUE*._optimizer_cost_modelCHOOSE*._optimizer_dim_subq_join_selTRUE*._optimizer_distinct_eliminationTRUE*._optimizer_enable_density_improvementsTRUE*._optimizer_enable_extended_statsTRUE*._optimizer_enhanced_filter_pushTRUE*._optimizer_extend_jppd_view_typesTRUE*._optimizer_extended_cursor_sharingUDO*._optimizer_extended_cursor_sharing_relSIMPLE*._optimizer_extended_stats_usage_control240*._optimizer_filter_pred_pullupTRUE*._optimizer_fkr_index_cost_bias10*._optimizer_group_by_placementTRUE*._optimizer_improve_selectivityTRUE*._optimizer_join_elimination_enabledTRUE*._optimizer_join_order_control3*._optimizer_join_sel_sanity_checkTRUE*._optimizer_max_permutations2000*._optimizer_mode_forceTRUE*._optimizer_multi_level_push_predTRUE*._optimizer_native_full_outer_joinFORCE*._optimizer_new_join_card_computationTRUE*._optimizer_null_aware_antijoinTRUE*._optimizer_or_expansionDEPTH*._optimizer_order_by_elimination_enabledTRUE*._optimizer_outer_to_anti_enabledTRUE*._optimizer_push_down_distinct0*._optimizer_push_pred_cost_basedTRUE*._optimizer_rownum_bind_default10*._optimizer_rownum_pred_based_fkrTRUE*._optimizer_skip_scan_enabledTRUE*._optimizer_sortmerge_join_inequalityTRUE*._optimizer_squ_bottomupTRUE*._optimizer_star_tran_in_with_clauseTRUE*._optimizer_system_stats_usageTRUE*._optimizer_transitivity_retainTRUE*._optimizer_undo_cost_change11.1.0.6*._or_expand_nvl_predicateTRUE*._ordered_nested_loopTRUE*._parallel_broadcast_enabledTRUE*._partition_view_enabledTRUE*._pivot_implementation_methodCHOOSE*._pre_rewrite_push_predTRUE*._pred_move_aroundTRUE*._push_join_predicateTRUE*._push_join_union_viewTRUE*._push_join_union_view2TRUE*._px_minus_intersectTRUE*._px_pwg_enabledTRUE*._px_ual_serial_inputTRUE*._query_rewrite_setopgrw_enableTRUE*._remove_aggr_subqueryTRUE*._right_outer_hash_enableTRUE*._selfjoin_mv_duplicatesTRUE*._sql_model_unfold_forloopsRUN_TIME*._sqltune_category_parsedDEFAULT # parsed sqltune_category*._subquery_pruning_enabledTRUE*._subquery_pruning_mv_enabledFALSE*._table_scan_cost_plus_oneTRUE*._union_rewrite_for_gsYES_GSET_MVS*._unnest_subqueryTRUE*._use_column_stats_for_functionTRUE*.audit_file_dest/data/oracle/admin/test/adump*.audit_trailDB*.cluster_databaseTRUE*.cluster_database_instances3*.compatible11.1.0.0.0*.control_filesDATA/test/controlfile/current.529.684067899test1.core_dump_dest/data/oracle/diag/rdbms/test/test1/cdumptest2.core_dump_dest/data/oracle/diag/rdbms/test/test2/cdump*.db_block_size8192*.db_create_file_destDATA*.db_domain*.db_nametest*.diagnostic_dest/data/oracletest1.instance_number1test2.instance_number2*.log_archive_config*.log_archive_dest_1LOCATION/data/oracle/oradata/test/archivelog*.log_buffer4197376 # log buffer update*.open_cursors500*.optimizer_dynamic_sampling2*.optimizer_modeALL_ROWS*.pga_aggregate_target245M*.plsql_warningsDISABLE:ALL # PL/SQL warnings at init.ora*.processes150*.query_rewrite_enabledTRUEtest1.remote_login_passwordfileSHAREDtest2.remote_login_passwordfileEXCLUSIVE*.resource_manager_plan*.result_cache_max_size3808K*.sga_target740M*.skip_unusable_indexesTRUEtest1.thread1test2.thread2test1.undo_tablespaceUNDOTBS1test2.undo_tablespaceUNDOTBS2但是这种方法显然也存在问题首先从得到的结果看里面除了包含用户设置的初始化参数外还包含了大量的隐含参数。如果这些隐患参数是Oracle用于自动调整的双下划线参数也不奇怪问题是大部分都是Oracle不推荐设置的单下划线隐含参数。不过这倒是一个查看Oracle隐患参数的好办法。另外一个问题是这个方法只对当前实例设置的参数有效而无法合并多个实例的设置对比上面的OPEN_CURSORS参数的设置和下面查询的结果就可以发现这个问题SQL select sid, name, value 2 from v$spparameter 3 where name open_cursors;SID NAME VALUE---------- ------------------------------ --------------------------------------------------* open_cursors 300test1 open_cursors 500test2 open_cursors 700SQL select inst_id, name, value 2 from gv$system_parameter 3 where name open_cursors; INST_ID NAME VALUE---------- ------------------------------ -------------------------------------------------- 1 open_cursors 500 2 open_cursors 500显然CREATE PFILE获取的结果和SPFILE中的设置并不相符下面修改一下初始化参数OPEN_CURSORS的值SQL alter system set open_cursors 400;系统已更改。SQL create pfile /export/home/oracle/inittest1.ora from memory;文件已创建。SQL host more /export/home/oracle/inittest1.ora | grep open_cursors*.open_cursors400从这个结果可以看到CREATE PFILE获取的PFILE只对当前实例有效虽然获取的结果包含多个实例的设置但是这些设置可能和其他实例上的真正设置并不相符。oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html 转载于:https://blog.51cto.com/19880614/1199763
