How Oracle Queries initialization parameters (iii) V$spparameter differs from Gv$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$spparameter view and the Gv$parameter view.

The previous article describes the difference between V$system_parameter and V$parameter views, which focuses on queries that initialize parameters in a RAC environment.

As already mentioned, using the show parameter query to see the initialization parameters that the current session can see, this argument is not visible from this command because it is set globally or by the current instance.

Although Oracle provides initialization parameters at the beginning of the gv$, it can be used to query settings on two instances, but it is not so simple.

A simple example:

Sql> Show Parameter Open_cursors

NAME TYPE VALUE

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

Open_cursors Integer 300

Sql> alter system Set Open_cursors = Scope = Both sid = ' test1 ';

The system has changed.

Sql> Disc

From Oracle database11genterprise Edition release11.1.0.6.0-64bit Production

With the partitioning, real application clusters, OLAP, Data Mining

and real application testing options disconnected

Sql> Set Instance Test2

Oracle Database11grelease 11.1.0.0.0-production

Sql> Conn SYS as SYSDBA

Enter Password:

is connected.

Sql> alter system Set Open_cursors = Scope = Both sid = ' test2 ';

The system has changed.

Sql> Disc

From Oracle database11genterprise Edition release11.1.0.6.0-64bit Production

With the partitioning, real application clusters, OLAP, Data Mining

and real application testing options disconnected

Sql> Set Instance Local

Oracle database11grelease11.1.0.0.0-production

Sql> Conn/as SYSDBA

is connected.

Now look at the results of different query methods:

Sql> select name, value

2 from V$parameter

3 WHERE name = ' open_cursors ';

NAME VALUE

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

Open_cursors 500

Sql> Select inst_id, name, value

2 from Gv$parameter

3 WHERE name = ' open_cursors ';

inst_id NAME VALUE

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

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

1 Open_cursors 500

2 Open_cursors 400

Sql> Show Parameter Open_cursors

NAME TYPE VALUE

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

Open_cursors Integer 500

sql> Select SID, Name, value

2 from V$spparameter

3 WHERE name = ' open_cursors ';

SID NAME VALUE

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