Using Oracle SQL Developer Web in Database Classic Cloud Service

When you create an Oracle Database Classic Cloud Service database deployment of a single-instance database, Oracle SQL Developer Web is installed for you.

SQL Developer Web is a web interface for Oracle SQL Developer and provides a subset of the features of the desktop version. It enables you to run SQL statements and scripts in the worksheet, export data, and design Data Modeler diagrams using new and existing objects. It also enables database administrators to monitor and manage the database and provides a real time SQL monitoring interface.

Access to SQL Developer Web is provided through schema-based authentication. Consequently, you can provide database developers and administrators access to databases without having to create and maintain Oracle Cloud accounts for them. However, before a developer or administrator can sign in to SQL Developer Web, you must enable their schema in the database for access.

Topics

Enabling a Schema for SQL Developer Web

When using Oracle SQL Developer Web in an Oracle Database Classic Cloud Service database deployment, you sign in as a database user. Before you can do so, however, you must enable the database user’s schema for SQL Developer Web.

Note:

You do not need to enable the PDBADMIN user’s schema in the PDB created when a database deployment of Oracle Database 12.1 or later is created. Database Classic Cloud Service automatically enables this schema for SQL Developer Web, including its DBA features.

Before you begin

Before attempting to enable a database user’s schema for SQL Developer Web, you need to make sure the database deployment is using cloud tooling version 18.2.3 or later. For instructions on checking and updating the cloud tooling, see Updating the Cloud Tooling by Using the dbaascli Utility.

Procedure

Connect as the opc user to the deployment’s compute node.

For detailed instructions, see Connecting to a Compute Node Through Secure Shell (SSH).
Start a root-user command shell:
```
$ sudo -s
#
```
Create a text file containing the password of the user whose schema you want to enable.
1. Create the file:
```
# touch /home/oracle/password.txt
```
2. Restrict permissions on the file:
```
# chmod 600 /home/oracle/password.txt
```
3. Use a text editor (such as vim) to enter the password in the file. The file should consist of a single line containing the password without any whitespace characters.
Use the ords assistant to enable the schema.
1. Go to the directory containing the ords assistant:
```
# cd /var/opt/oracle/ocde/assistants/ords
```
2. Run the ords assistant to enable the schema.
  
  For a schema in Oracle Database 11g, enter a command of this form:
```
# ./ords -ords_action="enable_schema_for_sdw" \
-ords_sdw_schema="schema-name" \
-ords_sdw_schema_password="/home/oracle/password.txt" \
-ords_sdw_schema_enable_dba="dba-boolean"
```
  For a schema in a PDB (pluggable database) in Oracle Database 12.1 or later, enter a command of this form:
```
# ./ords -ords_action="enable_schema_for_sdw" \
-ords_sdw_schema="schema-name" \
-ords_sdw_schema_password="/home/oracle/password.txt" \
-ords_sdw_schema_container="pdb-name" \
-ords_sdw_schema_enable_dba="dba-boolean"
```
  In these command forms:
  - schema-name is the name of the schema you want to enable. If it doesn’t exist, it will be created.
  - dba-boolean is TRUE or FALSE. If you enter TRUE, the schema will be enabled to support the DBA (database administrator) features of SQL Developer Web.
  - pdb-name is the name of the pluggable database (PDB) containing the schema you want to enable.

After the ords assistant finishes, take note of the sign-in information it provides. For example:

# ./ords -ords_action="enable_schema_for_sdw" \
-ords_sdw_schema="SDW" \
-ords_sdw_schema_password="/home/oracle/password.txt" \
-ords_sdw_schema_container="PDB1" \
-ords_sdw_schema_enable_dba="TRUE"

INFO:  To access SQL Developer Web through DBCS Landing Page, the schema "PDB1/sdw" needs to be provided.
INFO: "SDW" schema in the "PDB1" container for SQL Developer Web was enabled successfully.
#

Exit the root-user command shell and disconnect from the compute node:
```
# exit
$ exit
```

Accessing SQL Developer Web

You can access Oracle SQL Developer Web in an Oracle Database Classic Cloud Service database deployment in the following ways:

Tip:

The ways to access SQL Developer Web require you to provide the schema path reported by the ords assistant when the database user’s schema was enabled for SQL Developer Web access. If you’ve forgotten this value, use these guidelines to determine it:

For a schema in Oracle Database 11g:

The schema path is the schema name with all letters lowercase and special characters changed to underscores. Multiple special characters in a row are changed to a single underscore. For example, the schema path for the C##CORPDBA1 schema is c_corpdba1.
For a schema in a PDB of Oracle Database 12c or later:

The schema path is the pdb name, a slash (/), and the schema name with all letters lowercase and special characters changed to underscores. Multiple special characters in a row are changed to a single underscore. For example, the schema path for the ORGDBA1 schema in the HRORG PDB is hrorg/orgdba1.

Using the Database Deployment’s Landing Page

Note:

For database deployments built on Oracle Cloud Infrastructure Classic, the network port to access the deployment’s landing page is blocked by default. To use the deployment’s landing page, you must unblock port 443, either by enabling the deployment’s ora_p2_httpssl predefined access rule or by creating your own access rule that opens port 443. For instructions, see Enabling Access to a Compute Node Port.

In your web browser, go to the following URL:
```
https://node-ip-address/
```
where node-ip-address is the IP address of the deployment’s compute node as listed on the deployment’s Overview page.
If your browser displays a warning that your connection is not secure or not private, use the browser’s advanced option to continue.

You get this warning because Database Classic Cloud Service database deployments use a self-signed certificate to provide HTTPS (secure HTTP) connectivity, and such certificates are considered suspicious by many web browsers.
In the SQL Developer box’s Schema field, enter the schema path reported by the ords assistant when the database user’s schema was enabled for SQL Developer Web access. Then click Go.
When prompted for a username and password, enter the user name and password of the database user whose schema path you entered in the previous step. Make sure to enter the user name in all-uppercase. Then click Sign In.
If the user’s schema was enabled to support the DBA features of SQL Developer Web, the Database Cloud Service Dashboard page is displayed. Otherwise, the SQL Developer Home page is displayed.

Using a Direct URL

Note:

For database deployments built on Oracle Cloud Infrastructure Classic, the network port to access SQL Developer Web is blocked by default. To use a direct URL, you must unblock port 443, either by enabling the deployment’s ora_p2_httpssl predefined access rule or by creating your own access rule that opens port 443. For instructions, see Enabling Access to a Compute Node Port.

In your web browser, go to the following URL:
```
https://node-ip-address/ords/schema-path/_sdw
```
where node-ip-address is the IP address of the deployment’s compute node as listed on the deployment’s Overview page, and schema-path is the schema path reported by the ords assistant when the schema was enabled for SQL Developer Web access.
If your browser displays a warning that your connection is not secure or not private, use the browser’s advanced option to continue.

You get this warning because Database Classic Cloud Service database deployments use a self-signed certificate to provide HTTPS (secure HTTP) connectivity, and such certificates are considered suspicious by many web browsers.
When prompted for a username and password, enter the user name and password of the database user whose schema-path you gave in the URL. Make sure to enter the user name in all-uppercase. Then click Sign In.
If the user’s schema was enabled to support the DBA features of SQL Developer Web, the Database Cloud Service Dashboard page is displayed. Otherwise, the SQL Developer Home page is displayed.

Using an SSH Tunnel

Create an SSH tunnel to port 443 on the compute node hosting SQL Developer Web. For information about creating an SSH tunnel, see Creating an SSH Tunnel to a Compute Node Port.
After creating the SSH tunnel, direct your browser to the following URL:
```
https://localhost/ords/schema-path/_sdw
```
where schema-path is the schema path reported by the ords assistant when the schema was enabled for SQL Developer Web access.
When prompted for a username and password, enter the user name and password of the database user whose schema-path you gave in the URL. Make sure to enter the user name in all-uppercase. Then click Sign In.
If the user’s schema was enabled to support the DBA features of SQL Developer Web, the Database Cloud Service Dashboard page is displayed. Otherwise, the SQL Developer Home page is displayed.

Features of SQL Developer Web

Oracle SQL Developer Web provides a rich set of developer and administrator features for a database, a CDB (container database) or a PDB (pluggable database).

Developer Features

Users of the Oracle SQL Developer desktop product will find SQL Developer Web very familiar and easy-to-use. It provides similar Worksheet and Data Modeler features, and includes a Home page that provides pertinent database statistics and access to the worksheets and diagrams you have saved. For more information, see these topics in Using Oracle SQL Developer Web:

Administrator Features

For users whose schemas have been enabled for DBA access, SQL Developer Web provides features for database and OS administration and monitoring:

A dashboard that shows overview statistics for the database. This dashboard is displayed immediately after you sign in, and many of the statistics it displays are “hot”: you can click them to go directly to detail pages where you can investigate and take appropriate action. Additionally, the dashboard provides quick links to such resources as the Worksheet, Data Modeler and SQL Monitor.
DBA features to manage the database and see information about the listener, backups, alerts and several other items of interest to DBAs. For more information, see Using DBA Features in Using Oracle SQL Developer Web.
OS features to monitor RAM and CPU usage, OS storage and running processes. For more information, see Monitoring OS in Using Oracle SQL Developer Web.