How to get the INIT.ORA parameter values

Here we see the differenet methods to get the init.ora parameter values

1. Using built in DBMS_UTILITY.GET_PARAMETER_VALUE
2. Show parameter
3. Using V$PARAMETER

Method 1: DBMS_UTILITY.GET_PARAMETER_VALUE
DBMS_UTILITY.GET_PARAMETER_VALUE (arg1, arg2, arg3) takes 3 parameters, arg1 takes IN mode we pass the parameter name for which the value needs to be knows. arg2 and arg3 takes IN OUT mode, arg2 returns the integer value if arg1 is of integer type, arg3 returns the string value if arg1 is of string value. If arg1 of string type then arg3 retruns the string value and arg2 returns the length of the arg3 value.

Here is the sample block to illustrate how to use it.

srikar@ORCL> ed
Wrote file afiedt.buf

  1  declare
  2     l_partype  number;
  3     l_intval   number;
  4     l_strval   varchar2(2000);
  5  begin
  6     dbms_output.put_line ('Enter the parameter name: ');
  7     l_partype :=  dbms_utility.get_parameter_value ('&l_param4',l_intval,l_strval);
  8     dbms_output.put('The parameter type is:');
  9     if (l_partype = 1) then
 10     dbms_output.put('String ');
 11     else
 12     dbms_output.put('Number ');
 13     end if;
 14     dbms_output.put(' and the value is: ');
 15     if (l_partype = 1) then
 16     dbms_output.put_line(l_strval);
 17     else
 18     dbms_output.put_line(l_intval);
 19     end if;
 20     dbms_output.put_line('Both the inout values are: l_intval: '
 21                           ||l_intval
 22                           ||' l_strval: '
 23                           ||l_strval ) ;
 24* end;
srikar@ORCL> /
Enter value for l_param4: control_files
old   7:    l_partype :=  dbms_utility.get_parameter_value ('&l_param4',l_intval,l_strval);
new   7:    l_partype :=  dbms_utility.get_parameter_value ('control_files',l_intval,l_strval);
Enter the parameter name:
The parameter type is:String  and the value is: C:\APP\SRIKARDASARI\ORADATA\ORCL\CONTROL01.CTL
Both the inout values are: l_intval: 46 l_strval: C:\APP\SRIKARDASARI\ORADATA\ORCL\CONTROL01.CTL

PL/SQL procedure successfully completed.

Method 2: SHOW PARAMETER
This is simple and straight forward. We pass the parameter name to SHOW PARAMETER let say for control_files,

srikar@ORCL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\APP\SRIKARDASARI\ORADATA\OR
                                                 CL\CONTROL01.CTL, C:\APP\SRIKA
                                                 RDASARI\FLASH_RECOVERY_AREA\OR
                                                 CL\CONTROL02.CTL 

Method 3: V$PARAMETER
We can query against the V$PARAMETER view to get the parameter name.

srikar@ORCL> select name, value from v$parameter where name='control_files';
NAME    VALUE
-------------------------------------------------------------------------------------------control_files C:\APP\SRIKARDASARI\ORADATA\ORCL\CONTROL01.CTL, C:\APP\SRIKARDASARI\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL

srikar@ORCL> select name, value from v$spparameter where name='control_files';

NAME VALUE
-------------------------------------------------------------------------------------------control_files
C:\app\SrikarDasari\oradata\orcl\control01.ctl

control_files
C:\app\SrikarDasari\flash_recovery_area\orcl\control02.ctl