How Oracle queries initialization parameters (ii) V$parameter differs from V$system_parameter view

Query initialization parameters A lot of ways, such as show PARAMETER, or query V$parameter, here a brief summary.

This article describes the difference between the V$parameter view and the V$system_parameter view.

The previous article introduced a number of methods for querying initialization parameters, and explained the difference between the V$parameter view and the V$parameter2 view.

Here's a look at the difference between V$parameter and v$system_parameter views.

Generally in the query initialization parameters are habitually used show PARAMETER, that is, query V$parameter view, but sometimes query this view results are not accurate:

Sql> Show Parameter query_rewrite_enabled

NAME TYPE VALUE

------------------------------------ ----------- --------------------

query_rewrite_enabled string TRUE

Sql> select name, value

2 from V$parameter

3 WHERE name = ' query_rewrite_enabled ';

NAME VALUE

---------------------------------------- ----------------------------

Query_rewrite_enabled TRUE

Sql> select name, value

2 from V$system_parameter

3 WHERE name = ' query_rewrite_enabled ';

NAME VALUE

---------------------------------------- ----------------------------

Query_rewrite_enabled TRUE

This is if you modify query_rewrite_enabled this initialization parameter at the session level:

Sql> alter session Set query_rewrite_enabled = FALSE;

The session has changed.

Sql> Show Parameter query_rewrite_enabled

NAME TYPE VALUE

------------------------------------ ----------- -------------------

query_rewrite_enabled string FALSE

Sql> select name, value

2 from V$parameter

3 WHERE name = ' query_rewrite_enabled ';

NAME VALUE

---------------------------------------- ---------------------------

Query_rewrite_enabled FALSE

Sql> select name, value

2 from V$system_parameter

3 WHERE name = ' query_rewrite_enabled ';

NAME VALUE

---------------------------------------- ---------------------------

Query_rewrite_enabled TRUE

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45544.htm

As you can see, the results of show parameter and query V$parameter view are false, and the changes just made are session-level, and the system's initialization parameters are not modified. As mentioned in the previous description of the two views, the V$parameter view reflects the values that the initialization parameter takes effect in the current session, and the v$system_parameter reflects the initialization parameters at the instance level.

Let's look at the delay parameter modifications:

Sql> select name, value

2 from V$parameter

3 WHERE name = ' RecycleBin ';

NAME VALUE

---------------------------------------- ------------------------------------

RecycleBin on

Sql> select name, value

2 from V$system_parameter

3 WHERE name = ' RecycleBin ';

NAME VALUE

---------------------------------------- ------------------------------------

RecycleBin on

Sql> alter system Set RecycleBin = off deferred scope = memory;

The system has changed.

Sql> select name, value

2 from V$parameter

3 WHERE name = ' RecycleBin ';

NAME VALUE

---------------------------------------- ------------------------------------

RecycleBin on

Sql> select name, value

2 from V$system_parameter

3 WHERE name = ' RecycleBin ';

NAME VALUE

---------------------------------------- ------------------------------------

RecycleBin off

The result and the preceding, in turn, are not changed in the V$parameter view, and the result of the V$system_parameter view turns off. This is because deferred modifications do not take effect on the currently existing session in the database, so the V$parameter view results that reflect the current session situation are unchanged, and for the system, initialization parameters have changed and the parameters of all new sessions are changed, so V$system_ The results of the parameter view have changed.

Sql> CONN yangtk/yangtk@ytk111 is connected.

Sql> select name, value

2 from V$parameter

3 WHERE name = ' RecycleBin ';

NAME VALUE

---------------------------------------- ---------------------------

RecycleBin off

Sql> select name, value

2 from V$system_parameter

3 WHERE name = ' RecycleBin ';

NAME VALUE

---------------------------------------- ---------------------------

RecycleBin off

According to these two examples, using the V$parameter view to get the initialization parameters of the system is inaccurate and should be obtained from the V$system_parameter view.