Oracle Lineage Scanner

The Oracle lineage scanner supports only metadata ingestion using JDBC:

Online (JDBC) extraction and configuration

Supported statement types and SQL syntax

Supported Statements Types:
- CREATE TABLE AS SELECT
- CREATE VIEW
- INSERT
- MERGE
- SELECT
- UPDATE
Supported Database Objects for Design Time Lineage:
- Views
- Materialized Views
- Lineage for PL/SQL code
  - procedures
  - functions
  - packages
Not yet supported SQL constructs:
- Statements containing these SQL constructs will not be included in the lineage diagram:
  - Table Functions
  - Database Links
  - Synonyms (in the next release)
  - CONNECT BY
  - MATCH RECOGNIZE
  - Multi column pivot/unpivot (Note: single column is supported)
Lineage is not supported for:
- Dynamic SQL
- Lineage for virtual columns

Note: Most of the unsupported syntax and Oracle artifacts are planned to be supported in the upcoming versions of the scanner, based on prioritization.

Database-Level Privileges

The scanner’s database user needs to be able to query these Data Dictionary views:

DBA_TABLES
DBA_VIEWS
DBA_MVIEWS
DBA_OBJECTS
DBA_TAB_COLS

The user must have the following privileges:

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. This role doesn’t grant us any access to the data in the database - only to performance & meta-data information..
```
GRANT SELECT_CATALOG_ROLE TO [YourUserOrRole];
```

Online (JDBC) extraction and configuration

Online (JDBC) based extraction connects to Oracle and extracts the necessary metadata.

Scope: Design time views and stored procedures.

Limitations: Currently, only username/password authentication is supported (other types are planned).

Property Description

Property	Description
`*name`	Unique name of the scanner job
`*sourceType`	Must contain ORACLE
`*description`	Human readable description
`oneConnections`	List of Ataccama ONE connection names for future automatic pairing
`*databaseNameInOne`	The corresponding Oracle database name on ATA ONE Catalog. Prerequisite for matching the imported lineage assets with ATA ONE catalog items
`*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". Value "ALL" restrict the lineage ingestion scope only for database objects that the scanner user can access (i.e.: views) or can execute (i.e.: procedures, packages) Default: DBA
`includeSchemas`	List of schemas to include in lineage extraction. The filter is case-insensitive and supports Oracle SQL wildcards: '%' and ''. I.e.: to include all schemas ending with "PROD", add filter "%PROD". The escape characters is '\'. I.e.: to include all schemas starting with "DEV", add 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. It should be set to true only in case of troubleshooting, testing or in specific POC use cases. Default: false
`skipViewsExtraction`	If set to true, views metadata is not extracted. Default: false
`skipProceduresExtraction`	If set to true, procedures/packages metadata is not extracted. Default: false

*name

Unique name of the scanner job

*sourceType

Must contain ORACLE

*description

Human readable description

oneConnections

List of Ataccama ONE connection names for future automatic pairing

*databaseNameInOne

The corresponding Oracle database name on ATA ONE Catalog. Prerequisite for matching the imported lineage assets with ATA ONE catalog items

*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". Value "ALL" restrict the lineage ingestion scope only for database objects that the scanner user can access (i.e.: views) or can execute (i.e.: procedures, packages) Default: DBA

includeSchemas

List of schemas to include in lineage extraction. The filter is case-insensitive and supports Oracle SQL wildcards: '%' and ''. I.e.: to include all schemas ending with "PROD", add filter "%PROD". The escape characters is '\'. I.e.: to include all schemas starting with "DEV", add 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. It should be set to true only in case of troubleshooting, testing or in specific POC use cases. Default: false

skipViewsExtraction

If set to true, views metadata is not extracted. Default: false

skipProceduresExtraction

If set to true, procedures/packages metadata is not extracted. Default: false

Legend: *mandatory

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]"
      }
    }
  ]
}

File-based extraction and configuration

File-based extraction is not yet available.

Scanner special features

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

Supported Oracle versions

All on-premises Oracle versions starting from Oracle 12c are supported. Cloud releases are also supported, provided the scanner authentication methods offer a sufficient level of security.

Was this page useful?