Systems Engineering and RDBMS

  • Click Here for Decipher's Homepage

  • Categories

  • Questions?

    Please send your wish list of things that you would like us to write about or if you have suggestions to help improve this blog site. You can send all questions/suggestions to: Blog Support
  • Archives

  • Blog Stats


Checking database parameters in Oracle

Posted by decipherinfosys on March 20, 2007

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
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_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_SORT                       BINARY

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

2 Responses to “Checking database parameters in Oracle”

  1. […] Checking database parameters in Oracle […]

  2. […] blog post, we showed how we can check database parameters and their values in different ways in Oracle and in SQLServer and DB2 […]

Sorry, the comment form is closed at this time.

%d bloggers like this: