Oracle Cloud Infrastructure Documentation

Change Autonomous Database Operation Mode to Read/Write Read-Only or Restricted

You can select an Autonomous Database operation mode: Read/Write, Read-Only, or Restricted. The default mode is Read/Write.

Parent topic: Lifecycle Operations

Change Autonomous Database Operation Mode for a Session

You can set the Autonomous Database operation mode for a session to Read-Only. When you set the session mode to Read-Only users in the session can only run queries.

To enable Read-Only mode for a session:

  1. Connect to Autonomous Database.

    You can connect as the ADMIN user or as the user whose session you want to set to Read-only mode.

  2. Run the following SQL statement:
ALTER SESSION SET READ_ONLY = TRUE;

To disable Read-Only mode for a session:

ALTER SESSION SET READ_ONLY = FALSE;
Note

The READ_ONLY session parameter applies when the database is in Read/Write mode. If the database is in Read-Only mode, setting the session parameter value to TRUE or to FALSE does not change the operation mode for the session.

Change Autonomous Database Operation Mode to Read/Write Read-Only or Restricted

From the Oracle Cloud Infrastructure Console you can select an Autonomous Database operation mode: Read/Write, Read-Only, or Restricted. The default mode is Read/Write.

If you select Read-Only mode users can only run queries. In addition, for Read-Only or Read/Write mode, you can restrict access to only allow users with the RESTRICTED SESSION privilege to connect to the database. The ADMIN user has this privilege. You can use the restricted access mode to perform administrative tasks such as indexing, data loads, or other planned activities.

Perform the following prerequisite steps as necessary:

  • Open the Oracle Cloud Infrastructure Console by clicking the navigation icon next to Oracle Cloud.

  • From the Oracle Cloud Infrastructure left navigation menu click Oracle Database and then, depending on your workload click one of: Autonomous Data Warehouse, Autonomous JSON Database, or Autonomous Transaction Processing.

  • On the Autonomous Databases page select an Autonomous Database from the links under the Display name column.

To change the Autonomous Database operation mode, do the following:

  1. On the Details page, in the Mode field under General Information, click Edit.

    This shows the Change database mode dialog.

    Description of adb_mode_edit.png follows
  2. Select a mode, either Read/write or Read only.
  3. If you want to restrict access to only allow the ADMIN or privileged users, then select Allow administrator access only.
  4. Click Confirm.

    When concurrent operations such as scaling are active, the confirmation also confirms either pausing or canceling the concurrent operation. See Concurrent Operations on Autonomous Database for more information.

    While the mode changes, the Lifecycle State field shows Updating. After the mode change completes, the Lifecycle State field shows Available.

Notes for changing the database mode:

  • The database must be available with the Lifecycle State field showing Available to change the database mode.

  • When you change the database mode to restricted mode or when you change from restricted mode to unrestricted mode, the database terminates existing database connections as follows:

    • When the mode is restricted, that is the Mode field shows (Admin-only access), if you deselect Allow administrator access only and click Confirm, then users and applications need to reestablish database connections after the mode change completes.

    • When the mode is unrestricted, that is the Mode field does not show (Admin-only access), if you select Allow administrator access only and click Confirm, then users and applications need to reestablish database connections after the mode change completes.

Autonomous Database Operation in Read-Only Mode

When the database is in Read-Only mode, each of the following is disabled:

  • On the Oracle Cloud Infrastructure Console, the Administrator Password action is not allowed. Also, in Database Actions, under Administration, the Database Users card is disabled.

  • Oracle APEX URLs are not available:

    • In Database Actions, under Development, the APEX link is disabled.
    • On the Oracle Cloud Infrastructure Console Tools tab, the Open APEX link is disabled.

  • Oracle Machine Learning User Administration is disabled:

    • In Database Actions, under Development, the Oracle Machine Learning link is disabled.
    • On the Oracle Cloud Infrastructure Console Tools tab, the Open Oracle ML User Administration link is disabled.

  • Setting resource management rules is disabled. In Database Actions, under Administration, the Set Resource Management Rules link is disabled.

  • Upgrading the database is disabled when the database is in Read-Only mode.