lunes, 4 de abril de 2011

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

No hay comentarios:

Publicar un comentario