User Community Service Desk Downloads

Oracle Lineage Scanner

Scanned and supported objects

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

Limitations

  • Not 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

Supported connectivity

Connector type: JDBC
Authentication method: Username and password

Server side permission settings

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];

Scanner configuration

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

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

Additional features

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

Was this page useful?