Monday, April 20, 2009

Dynamic view V$PARAMETER

You can use the V$PARAMETER dynamic view to see the current setting of the different database parameters. In this example, we use the DESC SQL*Plus command to describe the V$PARAMETER view, and we then query the V$PARAMETER view to see the value of the control_file parameter setting:

SQL> desc v$parameter
Name Null? Type
----------------------------------------- -------- -------------
NUM NUMBER
NAME VARCHAR2(80)
TYPE NUMBER
VALUE VARCHAR2(512)
DISPLAY_VALUE VARCHAR2(512)
ISDEFAULT VARCHAR2(9)
ISSES_MODIFIABLE VARCHAR2(5)
ISSYS_MODIFIABLE VARCHAR2(9)
ISINSTANCE_MODIFIABLE VARCHAR2(5)
ISMODIFIED VARCHAR2(10)
ISADJUSTED VARCHAR2(5)
ISDEPRECATED VARCHAR2(5)
DESCRIPTION VARCHAR2(255)
UPDATE_COMMENT VARCHAR2(255)
HASH NUMBER
SQL> select name, value from v$parameter where name = 'control_files';
NAME VALUE
-------------------- -----------------------------------------------
control_files C:\ORACLE\ORADATA\BOOKTST\BOOKTST\CONTROL01.CTL, C:\ORACLE
\ORADATA\BOOKTST\BOOKTST\CONTROL02.CTL, C:\ORACLE\ORADATA\
BOOKTST\BOOKTST\CONTROL03.CTL

You may also use the shortcut “show parameter” command. For instance:

SQL> show parameter control_files;

The Parameter File at Startup Time

Oracle prefers the use of an SPFILE to a PFILE. When you startup your Oracle database, Oracle will scan the contents of your parameter directory ($ORACLE_HOME/database on Windows or the Linux directory name $ORACLE_HOME/dbs), searching in the following order:

* spfileSID.ora

* spfile.ora

* initSID.ora

* init.ora

If the directory contains none of the above, then the startup will fail.

No comments: