Oracle Cloud Infrastructure Documentation

Reference for All Other Views

This guide lists the predefined objects in Resource Analytics. You can find information about other views, entity relationships, subject areas, and sample queries.

Views

This section provides information about other views within Resource Analytics and their columns, data types, keys, and the referred view and column names. The following views are available:

Views in Resource Analytics
Name Description
AD_DIM_V This view stores information about availability domains, which are data centers located within a region.
REGION_DIM_V This view stores information about regions, which are localized geographic areas where Oracle Cloud Infrastructure is hosted.
DATE_DIM_V This view stores information on dates.
HOUR_DIM_V This view stores information on dates at an hourly grain.
TAGS_DIM_V This view stores information on the tags associated with each resource.
NETWORK_RESOURCE_MAP_V Denormalized view that stores information on the networking associated with compute instances, autonomous databases, network load balancers and their backends, and load balancers and their backends.
RESOURCE_DISCOVERY_V Denormalized view that contains details about all resources that have OCIDs.

The suffixes in the view names specify the view type:

  • DIM_V: Dimension
  • _V: Denormalized view

Relationship Diagrams

The contents of each view and their relationships are listed in the following file: other views.

Sample Queries

List the 3 letter region keys for all compute instances:
SELECT C.ID, C.AVAILABILITY_DOMAIN, A.REGION_KEY
FROM OCIRA.COMPUTE_INSTANCE_DIM_V C
LEFT JOIN OCIRA.AD_DIM_V A
ON C.AVAILABILITY_DOMAIN = A.NAME;
List the detailed region information for all load balancers:
SELECT L.ID, L.REGION, R.REGION_KEY, R.REGION_NAME, R.REGION_LOCATION, R.REALM_KEY
FROM OCIRA.LBAAS_LOAD_BALANCER_DIM_V L
LEFT JOIN OCIRA.REGION_DIM_V R
ON L.REGION = R.REGION_IDENTIFIER;
List the week start date that each autonomous database was last updated:
ELECT F.AUTONOMOUS_DATABASE_ID, F.OCIRA_UPDATE_DATE, H.WEEK_START
FROM OCIRA.DBAAS_AUTONOMOUS_DB_FACT_V F
LEFT JOIN OCIRA.HOUR_DIM_V H
ON F.TIME_KEY = H.TIME_KEY;
List all resources with tag 'XYZ':
SELECT RESOURCE_ID, RESOURCE_TYPE, TAG_KEY_NAME, TAG_VALUE
FROM OCIRA.TAGS_DIM_V
WHERE TAG_VALUE = 'XYZ';
List the number of compute instances created by each user:
SELECT TAG_VALUE AS CREATED_BY, COUNT(*)
FROM OCIRA.TAGS_DIM_V
WHERE RESOURCE_TYPE = 'instance'
AND TAG_NAMESPACE = 'Oracle-Tags'
AND TAG_KEY_NAME = 'CreatedBy'
GROUP BY TAG_VALUE;
List the VCN, subnet, and VNIC associated with a compute instance:
SELECT VCN_ID, SUBNET_ID, VNIC_ID
FROM OCIRA.NETWORK_RESOURCE_MAP_V
WHERE COMPUTE_INSTANCE_ID = <insert compute instance OCID>;
List all resources in region us-ashburn-1:
SELECT RESOURCE_NAME, RESOURCE_ID, RESOURCE_TYPE
FROM OCIRA.RESOURCE_DISCOVERY_V
WHERE REGION = 'us-ashburn-1';

Data Lineage

The Customer Experience Semantic Model Lineage spreadsheet and Metric Calculation Logic spreadsheet for Database provides an end-to-end data lineage summary report for physical and logical relationships in your data.

For more information, see Data Lineage.