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
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 |
|
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.
- Login to the OvalEdge application
- Navigate to Administration > Connector module.
- Click on the + icon, and the Add Connection with Search Connector pop-up window is displayed.
- Select the connection type as Oracle. The Manage Connection with Oracle connector specific details pop-up window is displayed.
- 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:ORCLPlugin 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
- 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.
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.
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.
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).
Note: A profile type set to “Auto” will always depend on the Rowcount limit and the Rowcount Constraint (Set as “True”).
|
|
Row Count Constraint |
The Row Count Constraint option is applicable only when the Profile Type is selected as Auto.
|
|
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
FAQs
- How much does the driver cost?
The JDBC Driver for Oracle is available at no additional charge. - 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. - 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. - 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.