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

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

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?