Use of the Oracle 11g show spparameter command

Use of the Oracle 11g show spparameter command

When we want to obtain the current parameter value of the Oracle database, we can query the dynamic performance view of v $ parameter. Of course, we can also use the show parameter p_name method. However, before Oracle 11g, show parameter can only obtain the parameter value of the current session, depending on other parameter values, for example, you need to use a data dictionary to modify system-level parameter values and the parameter values in the spfile. Oracle made some changes in 11g and introduced the show spparameter p_name command, which makes it easier for us to obtain the parameter values in the spfile file. Obviously, this is just a small trick, but it makes Oracle more flexible and convenient to use.

SQL> show parameter open_cursors
NAME TYPE VALUE
-----------------------------------------------------------------------------
Open_cursors integer 400

We only need to modify the parameter file and add scope = spfile.
SQL> alter system set open_cursors = 300 scope = spfile;
System altered.

If you want to view the parameter value, view the current session
SQL> show parameter open_cursors

NAME TYPE VALUE
-----------------------------------------------------------------------------
Open_cursors integer 400

SQL> desc v $ parameter
Name Null? Type
-----------------------------------------------------------------------------
NUM NUMBER
NAME VARCHAR2 (80)
TYPE NUMBER
VALUE VARCHAR2 (4000)
DISPLAY_VALUE VARCHAR2 (4000)
ISDEFAULT VARCHAR2 (9)
ISSES_MODIFIABLE VARCHAR2 (5)
ISSYS_MODIFIABLE VARCHAR2 (9)
ISINSTANCE_MODIFIABLE VARCHAR2 (5)
ISMODIFIED VARCHAR2 (10)
ISADJUSTED VARCHAR2 (5)
ISDEPRECATED VARCHAR2 (5)
ISBASIC VARCHAR2 (5)
DESCRIPTION VARCHAR2 (255)
UPDATE_COMMENT VARCHAR2 (255)
HASH NUMBER

SQL> select name, value from v $ parameter where name = 'open _ cursors ';

NAME VALUE
------------------------------------------------------------
Open _cursors 400

If you want to view the value in the parameter file after parameter modification
SQL> desc v $ spparameter
Name Null? Type
-----------------------------------------------------------------------------
SID VARCHAR2 (80)
NAME VARCHAR2 (80)
TYPE VARCHAR2 (11)
VALUE VARCHAR2 (255)
DISPLAY_VALUE VARCHAR2 (255)
ISSPECIFIED VARCHAR2 (6)
ORDINAL NUMBER
UPDATE_COMMENT VARCHAR2 (255)

SQL> col name for a15
SQL> col value for a45

SQL> select name, value from v $ spparameter where name = 'open _ cursors ';

NAME VALUE
------------------------------------------------------------
Open _cursors 300

SQL> show spparameter open_cursors

SID NAME TYPE VALUE
----------------------------------------------------------------------------
* Open_cursors integer 300

Obviously, with show spparameter, the operation is simpler.