How Oracle queries initialization parameters (iv) the necessity of describing Gv$spparameter parameters

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

This article describes the necessity of gv$spparameter parameters.

The previous article describes the V$spparameter parameter and also explains the difference between the information in the V$spparameter view and the Gv$parameter view.

In fact, there is still a problem here, is whether the gv$spparameter is meaningful. Because the V$spparameter parameter itself contains the SID column, and the SPFile itself contains the settings for all instances, then querying the Gv$spparameter view does not make much sense.

Because each node of the RAC can be started with a unified spfile, you can also select a different spfile to start, and the result in Gv$spparameter view is the result of the actual instance spfile.

This is more difficult to understand, look at a simple example:

Sql> Select inst_id, name, value

2 from Gv$system_parameter

3 WHERE name = ' open_cursors ';

inst_id NAME VALUE

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

1 Open_cursors 600

2 Open_cursors 400

sql> Select SID, Name, value

2 from V$spparameter

3 WHERE name = ' open_cursors ';

SID NAME VALUE

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

* Open_cursors 300

Test1 Open_cursors 500

Test2 open_cursors 700

Sql> Select inst_id, sid, name, value

2 from Gv$spparameter

3 WHERE name = ' open_cursors ';

inst_id SID NAME VALUE

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

1 * open_cursors 300

1 Test1 open_cursors 500

1 Test2 open_cursors 700

2 * open_cursors 300

2 Test1 open_cursors 500

2 Test2 open_cursors 700

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

6 rows have been selected.

Sql> Select inst_id, name, value

2 from Gv$system_parameter

3 WHERE name = ' SPFile ';

inst_id NAME VALUE

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

1 SPFile +data/test/spfiletest.ora

2 SPFile +data/test/spfiletest.ora

The following is an in-memory parameter to create the SPFile, and the new SPFile is used to start the current instance:

sql> create spfile= '/export/home/oracle/spfiletest1.ora ' from memory;

The file has been created.

Sql> Host

$ vi/export/home/oracle/inittest1.ora