ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【PARAMETER】获得当前实例的非缺省参数信息

【PARAMETER】获得当前实例的非缺省参数信息

原创 Linux操作系统 作者:secooler 时间:2010-10-14 22:47:58 0 删除 编辑
数据库启动以后包含众多的参数,如何获取数据库中哪些是非缺省参数信息?这个信息可以通过V$PARAMETER获得,该动态性能使用中的ISDEFAULT字段标识了该参数是否为系统的缺省参数。

1.
查询结果
sys@ora10g> col value for a60
sys@ora10g> col name for a30
sys@ora10g> select name,value from v$parameter where isdefault = 'FALSE';


NAME                           VALUE
------------------------------ ------------------------------------------------------------
processes                      500
sga_target                     167772160
control_files                  /oracle/u02/oradata/ORA10G/controlfile/o1_mf_4srph8fv_.ctl,
                               /oracle/u01/app/oracle/flash_recovery_area/ORA10G/controlfil
                               e/o1_mf_4srph96b_.ctl

db_block_size                  8192
compatible                     10.2.0.3.0
log_archive_format             %t_%s_%r.dbf
db_file_multiblock_read_count  16
db_create_file_dest            /oracle/u02/oradata
db_create_online_log_dest_1    /oracle/u01
db_create_online_log_dest_2    /oracle/u02
db_create_online_log_dest_3    /oracle/u02
db_recovery_file_dest          /oracle/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size     4294967296
undo_management                AUTO
undo_tablespace                UNDOTBS1
remote_login_passwordfile      EXCLUSIVE
db_domain
job_queue_processes            10
cursor_sharing                 EXACT
background_dump_dest           /oracle/u01/app/oracle/admin/ora10g/bdump
user_dump_dest                 /oracle/u01/app/oracle/admin/ora10g/udump
core_dump_dest                 /oracle/u01/app/oracle/admin/ora10g/cdump
audit_file_dest                /oracle/u01/app/oracle/admin/ora10g/adump
db_name                        ora10g
open_cursors                   300
pga_aggregate_target           16777216

26 rows selected.

2.V$PARAMETER参数的参考信息
附10g oracle官方文档关于v$parameter的说明,供参考。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2012.htm#REFRN30176

V$PARAMETER

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.

Column Datatype Description
NUM NUMBER Parameter number
NAME VARCHAR2(80) Name of the parameter
TYPE NUMBER Parameter type:
  • 1 - Boolean

  • 2 - String

  • 3 - Integer

  • 4 - Parameter file

  • 5 - Reserved

  • 6 - Big integer

VALUE VARCHAR2(512) Parameter value for the session (if modified within the session); otherwise, the instance-wide parameter value
DISPLAY_VALUE VARCHAR2(512) Parameter value in a user-friendly format. For example, if the VALUE column shows the value 262144 for a big integer parameter, then the DISPLAY_VALUE column will show the value 256K.
ISDEFAULT VARCHAR2(9) Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)
ISSES_MODIFIABLE VARCHAR2(5) Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)
ISSYS_MODIFIABLE VARCHAR2(9) Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:
  • IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.

  • DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.

  • FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.

ISINSTANCE_MODIFIABLE VARCHAR2(5) For parameters that can be changed with ALTER SYSTEm, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If the ISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.
ISMODIFIED VARCHAR2(10) Indicates whether the parameter has been modified after instance startup:
  • MODIFIED - Parameter has been modified with ALTER SESSION

  • SYSTEM_MOD - Parameter has been modified with ALTER SYSTEM (which causes all the currently logged in sessions' values to be modified)

  • FALSE - Parameter has not been modified after instance startup

ISADJUSTED VARCHAR2(5) Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)
ISDEPRECATED VARCHAR2(5) Indicates whether the parameter has been deprecated (TRUE) or not (FALSE)
DESCRIPTION VARCHAR2(255) Description of the parameter
UPDATE_COMMENT VARCHAR2(255) Comments associated with the most recent update
HASH NUMBER Hash value for the parameter name

3.小结
建议对常用的动态性能视图中每一字段进行实践探究。这个过程中我们会有很多意想不到的收获。

Good luck.

secooler
10.10.14

-- The End --



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-567197/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8012190