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.

ver fuente