In simple terms, a database parameter may be thought of as a key/value pair. To see the current value of an instance parameter, you can query the V$ view V$PARAMETER. Alternatively, in SQL*Plus you can use the SHOW PARAMETER command, for example:
$ sqlplus / as sysdba
SQL> select value from v$parameter where name = ‘db_block_size’;
8192
SQL> show parameter db_block_s
NAME TYPE VALUE
db_block_size integer 8192
Both outputs show basically the same information, although you can get more information from V$PARAMETER (there are many more columns to choose from than displayed in this example). But SHOW PARAMETER wins for me in ease of use and the fact that it “wildcards” automatically. Notice that I typed in only db_block_s; SHOW PARAMETER adds % to the front and back.
Note All V$ views and all dictionary views are fully documented in the Oracle Database Reference manual. Please regard that manual as the definitive source of what is available in a given view.
If you were to execute the preceding example as a less-privileged user (EODA has been granted the DBA role for the purposes of this book), you would see instead
Not every parameter is available via the dbms_utility.get_parameter_value API call. Specifically, the memory-related parameters such as sga_max_size, db_cache_size, pga_aggregate_target, and the like are not visible. We deal with that in the code on lines 17 through 21—we return ‘*access denied*’ when we hit a parameter that we are not allowed to see. If you are curious about the entire list of restricted parameters,
you can (as can any account that has been granted EXECUTE on this function) issue the following query:
$ sqlplus scott/tiger@PDB1
SQL> select name, scott.get_param( name ) val from v$parameter
where scott.get_param( name ) = ‘access denied‘;
NAME VAL
sga_max_size access denied
shared_pool_size access denied
large_pool_size access denied
java_pool_size access denied
streams_pool_size access denied
…
client_result_cache_lag access denied
olap_page_pool_size access denied
33 rows selected.
Note You’ll see different results for this query on different versions. You should expect the number and values of inaccessible parameters to go up and down over time as the number of parameters changes.
The number of parameters (and their names) varies by release. Most parameters, like db_block_size, are very long-lived (they won’t go away from release to release), but over time many other parameters become obsolete as implementations change. For example, there was a distributed_transactions parameter that could be set to some positive integer and that controlled the number of concurrent distributed transactions the database could perform. It was available in prior releases, but it is not found in any recent release of Oracle. In fact, attempting to use that parameter with subsequent releases raises an error. For example:
$ sqlplus / as sysdba
SQL> alter system set distributed_transactions = 10; alter system set distributed_transactions = 10 *
ERROR at line 1:
ORA-25138: DISTRIBUTED_TRANSACTIONS initialization parameter has been made
obsolete
If you would like to review the parameters and get a feeling for what is available and what each parameter does, refer to the Oracle Database Reference manual. The first chapter of this manual examines every documented parameter in detail. On the whole, the default value assigned to each parameter (or the derived value for parameters that obtain their default settings from other parameters) is sufficient for most systems. In general, the values of parameters, such as the control_files parameter (which specifies the location of the control files on your system), db_block_size, various memory-related parameters, and so on, need to be set uniquely for each database.
Notice I used the term “documented” in the preceding paragraph. There are undocumented parameters as well. You can identify these because their names begin with an underscore (_). There is a great deal of speculation about these parameters. Since they are undocumented, some people believe they must be “magical,” and many people assume that they are well known and used by Oracle insiders. In fact, I find the opposite to be true. They are not well known and they are hardly ever used. Most of these undocumented parameters are rather boring, actually, as they represent deprecated functionality and backward compatibility flags. Others help in the recovery of data, not of the database itself; for example, some of them enable the database to start up in certain extreme circumstances, but only long enough to get data out. You have to rebuild after that.
Unless you are so directed by Oracle Support, there is no reason to have an undocumented parameter in your configuration. Many have side effects that could be devastating. In my production database, I don’t want to use any undocumented settings.
Caution Use undocumented parameters only at the request of Oracle Support. Their use can be damaging to a database, and their implementation can—and will—change from release to release.
You may set the various parameter values in one of two ways: either just for the current instance or persistently. It is up to you to make sure that the parameter files contain the values you want them to. When using legacy init.ora parameter files, this is a manual process. To change a parameter value persistently, to have that new setting be in place across server restarts, you must manually edit and modify the init.ora parameter file. With server parameter files, you’ll see that this has been more or less fully automated for you in a single command.