Oracle Lineage Scanner

Scanned and supported objects

While we strive for comprehensive lineage capture, certain dataflows and transformations might be incomplete or unavailable due to technical constraints. We continuously work to expand coverage and accuracy.

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
`sourceType*`	Identifies the source type to be scanned. Must be set to `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.
`includeTables`	List of target table names to include in lineage extraction. See Target filter semantics.
`excludeTables`	List of target table names to exclude from lineage extraction. See Target filter semantics.
`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`.

sourceType*

Identifies the source type to be scanned. Must be set to 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.

includeTables

List of target table names to include in lineage extraction. See Target filter semantics.

excludeTables

List of target table names to exclude from lineage extraction. See Target filter semantics.

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.

Target filter semantics

The target filters (includeSchemas, excludeSchemas, includeTables, excludeTables) follow Oracle LIKE semantics and are case-insensitive.

The following rules apply:

Wildcards follow Oracle SQL LIKE semantics: % matches any sequence of characters, _ matches a single character.
Exclude filters have priority. If a target matches both an include and an exclude filter, the target is excluded.
A bare table name (for example, Table1) matches that table name across any included schema.
A fully-qualified name with a dot (for example, SchemaB.Table2) is split on the first dot and matches both schema and table parts.
Schema and table filters are combined with AND logic. A fully-qualified includeTables entry cannot re-include a schema that is excluded by excludeSchemas or absent from includeSchemas. For example, with includeSchemas: [SchemaA] and includeTables: [Table1, SchemaB.Table2], only SchemaA.Table1 is included.
Empty or whitespace-only filter values are ignored.
Target filters apply only to lineage targets (views and statement or procedure target tables). Source tables of a query are always included to ensure complete lineage capture.

Oracle scanner example configuration

{
   "scannerConfigs": [
      {
         "sourceType": "ORACLE",
         "description": "Scan Oracle using JDBC",
         "databaseNameInOne": "DEMO_DB",
         "includeSchemas": [],
         "excludeSchemas": ["DEV%", "TST%"],
         "includeTables": [],
         "excludeTables": ["%_backup", "%_old"],
         "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?