Database Reference
In-Depth Information
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:
EODA@ORA12CR1> select name, scott.get_param( name ) val
2 from v$parameter
3 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*
25 rows selected.
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.
Note
If you were to count the number of documented parameters you can set in each of the database versions—9 i
Release 2, 10 g Release 2, 11 g Release 1, 11g Release 2 and 12 c Release 1—you'd probably find 258, 259, 294, 342 and
368 different parameters, respectively (I'm sure there could be additional parameters available on an operating
system-specific basis). In other words, 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, in Oracle 9.0.1 and before—back to version 6 of Oracle—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 release
subsequent to 9.0.1. In fact, attempting to use that parameter with subsequent releases raises an error. For example:
EODA@ORA12CR1> 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.
 
 
Search WWH ::




Custom Search