Get the init.ora parameter value using DBMS_UTILITY

“Init.ora” is a text file, which can be viewed using a text
editor. Alternatively, we can use DBMS_UTILITY, a PL/SQL package that
comes along with the standard database installation. The DBMS_UTILITY
contains many useful functions, including GET_PARAMETER_VALUE. The GET_PARAMETER_VALUE
procedure gets “init.ora” parameter values. The parameter name is
supplied as an input to the procedure.

DBMS_UTILITY.GET_PARAMETER_VALUE (

   parnam IN     VARCHAR2,

   intval IN OUT BINARY_INTEGER,

   strval IN OUT VARCHAR2)

  RETURN BINARY_INTEGER;

Where:

Return value is:

Here
is a sample SQL *PLUS script that uses GET_PARAMETER_VALUE:

SET SEVEROUTPUT ON

DECLARE

  parnam VARCHAR2(256);

  intval BINARY_INTEGER;

  strval VARCHAR2(256);

  partyp BINARY_INTEGER;

BEGIN

  partyp := dbms_utility.get_parameter_value

			(‘db_files’,intval, strval);

  — check for parameter type

  IF partyp = 1 THEN

    dbms_output.put_line(‘The parameter type is string’);

  ELSE

    dbms_output.put_line(‘ The parameter type is integer’);

  END IF;
  — print value

  dbms_output.put(‘The parameter value is: ‘);

  IF partyp = 1 THEN

    dbms_output.put_line(strval);

  ELSE

    dbms_output.put_line(intval);

  END IF;
  — if paramter type is string Print Value length

  IF partyp = 1 THEN

    dbms_output.put(‘Length of the string is: ‘);

    dbms_output.put_line(intval);

  END IF;

END;

/

show errors;

SET SEVEROUTPUT OFF

SQL>  @c:ajaydu.sql

The parameter type is: integer

The parameter value is: 1024

PL/SQL procedure successfully completed.
No errors.

SQL>

Change
the parameter type in the above example to ‘db_name‘ and the output will
be as shown below;

SQL>  @c:ajaydu.sql

The parameter type is: string

The parameter value is: mydb

Length of the string is: 4

PL/SQL procedure successfully completed.
No errors.

Summary

You can use the DBMS_UTILITY package to
get values of parameters or use the Oracle provided dynamic view V$PARAMETER
to get parameter values. The TYPE column of the view contains the values
1,2 and 3 for BOOLEAN, STRING and INTEGER values respectively.