Checking database parameters in Oracle

A very easy way to check parameter values for Oracle database is to issue following command from SQL*Plus prompt. It is followed by abbreviated output.

SQL>show parameter

NAME                        TYPE        VALUE
————————— ———– ——————————
background_core_dump        string      partial
background_dump_dest        string      D:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\BDUMP
backup_tape_io_slaves       boolean     FALSE
bitmap_merge_area_size      integer     1048576
blank_trimming              boolean     FALSE

To view specific values of parameters related to SGA command should be

SQL>show parameter area

NAME                        TYPE        VALUE
————————— ———– ——————————
bitmap_merge_area_size      integer     1048576
create_bitmap_area_size     integer     8388608
hash_area_size              integer     131072
sort_area_retained_size     integer     0
sort_area_size              integer     65536
workarea_size_policy        string      AUTO

Alternatively Oracle provides two dynamic performance views to look at all the parameter values specified for the database.

V$Parameter – provides information about all parameters. It also indicates whether parameters are modifiable at session level or system level. Following is the query to look for parameters related to SGA.

SQL> SELECT name,value,isses_modifiable,issys_modifiable
FROM v$parameter
WHERE name like ‘%sga%’;

NAME            VALUE           ISSES ISSYS_MOD
————— ————— —– ———
sga_max_size    612368384       FALSE FALSE
pre_page_sga    FALSE           FALSE FALSE
lock_sga        FALSE           FALSE FALSE
sga_target      612368384       FALSE IMMEDIATE

V$NLS_Parameters – provides information about all parameters related to NLS settings.

SQL> select * from v$nls_parameters;

PARAMETER                      VALUE
—————————— ——————————
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               WE8MSWIN1252
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

Certain parameters can be changed dynamically using ALTER SYSTEM command. Other parameter values need to be changed in init.ora or spfile or both.