Oracle Cloud Infrastructure Documentation

View and Edit Database Parameters

As part of the managing and monitoring tasks associated with a Managed Database, you can view and edit the database initialization parameters for the database.

The initialization parameters file contains a list of parameters and a value for each parameter, and affects the basic operation of the database. For example, initialization parameters can be used to:

  • Set limits for the entire database
  • Set limits on database resources
  • Set user or process limits

To view database parameters, go to the Managed database details page and click Database parameters on the left pane under Resources. The Database parameters section has the following tabs:

  • Current: Lists the initialization parameters that are currently active (in memory) for the database instance.
  • SP file: Lists all the initialization parameters in the Server Parameter (SP) file, which is the preferred form of an initialization parameter file. The file location is displayed on the tab.
Note

For Autonomous Databases, only a subset of initialization parameters that can be modified are listed in the Database parameters section.

By default, even hidden database parameters are listed on the tabs in the Database parameters section, and to ensure that hidden parameters are not listed, deselect the Show hidden parameters check box on the left pane.

The Current and SP file tabs list the following details:

  • Name of the parameter.
  • Value of the parameter, such as True or False.
  • Comments added for the parameter, if any.
  • Type of parameter, such as Boolean or String.
  • A check mark to indicate if the parameter was modified.
  • A check mark to indicate if the parameter is a Dynamic parameter. The parameters without a check mark are Static parameters.
  • Category of the parameter, such as File configuration.
Note

If the Managed Database is a RAC database, then the Database parameters section has the following additional columns:
  • Instance: Displays the RAC instance to which the database parameter value applies. If an asterisk "*" is displayed in the Instance column, it indicates that the parameter has the same value for all the instances in the RAC database, unless it's overridden by a specific instance.
  • Constraint: Displays the constraint, if any, which can either be Identical or Unique. Identical indicates that the parameters that are critical for database creation or that affect certain database operations must have the same value for every instance in the Oracle RAC database. Unique indicates that the parameters are unique to each instance, such as the INSTANCE_NUMBER parameter.

You can use the Export to file option above the parameters to download the parameters. You can also use the drop-down lists available under Filters on the left pane to filter the database parameters. These drop-down lists can be used in isolation or together, for example, you can select the following options to view all the dynamic parameters that have been modified.

  • Yes in the Modified drop-down list
  • Yes in the Dynamic drop-down list
  • All in the Category drop-down list

Edit Database Parameters

You can edit and reset up to 20 initialization parameters at a time in the Database parameters section on the Managed database details page.

Note

For a RAC database, you can only edit RAC database-level parameters, which are denoted by an asterisk in the Instance column, and do not have Unique constraints.

On the Current tab, you can only edit dynamic initialization parameters that are currently active. To do so:

  1. Click Edit parameters.
  2. In the Edit parameters panel:
    1. Provide the following details:
      1. Connection credentials: Select one of the available options in the Credential type drop-down list to specify database credentials to connect to the Managed Database. For information on credential types, see Use Credentials to Perform Diagnostics & Management Tasks.
        Note

        The database user must have the ALTER SYSTEM privileges to edit parameters.
      2. Parameters: Set the scope and edit the parameters.

        The Apply changes in current running instances mode to SP file to persist the changes. check box is selected by default and ensures that the changes made to the current parameter are applied to the SP file and persist even after the server is restarted: SCOPE=BOTH. If you deselect the check box, then the changes are only applied to the current instance, and will not persist after the server is restarted: SCOPE=MEMORY.

        You can filter the parameters by Category or search for a particular parameter and edit the parameter value, add or edit comments, and reset values to default.

        Note

        You can reset currently active database parameters to default only for Oracle Database version 12.2.0.4 and later.
    2. Optionally, click Show SQL to view the SQL statement that will be executed.
    3. Click Save changes to save the changes made to the parameters.

On the SP file tab, you can edit the dynamic and static parameters in the SP file. To do so:

  1. Click Edit SP file parameters.
  2. In the Edit SP file parameters panel:
    1. Provide the following details:
      1. Connection credentials: Select one of the available options in the Credential type drop-down list to specify database credentials to connect to the Managed Database. For information on credential types, see Use Credentials to Perform Diagnostics & Management Tasks.
        Note

        The database user must have the ALTER SYSTEM privileges to edit parameters, and the SYSDBA role to edit static parameters.
      2. Parameters: Set the scope and edit the parameters.

        Select the Apply changes in SP file mode to the current running instances... check box to ensure that the changes made to the parameters in the SP file are applied to the current parameters running in the database instance: SCOPE=BOTH. If you deselect the check box, then the changes are only applied to the parameters in the SP file: SCOPE=SPFILE. Note that any changes made to static parameters only take effect after the server is restarted.

        You can filter the parameters or search for a particular parameter and edit the parameter value, add or edit comments, and reset values to default.

    2. Optionally, click Show SQL to view the SQL statement that will be executed.
    3. Click Save changes to save the changes made to the parameters.