Oracle Lineage Scanner

Scanned and supported objects

Supported statement types and SQL syntax

Supported statements types are as follows:

CREATE TABLE AS SELECT
CREATE VIEW
INSERT
MERGE
SELECT
UPDATE

Supported database objects for Design Time Lineage are as follows:

Views
Materialized views
Lineage for PL/SQL code
- Procedures
- Functions
- Packages

Limitations

The following SQL constructs are not supported:

This means that statements containing these SQL constructs will not be included in the lineage diagram.

Table functions
Database links
Synonyms
CONNECT BY
MATCH RECOGNIZE
Multi column pivot and unpivot (single column is supported)

Lineage is also not supported for:

Dynamic SQL
Virtual columns

Supported connectivity

Connector type: JDBC.
Authentication method: Username and password.

Oracle permissions

To ensure the scanner can access the necessary data, the following database permissions must be granted:

Database-level permissions

The user connecting to the scanner database needs to be able to query these Data Dictionary views:

DBA_TABLES
DBA_VIEWS
DBA_MVIEWS
DBA_OBJECTS
DBA_TAB_COLS

User permissions

The user must have the following permissions:

CONNECT: This role allows the user to connect to the database.
```
GRANT CONNECT TO [YourUserOrRole];
```
SELECT_CATALOG_ROLE: This role grants read only access to Data Dictionary (DBA_%) and Performance (V$%) views.

The role doesn’t grant any access to the data in the database, only to performance and metadata information.
```
GRANT SELECT_CATALOG_ROLE TO [YourUserOrRole];
```

Scanner configuration

All fields marked with an asterisk (*) are mandatory.

Property Description

Property	Description
`name*`	Unique name for the scanner job.
`sourceType*`	Specifies the source type to be scanned. Must contain `ORACLE`.
`description*`	A human-readable description of the scan.
`oneConnections`	List of Ataccama ONE connection names for future automatic pairing.
`databaseNameInOne*`	The corresponding Oracle database name in Ataccama ONE Catalog. A prerequisite for matching the imported lineage assets with catalog items in ONE.
`jdbcUrl*`	Full JDBC connection string.
`username*`	Oracle username.
`password*`	Oracle password. Can be encrypted.
`catalogScope*`	Defines the Oracle views that are used for lineage ingestion. Possible values: `ALL` and `DBA`. `ALL` restricts the lineage ingestion scope only to database objects that the scanner user can access (that is, views) or execute (such as procedures, packages). Default value: `DBA`.
`includeSchemas`	List of schemas to include in lineage extraction. The filter is case insensitive and supports Oracle SQL-like wildcards, such as the percent sign (`%`) and underscore (`_`). For example, to include all schemas ending with `PROD`, add the filter `%PROD`. The escape characters is a backslash (`\`). For example, to include all schemas starting with `DEV_`, add the filter `DEV\_%`.
`excludeSchemas`	List of schemas to exclude from lineage extraction. Similarly to `includeSchemas`, this filter is case insensitive and supports Oracle SQL wildcards.
`skipTableColumnsExtraction`	If set to `true`, data dictionary metadata is not extracted. Set it to `true` only for troubleshooting or testing purposes, or in specific proof-of-concept use cases. Default value: `false`.
`skipViewsExtraction`	If set to `true`, views metadata is not extracted. Default value: `false`.
`skipProceduresExtraction`	If set to `true`, procedures and packages metadata is not extracted. Default value: `false`.

name*

Unique name for the scanner job.

sourceType*

Specifies the source type to be scanned. Must contain ORACLE.

description*

A human-readable description of the scan.

oneConnections

List of Ataccama ONE connection names for future automatic pairing.

databaseNameInOne*

The corresponding Oracle database name in Ataccama ONE Catalog. A prerequisite for matching the imported lineage assets with catalog items in ONE.

jdbcUrl*

Full JDBC connection string.

username*

Oracle username.

password*

Oracle password. Can be encrypted.

catalogScope*

Defines the Oracle views that are used for lineage ingestion. Possible values: ALL and DBA. ALL restricts the lineage ingestion scope only to database objects that the scanner user can access (that is, views) or execute (such as procedures, packages).

Default value: DBA.

includeSchemas

List of schemas to include in lineage extraction. The filter is case insensitive and supports Oracle SQL-like wildcards, such as the percent sign (%) and underscore (_). For example, to include all schemas ending with PROD, add the filter %PROD.

The escape characters is a backslash (\). For example, to include all schemas starting with DEV_, add the filter DEV\_%.

excludeSchemas

List of schemas to exclude from lineage extraction. Similarly to includeSchemas, this filter is case insensitive and supports Oracle SQL wildcards.

skipTableColumnsExtraction

If set to true, data dictionary metadata is not extracted. Set it to true only for troubleshooting or testing purposes, or in specific proof-of-concept use cases.

Default value: false.

skipViewsExtraction

If set to true, views metadata is not extracted.

Default value: false.

skipProceduresExtraction

If set to true, procedures and packages metadata is not extracted.

Default value: false.

Oracle scanner example configuration

{
   "scannerConfigs": [
      {
         "name": "oracle-lineage-poc",
         "sourceType": "ORACLE",
         "description": "Scan Oracle using JDBC",
         "databaseNameInOne": "DEMO_DB",
         "includeSchemas": [],
         "excludeSchemas": ["DEV%", "TST%"],
         "catalogScope": "DBA",
         "connection": {
            "jdbcUrl": "jdbc:oracle:thin:@localhost:1521:xe",
            "username": "ATA_LINEAGE_EXTRACTION_USER",
            "password": "@@ref:ata:[ORACLE_PASSWORD]"
         }
      }
   ]
}

Additional features

The Oracle scanner supports the same SQL anomaly detection checks as the Snowflake scanner. See SQL DQ (anomaly) detection for more details.

Was this page useful?