Shulou(Shulou.com)05/31 Report--

This article mainly explains the "query and modification methods of Oracle parameters", the content of the explanation is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn the "query and modification methods of Oracle parameters"!

Test environment

DB Version: oracle 11.2 RAC

OS: RHEL 6.x

The difference between v$parameter, v$parameter2, v$system_parameter, v$system_parameter2 and v$spparameter

The views of the main query parameters in Oracle 11g are v$parameter, v$parameter2, v$system_parameter, v$system_parameter2, v$spparameter, dba_hist_parameter

What's the difference between v$parameter and v$parameter2? The difference is the same as v$system_parameter and v$system_parameter2. Ha ha, specifically as follows:

What is stored in v$parameter is the value of each parameter, a parameter and a record. V$parameter2 also stores the value of each parameter, but in v$parameter2 there is only one value per line of name. Take control_files as an example, this parameter will correspond to at least two values, so there will be only one record in v$parameter and two records in v$parameter2. In fact, I really do not understand why oracle has to design such a view, where is the use scene? Children's shoes who know are welcome to tell me.

The difference between v$parameter and v$system_parameter: v$parameter is the parameter/value that stores the current session, while v$system_parameter stores the current instance-level parameter/value;, that is, a newly created session. The parameter/value in v$parameter and v$system_parameter is the same; because session level parameter is inherited from instance level parameter; then you can change the value of v$parameter through alter session, but not v$system_parameter data.

I try to execute an alter session from session1, and then session1 to see that the value of v$ parameter has changed. When I open session2, I see that the value of v$parameter is still the old value, which puzzles me. Doesn't v$parameter see the value after alter session? Why do session1 and session2 see different results? This is because v$parameter shows the parameter name/value of the current session.

V$spparameter and v$system_parameter: the difference between these two view is that v$spparameter is used to store parameter name/value in spfile, while v$system_parameter is used to store instance level where is the current parameter name/value;?

Examples

SQL > select name, value from v$system_parameter where name='cursor_sharing'

NAME VALUE

Cursor_sharing EXACT

SQL > select name, value from v$spparameter where name='cursor_sharing'

NAME VALUE

Cursor_sharing EXACT

SQL > select name, value from v$parameter where name='cursor_sharing'

NAME VALUE

Cursor_sharing EXACT

-query results of three tables after executing alter session

Session 1:

SQL > select userenv ('sid') from dual

USERENV ('SID')

-

five hundred and sixty nine

SQL > alter session set cursor_sharing='FORCE'

Session altered.

SQL > select name, value from v$parameter where name='cursor_sharing'

NAME VALUE

Cursor_sharing FORCE

SQL > select name, value from v$spparameter where name='cursor_sharing'

NAME VALUE

Cursor_sharing EXACT

SQL > select name, value from v$system_parameter where name='cursor_sharing'

NAME VALUE

Cursor_sharing EXACT

Session 2:

SQL > select userenv ('sid') from dual

USERENV ('SID')

-

eight hundred and fifty three

SQL > select name, value from v$parameter where name='cursor_sharing'

NAME VALUE

Cursor_sharing EXACT

SQL > select name, value from v$spparameter where name='cursor_sharing'

NAME VALUE

Cursor_sharing EXACT

SQL > select name, value from v$system_parameter where name='cursor_sharing'

NAME VALUE

Cursor_sharing EXACT

From here, we can see that after being modified by alter session, the v$parameter in the current session1, cursor_sharing has been changed to FORCE, but the value in the v$parameter of another session has not changed; the value in v$system_parameter and v$spparameter has not changed.

Example 2:

Just modify the v$spparameter

Through alter system set... Scope=spfile, so that the modified parameters will only go into spfile, so only v$spparameter can see them.

Before modification

SQL > select name, value from v$parameter where name='cursor_sharing'

NAME VALUE

Cursor_sharing EXACT

SQL > select name, value from v$parameter where name='open_cursors'

NAME VALUE

Open_cursors 500

SQL > select name, value from v$system_parameter where name='open_cursors'

NAME VALUE

Open_cursors 500

SQL > select name, value from v$spparameter where name='open_cursors'

NAME VALUE

Open_cursors 500

After modification

SQL > alter system set open_cursors=3000 scope=spfile sid='*'

System altered.

SQL > select name, value from v$system_parameter where name='open_cursors'

NAME VALUE

Open_cursors 500

SQL > select name, value from v$spparameter where name='open_cursors'

NAME VALUE

Open_cursors 3000

SQL > select name, value from v$parameter where name='open_cursors'

NAME VALUE

Open_cursors 500

Example 3

Modify only v$system_parameter

Before modification

SQL > select name, value from v$spparameter where name='db_file_multiblock_read_count'

NAME VALUE

Db_file_multiblock_read_count

Note that NULL is shown here, indicating that the parameter value is not set in spfile, but there is a value in v$system_parameter, because the system will calculate a value according to certain rules.

SQL > select name, value from v$system_parameter where name='db_file_multiblock_read_count'

NAME VALUE

Db_file_multiblock_read_count 128

SQL > select name, value from v$parameter where name='db_file_multiblock_read_count'

NAME VALUE

Db_file_multiblock_read_count 128

After modification

SQL > alter system set db_file_multiblock_read_count=256 scope=memory sid='*'

System altered.

SQL > select name, value from v$spparameter where name='db_file_multiblock_read_count'

NAME VALUE

Db_file_multiblock_read_count

SQL > select name, value from v$system_parameter where name='db_file_multiblock_read_count'

NAME VALUE

Db_file_multiblock_read_count 256

SQL > select name, value from v$parameter where name='db_file_multiblock_read_count'

NAME VALUE

Db_file_multiblock_read_count 256

The value in v$spparameter is still empty, the value of v$system_parameter is changed to 256, and the value in v$parameter is also 256. What does that mean? Indicates that if a parameter does not execute alter session, then the value of this parameter is the same in v$parameter and v$system_parameter

Of course, you can also use alter system set. Scope=BOTH sid='*' makes changes visible in both v$spparameter and v$system_parameter; that's the difference between scope=BOTH/MEMORY/SPFILE

There are 355 parameter in oracle 11G, so which one can be modified by alter session and which one can be modified by alter system? And those that cannot be modified? You can know this through v$system_parameter.

Isses_modifiable: whether it can be modified by alter session. There are only two values: TRUE/FALSE

Issys_modifiable: whether it can be modified through alter system: there are three values: IMMEDIATE/DEFERRED/FALSE (IMMEDIATE means to take effect immediately, and it will take effect on the current session; DEFFERED: it does not take effect on all currently connected session, only on subsequent connections. FALSE means that alter system modification is not allowed)

For the parameters of ISSYS_MODIFIABLE=DEFERRED, you must pass alter system. Only deferred can be modified; otherwise, an error ORA-02096 will be reported.

ORA-02096: specified initialization parameter is not modifiable with this option

When using ASMM or AMM, many parameter value are automatically adjusted by the system. What if you modify a parameter and want to return it to the system for automatic management? In fact, it is to restore the default value. You can use SQL: alter system reset name= scope=... Note that the RESET command is used here. The reset command actually means not to set it, that is, to delete an item from the spfile, so if you want to delete it, you must have the existing item, so if you want to reset a parameter that does not exist, you will also report an error. Or in RAC, if the setting is set by instance by instance and when reset is set through alter system reset. Sid='*' will also report an error.

SQL > select name, value from v$spparameter where name='db_file_multiblock_read_count'

NAME VALUE

-

Db_file_multiblock_read_count

This parameter value cannot be found in v$spparameter, which means it is not set.

SQL > alter system reset db_file_multiblock_read_count scope=spfile sid='*'

Alter system reset db_file_multiblock_read_count scope=spfile sid='*'

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

There is a mistake here.

This is one case. For RAC, you can instance by instance the parameter information.

Here you can see that the parameter value of each instnace is different. Even if value is the same, you can also set instance by instance.

SQL > select sid, name, value from v$spparameter where name='db_file_multiblock_read_count'

SID NAME VALUE

-

Racaaweb1 db_file_multiblock_read_count 64

Racaaweb2 db_file_multiblock_read_count 256

SQL > alter system reset db_file_multiblock_read_count scope=spfile sid='*'

Alter system reset db_file_multiblock_read_count scope=spfile sid='*'

ERROR at line 1:

ORA-32010: cannot find entry to delete in SPFILE

There's still a mistake here.

Implied parameter

Oracle also has another parameter, called an implicit parameter, whose name is underlined _ prefixed. By default, implicit parameters do not appear in the above tables unless you modify the values of these parameters

Examples

Alter system set "_ undo_autotune" = TRUE

This statement modifies the value of the implicit parameter _ undo_autotune; note here:

1) when modifying implied parameters, the implied parameter names need to be enclosed in double quotation marks

2) for those without scope, the default value of scope is BOTH

3) for those without sid, the default value of sid is'*'.

The updated values are as follows:

SQL > select name, value from v$spparameter where name='_undo_autotune'

NAME VALUE

-

_ undo_autotune TRUE

SQL > select name, value from v$system_parameter where name='_undo_autotune'

NAME VALUE

-

_ undo_autotune TRUE

That is, all parameters that have been modified and are currently in effect can be seen in the v$parameter, whether implied or not. So how to query which implicit parameters that have not been modified? Use SQL:

Hidden Parameter

Set linesize 1000

SET VERIFY OFF

COLUMN parameter FORMAT a37

COLUMN description FORMAT a50 WORD_WRAPPED

COLUMN session_value FORMAT a10

COLUMN instance_value FORMAT a10

SELECT a.ksppinm AS parameter

A.ksppdesc AS description

B.ksppstvl AS session_value

C.ksppstvl AS instance_value

FROM x$ksppi a

X$ksppcv b

X$ksppsv c

WHERE a.indx = b.indx

AND a.indx = c.indx

AND a.ksppinm LIKE'%'| | LOWER ('& 1') | |'% 'ESCAPE' /'

ORDER BY a.ksppinm

Supplement

1: x$ksppi: all parameter name; x$ksppcv stores parameter value of session level, and x$ksppsv stores parameter value; of instance level. You can see this by querying x$fixed_view_definition.

2. Oracle snapshot also records these parameter value, so you can see whether there are any changes from here.

3. When you modify any parameter, it will be recorded in the alert log, so you can see all the changed parameter from here.

Thank you for your reading, the above is the content of "query and modification methods of Oracle parameters". After the study of this article, I believe you have a deeper understanding of the query and modification methods of Oracle parameters, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.