Set up your Autonomous Database to use Graph Studio

6 min readJun 9, 2022

In this article, we will Provision an Autonomous Database, and configure a graph enabled user. This will allow you to log into Graph Studio, which is built into the Autonomous Database web console.

Provision Autonomous Database

1. Log in to Oracle Cloud.

2. Once you are logged in, you are taken to the cloud services dashboard where you can see all the services available to you. Click the navigation menu in the upper left corner to show top level navigation choices.
Note: You can also directly access your Oracle Autonomous Data Warehouse or Oracle Autonomous Transaction Processing service in the Quick Actions section of the dashboard.

3. The following steps apply similarly to either Oracle Autonomous Data Warehouse or Oracle Autonomous Transaction Processing. This lab shows provisioning of an Oracle Autonomous Data Warehouse database, so click Autonomous Data Warehouse.

4. Make sure your workload type is Data Warehouse or All to see your Oracle Autonomous Data Warehouse instances. Use the List Scope drop-down list to select a compartment.
Note: Avoid the use of the ManagedCompartmentforPaaS compartment as this is an Oracle default used for Oracle Platform Services.

5. Click Create Autonomous Database to start the instance creation process.

6. Provide basic information for the autonomous database:

Choose a compartment — Select a compartment for the database from the drop-down list.
Display Name — Enter a memorable name for the database for display purposes.
Database Name — Use letters and numbers only, starting with a letter. Maximum length is 14 characters. (Underscores not initially supported.)

7. Choose a workload type. Select the workload type for your database from the choices:

Data Warehouse — For this lab, choose Data Warehouse as the workload type.
Transaction Processing — Alternatively, you could have chosen Transaction Processing as the workload type.

8. Choose a deployment type. Select the deployment type for your database from the choices:

Shared Infrastructure — For this lab, choose Shared Infrastructure as the deployment type.
Dedicated Infrastructure — Alternatively, you could have chosen Dedicated Infrastructure as the deployment type.

9. Configure the database:

Note: This example is not compatible with the Always Free Autonomous Database.

Choose database version — Select a database version from the available versions.
OCPU count — Number of CPUs for your service.
Storage (TB) — Select your storage capacity in terabytes.
Auto Scaling — Check this box to allow the system to automatically use up to three times more CPU and IO resources to meet workload demand.
New Database Preview — If a checkbox is available to preview a new database version, do NOT select it.

10. Create administrator credentials:

Password and Confirm Password: Specify the password for ADMIN user of the service instance.

The password must meet the following requirements:

The password must be between 12 and 30 characters long and must include at least one uppercase letter, one lowercase letter, and one numeric character.
The password cannot contain the username.
The password cannot contain the double quote (“) character.
The password must be different from the last 4 passwords used.
The password must not be the same one set less than 24 hours ago.
Re-enter the password to confirm it. Make a note of this password

11. Choose network access. For this lab, Select Secure access from allowed IPs and VCNs only, and select Add My IP Address for now. Uncheck the box that says Require mutual TLS authentication.

12. Choose a license type.

License Included: The cost of the database set at the normal rate.
Bring Your Own License (BYOL): For customers with existing Oracle licenses that you are migrating to the cloud. You pay a reduced price for what you use, but additionally continue paying the support stream.

For this lab, I will choose License Included.

13. Your instance will begin provisioning. In a few minutes, the state will turn from Provisioning to Available. At this point, your Oracle Autonomous Data Warehouse database is ready to use.

Create Graph User

Working with Graphs in Graph Studio requires users with granted roles. You can create Graph users with the correct set of roles and privileges using Oracle Database Actions.

Access Database Actions from the Oracle Cloud Infrastructure Console as the ADMIN user

2. Under Administration from the Database Actions Menu, click Database Users.

3. Click Create User on the Database Users page, in the All Users area.

4. Enter a user name (ex. GRAPHUSER, password and enter the password again to confirm the password.

5. Switch on the Graph Enable toggle button for the user.
The GRAPH_DEVELOPER role gets automatically assigned to the user.

6. Switch on the Web Access toggle button to provide the new user access to web console in Autonomous Database

7. Enter a quota value on tablespace. For this example, I recommend setting unlimited tablespace.

8. Click Create User.

Log In to Graph Studio

From your OCI console, navigate to your Autonomous Database Instance, and select Tools > Graph Studio.

2. Log in as your graph user, and you are ready to start making graphs! You can follow this blog to get started with a sample notebook.

Troubleshooting

If you see an `unauthorized error` when trying to log into Graph Studio, follow these steps

Navigate to your Autonomous Database and access Database Actions as the ADMIN user.
Select SQL Query.
Run the following statements:

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   role_exists EXCEPTION;
   PRAGMA EXCEPTION_INIT(role_exists, -01921);
   TYPE graph_roles_table IS TABLE OF VARCHAR2(50);
   graph_roles graph_roles_table;
 BEGIN
   graph_roles := graph_roles_table(
     'GRAPH_DEVELOPER',
     'GRAPH_ADMINISTRATOR',
     'PGX_SESSION_CREATE',
     'PGX_SERVER_GET_INFO',
     'PGX_SERVER_MANAGE',
     'PGX_SESSION_READ_MODEL',
     'PGX_SESSION_MODIFY_MODEL',
     'PGX_SESSION_NEW_GRAPH',
     'PGX_SESSION_GET_PUBLISHED_GRAPH',
     'PGX_SESSION_COMPILE_ALGORITHM',
     'PGX_SESSION_ADD_PUBLISHED_GRAPH');
   FOR elem IN 1 .. graph_roles.count LOOP
   BEGIN
     dbms_output.put_line('create_graph_roles: ' || elem || ': CREATE ROLE ' || graph_roles(elem));
     EXECUTE IMMEDIATE 'CREATE ROLE ' || graph_roles(elem);
   EXCEPTION
     WHEN role_exists THEN
       dbms_output.put_line('create_graph_roles: role already exists. continue');
     WHEN OTHERS THEN
       RAISE;
     END;
   END LOOP;
 EXCEPTION
   when others then
     dbms_output.put_line('create_graph_roles: hit error ');
     raise;
END;
/GRANT PGX_SESSION_CREATE TO GRAPHUSER;
GRANT PGX_SERVER_GET_INFO TO GRAPHUSER;
GRANT PGX_SERVER_MANAGE TO GRAPHUSER;
GRANT PGX_SESSION_CREATE TO GRAPHUSER;
GRANT PGX_SESSION_NEW_GRAPH TO GRAPHUSER;
GRANT PGX_SESSION_GET_PUBLISHED_GRAPH TO GRAPHUSER;
GRANT PGX_SESSION_MODIFY_MODEL TO GRAPHUSER;
GRANT PGX_SESSION_READ_MODEL TO GRAPHUSER;

4. Log in again in a fresh window.