Access to Resource Analytics ADW Database

Resource Analytics uses a regular Oracle Autonomous AI Database (ADW) database, and you can use and manage the database as you would any ADW database.

Resource Analytics deploys a set of database accounts and all tables and views are created in the OCIRA schema. Each of these accounts (names prefixed with "OCIRA") fulfill a specific role:

Database Details
Database Username	Role
OCIRA_ADM	Resource Analytics's internal admin account, used for patching and upgrading our database objects. It doesn't have ADMIN privileges on your database.
OCIRA	The main database schema for Resource Analytics objects. This schema owns all the tables and views that you use in your queries .
OCIRA_WORKER	The account used by our ingestion processes to load your resource data into Resource Analytics.
OCIRA_ANALYTICS	The account used in the associated Oracle Analytics Cloud (OAC) instance.
OCIRA_GRAPH	The account used in the associated Oracle Graph Studio instance.
OCIRA_GRAPH_ADMIN	This account is for your convenience to share graphs. This isn't a protected account. You can change the password and use it to create and share your own graph views.

Access to these accounts is protected and the database's own ADMIN account can't operate on these schemas.

To query the Resource Analytics views, use the OCIRA_RO database role that you can grant to your other database users.

To create a new user in ADW, see Create Users on Autonomous AI Database . Here's an example:

To query Resource Analytics objects, connect as ADMIN to your ADW and run this command:

grant OCIRA_RO to myuser;

The role doesn't work for ADMIN itself. It must be granted to a user other than an administration user, so you might need to create regular database users if you don't already have them in your ADW database. You can do that as you would in any Oracle database.

After the role is granted, you can connect to the database as your user and run queries on OCIRA views. For example:

select * from OCIRA.COMPARTMENT_DIM_V;

Here's an example of creating a new user in ADW and granting privileges to query Resource Analytics objects:

-- Create the user with a password
-- -- replae my_user with your own user name and replace my_password with a strong password.
CREATE USER my_user IDENTIFIED BY my_password;
GRANT UNLIMITED TABLESPACE to my_user;
 
-- Grant required privileges (example ..)
GRANT CREATE SESSION,
      CREATE TABLE,
      CREATE PROCEDURE,
      CREATE VIEW,
      CREATE TRIGGER,
      CREATE SEQUENCE,
      CREATE SYNONYM,
      CREATE JOB
TO my_user;
 
 
-- Now grant OCIRA_RO role to above user. This grants OCIRA data access to the user.
GRANT OCIRA_RO TO my_user;
 
-- (Optional) Grant additional roles if needed
-- Example: GRANT CONNECT, RESOURCE TO my_user;

To sign in through SQL Developer Web, follow the steps in this blog. In addition to the OCIRA_RO role, you must be granted the DWROLE or the REST ENABLED privilege directly. We recommend using the DWROLE as it includes REST access and other necessary privileges.

For example:

grant DWROLE to <my_user_name>;

Extending Oracle Resource Analytics

You can extend the functionality of your Resource Analytics environment by adding your own objects in your database schemas and then joining to Resource Analytics views.

For example, if you have a user, FINUSER, and a table, COST_CENTERS, you can list all OCI resources associated with specific cost centers if you tagged your resources appropriately:

select t.resource_id, t.resource_type from ocira.tags_dim_v t
join finuser.cost_centers cc on cc.cc_id = t.value
where t.tag_name = 'cost_center' and cc.cc_name = 'Project 1';

Oracle Cloud Infrastructure Documentation

Access to Resource Analytics ADW Database

Extending Oracle Resource Analytics