Oracle Connector

Connectivity Summary

An out of the box connector is available for Oracle databases. It provides support for crawling database objects, profiling of sample data and lineage building.

Connectivity Summary

10-2

The drivers used by the Connector are:

Driver/API

Version

Details

JDBC Driver

19.3.0.0

https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8

Note : Latest version is 21.1.0.0

Technical Specifications

The Connector capabilities are shown below:

Crawling

Feature

Supported Objects

Remarks

Crawling

Tables

-

Table Columns

Supported Data Types:

CHAR,VARCHAR, VARCHAR2, CHAR, LONG, NUMBER, DATE

 

Views

-

 

Stored Procedures

-

 

Functions

Functions will not be fetched if the additional crawl is checked.

 

Triggers

-

 

Roles

-

 

Users

-

 

Permissions

Check this option to query sql in the query sheet.

 

Additional Crawl

  • Check this to add tables and view accessible to DBA_ views
  • Filters the Temporary tables and columns (names contains $)
  • Functions will not be fetched

Profiling

 

Feature

Support

Remarks

Table Profiling

Row count, Columns count, View sample data

-

View Profiling

Row count, Columns count, View sample data

View is treated as a Table for profiling purposes.

Column Profiling

Min, Max, Null count, distinct, top 50 values

-

Full Profiling 

Supported

-

Sample Profiling

Supported

-

Lineage Building

Feature

Remarks

Table lineage

Supported

Column lineage

Supported

Lineage Sources

Stored procedures, functions, triggers, views, SQL Queries (from Query Sheet), Query Logs

Querying 

Operation 

Remarks

Select

Supported

Insert

Not supported, by default.

Update

Not supported, by default.

Delete

Not supported, by default.

Joins within database

Supported

Joins outside database

Not supported

Aggregations

Supported

Group By

Supported

Order By

Supported

By default the service account provided for the connector will be used for any query operations. If the user account has write privileges, then Insert / Update / Delete queries can be executed.

Pre-requisites

To use the connector, the following need to be available:

  • Connection details as specified in the following section should be available.
  • An User account with read privileges.
  • JDBC driver is provided by default. In case it needs to be changed, add Oracle drivers into the OvalEdge Jar path to communicate with the Oracle database. 

Check the Configuration section for further details on how to add the drivers to the jar path.

Operation 

Access Permission

Connection Validation

Select

Crawl Schemas

Select

Crawl Tables

Select

Profile Schemas, Tables

Select

Connection Details

To connect to the Oracle connector using the OvalEdge application, complete the following steps.

  1. Login to the OvalEdge application
  2. Navigate to Administration > Connector module.
  3. Click on the + icon, and the Add Connection with Search Connector pop-up window is displayed.
  4. Select the connection type as Oracle. The Manage Connection with Oracle connector specific details pop-up window is displayed.
    11-3
  5. The fields in the connector form are explained below:

    Field Name

    Mandatory/Optional

    Description

    Connector Type

    Mandatory

    By default, the selected connection type is displayed as Oracle.

    Credential Manager

    Optional

    Select the option from the drop-down menu, where you want to save your credentials.

    License Type

    Mandatory

    Select the license type as Standard/Auto Lineage.

    Connection Name

    Mandatory

    Select a Connection name for the Oracle database. The name that you specify is a reference name to easily identify your Oracle database connection in OvalEdge. Example: Oracle Connection DB1

    Environment

    Optional

    Enter the environment details.

    Server

    Mandatory

    Enter the fully qualified server name or IP address related to the Oracle server. 

    Example: 12.34.56.78

    Port Number

    Mandatory

    By default, the port number related to the Oracle server is displayed. If needed, the port number can be modified.(1521)

    Database 

    Mandatory

    Name of the database to connect.

    Database Type

    Optional

    Enter the name of the database which users want to crawl.

    Driver

    Mandatory

    The driver details associated with the Oracle server are displayed, and it is not editable. Example: oracle.jdbc.driver.OracleDriver

    Username

    Mandatory

    By default, the user credentials through which the OvalEdge application is logged in are displayed in the Username textbox related to the Oracle authentication. 

    Password

    Mandatory

    By default, the user credentials through which the OvalEdge application is logged in are displayed in the Password textbox.

    Connection String

    Optional

    Oracle connection string. Set the Connection string toggle button to automatic, to get the details automatically from the credentials provided. Alternatively, you can manually enter the string.
    Format: jdbc:oracle:thin:@{server}:1521:{sid}
    Example: jdbc:oracle:thin:@ovaledge.csklygkwz3dx.us-east-1.rds.amazonaws.com:1521:ORCL

    Plugin Server

    Optional

    Enter the Server Name if the connection is running as the plugin

    Example: ovaledgeoracle.csklygkw3.dxuseast-1rds.amazonnows.com

    Plugin Port

    Optional

    Enter the port number on which the plugin is running.

    Example: 3306

    Default Governance Roles

    Mandatory

    From the dropdown list, select Stewards, Custodian and Owner.

    No of archive objects

    Mandatory

    By default, the number of archive objects is set in the disable mode. To enable the archive option click on the Archive toggle button and enter the number of objects to be archived.

    Select Bridge

    Optional 

    Select option NO Bridge if no bridge is available for connector 

  6. Once after entering the connection details in the required fields, Click on the Save button or Save & Configure to establish the connection and configure the connection settings. Here when you click on the Save & Configure button, the Connection Settings pop-up window is displayed. Where you can configure the connection settings for the selected Connector.

Note: The Save & Configure button is displayed only for the Connectors for which the settings configuration is required.

 7. Click on the Validate button to validate the connection details.

Note: It is up to the user's wish, you can save the connection details first, or you can validate the connection first and then save it. 

Connector Settings

Once establishing the connection successfully the additional configurations for crawling need to be specified. To configure the Crawler settings for the Oracle Connector, select the Oracle Connection Name from the Crawler Information page and click on the 9 dots buttons and select the Settings options. The Connection Settings pop-up window is displayed. 

12-2

Crawler Settings

Crawler Configurations Settings

Details

Tables, Views, and Columns

Select the checkbox to crawl the tables, views, and columns existing in the Oracle database into the OvalEdge.

Note: By default, the checkbox for Tables, Views, and Columns is selected.

Procedures, Functions, Triggers & Views Source Code

The crawling will discover the procedures, functions, and views source code within the schemas selected and bring them into OvalEdge. 

Relationship

Select the checkbox to determine relationships between the tables for the selected schemas existing in the Oracle database into the OvalEdge.

Additional Crawl

The purpose of the additional crawl allows the Database administrator to fetch the metadata from the schema called DBA_VIEWS, USER_VIEW, and ALL_VIEWS. It also excludes temporary tables while crawling.

Crawler Rules: Include Regex

Enter the specific schema, table, views, and column names that start with, end with, or have middle characters that are included for crawling.

Crawler Rules: Exclude Regex

Enter the specific schema, table, views, and column names that start with, end with, or have middle characters that are excluded for crawling.

Profiler Settings

Profiler Configurations Settings

Details

Tables and Columns

Select the checkbox to profile the tables and columns that are existing in the Oracle database into the OvalEdge.

Note: By default, the checkbox for Tables and Columns is selected.

Views and Columns

Select the checkbox to profile the views and columns that are existing in the Oracle database into the OvalEdge.

Note: By default, the checkbox for Views and Columns is selected.

Profile Rules: Include Regex

Enter the specific table, view, and column names that start with, end with, or have middle characters that are included for profiling.

Profile Rules: Exclude Regex

Enter the specific table, view, and column names that start with, end with, or have middle characters that are excluded for profiling.


To configure the Profile Setting:

Click on the Edit icon that allows the Admin user to configure the profiler setting for the selected data source. There are many attributes you can specify in the profile settings. 

13-3

The attributes are as follows:

Columns

Description

Order

Order is the sequence in which the profiling is done. 

Day

Enter the day of the week profiling is set to run.

Start/End Time

Enter the start and end time at which profiling is set to perform.

Number of Threads

Thread is a process where a query is executed on a database to do single or multiple tasks. The number of threads determines the number of parallel queries executed on the data source. 

Profile Type

There are four main types of data profiling. 

  • Sample - For Sample profiling, 

The profiling is performed based on a given Sample Profile Size. The data on columns (like Min, Max, Distinct, Null Count, etc.) will be different compared to full profiles as we calculate them only on sample size. To execute a sample profile, select the profile type as “Sample” and enter a sample profile size(count of records to be profiled).

  • Auto - For Auto profiling,
    • If the Row Constraint checkbox is selected (Set as True) and if the configured Rowcount Limit (100) is less than the total Table Row Count (1000) then the sample profiling is performed by considering the count mentioned in the Sample Profile Size.
    • If the Row Constraint checkbox is selected (Set as True) and if the configured Rowcount Limit (1000) is greater than the total Table Row Count (100) then the query is executed without considering the count mentioned in the Rowcount Limit. 

Note:  A profile type set to “Auto” will always depend on the Rowcount limit and the Rowcount Constraint (Set as “True”). 

  • Query - For Query profiling,
    • If the Table Row Count (100) is less than the configured Rowcount Limit (1000), then the profiling is executed on the entire table.
    • If the Table Row Count (1000) exceeds the configured Rowcount Limit (100), then the profiling skips execution for those tables to avoid performance issues.
  • Disabled profile type prevents profiling on the selected data source. 

Row Count Constraint

The Row Count Constraint option is applicable only when the Profile Type is selected as Auto.

  • If the Row Constraint checkbox is selected (Set as True) and if the configured Rowcount Limit (100) is less than the total Table Row Count (1000) then the sample profiling is performed by considering the count mentioned in the Sample Profile Size.
  • If the Row Constraint checkbox is selected (Set as True) and if the configured Rowcount Limit (1000) is greater than the total Table Row Count (100) then the query is executed without considering the count mentioned in the Rowcount Limit. 

Row Count Limit

Enter the maximum number of rows that should be considered for profiling.

Sample Profile Size

Enter the total number of rows to be included in profiling.

Query Timeout:

Enter the length of time in seconds to allow the query to run on a remote database before timing out. 


Data Access Authorization 

Remote Access (Data Access Authorization) is included in the crawler-specific connector settings to ensure that the right user is accessing the query sheet and queries in the data catalog. Here the system validates the user credentials and allows that particular user to access the query sheet and queries in the data catalog.

  • OvalEdge follows OvalEdge data permissions: When this option is selected, the OvalEdge application uses a service account used for creating the connection in the Query Sheet page, Data Catalog > Table > Data page and Data Catalog > Query page to fetch the required data.
  • OvalEdge follows Remote system permissions: When this option is selected, then the users are prompted to enter the remote data source credentials in the Query Sheet page, Data Catalog > Table > Data page, and Data Catalog > Query page, and the submitted credentials are used to establish the connection to fetch the required data. Here, these credentials will be cached and cleared after 7 days of inactivity.

Example: When the Remote system permissions option is selected and the user navigates to the Query Sheet or Data Catalog Queries and selects the Connection (Database), then the Enter User Credentials pop-up window with the Username and Password is displayed, where the user needs to provide the remote data source credentials for accessing the Database.

Query Policies

The Query Policies is included in the crawler-specific connector settings for the connectors, which are supported with Query Sheet functionality. Here the system allows you under the specific role to restrict the usage of the selected query types in the Query Sheet page for fetching the required data.

Access Instruction

It allows the Crawler admin to write the instructions and guide the user to crawl the data source.

  • You can provide the instruction in Crawler > Setting page 
  • Click the Access Instruction tab
  • Enter the instructions 
Click the Save Changes button. Once you add the access instruction for a specific connection in the crawler settings, it will appear in the connection hierarchy like a database.

FAQs

  1. How much does the driver cost?
    The JDBC Driver for Oracle  is available at no additional charge.
  2. What should I do when we get an exception in logs like “Got minus one” ?
    The server you are trying to communicate with is busy with many users and operations running. Give some time and try it again. If you face the same issue, check all users and try to restart the server.
  3. Can we profile system tables or overflow tables?
    System tables or overflow tables with columns which have unconventional data types cannot be profiled. User permission on the schemas should also be checked before profiling.
  4. What are the minimum permissions required for crawling Oracle Database?
    The user must have User Privileges ( can have all and dba privileges also) to connect to the oracle database. A minimum of read access is required on the particular schema which needs to be crawled/ profiled.