Oracle¶
Required Information¶
To configure Oracle in Alation, you will need the following information:
Hostname or IP address of your server
Port number - default is 1521
Service name
Kerberos information (if using Kerberos)
Service account
Query Log Ingestion setup: Custom View

Synonym Extraction¶
Applies from release V R7 (5.12.x)
For Oracle sources, Alation can extract both private and public synonyms if synonyms extraction is enabled in Labs/Feature Configuration. All extracted public synonyms can be located under the schema called PUBLIC on the catalog page of your data source. This schema is used in Alation to only surface the synonym object pages to users - it is created by Alation and is not present in the database itself.
Private synonyms will be extracted together with their respective schemas if these schemas are extracted.
Preliminaries¶
Firewall Configuration¶
Open outbound TCP port 1521 to Oracle server
DNS Record (if using Kerberos authentication)
Oracle Kerberos authentication requires Alation server hostname to be resolvable. Add a DNS record for Alation server or edit /etc/hosts file on the Alation server.
Create Service Account¶
Sample SQL to create an account
CREATE USER alation IDENTIFIED BY [password];
GRANT CREATE SESSION TO alation;
Metadata Extraction¶
GRANT SELECT ON DBA_OBJECTS TO alation;
GRANT SELECT ON DBA_TAB_COLUMNS TO alation;
GRANT SELECT ON DBA_CONS_COLUMNS TO alation;
GRANT SELECT ON DBA_CONSTRAINTS TO alation;
GRANT SELECT ON DBA_VIEWS TO alation;
GRANT SELECT ON DBA_IND_COLUMNS TO alation;
GRANT SELECT ON DBA_INDEXES TO alation;
GRANT SELECT ON DBA_SYNONYMS TO alation;
GRANT SELECT ON DBA_COL_COMMENTS TO alation;
GRANT SELECT ON DBA_MVIEW_COMMENTS TO alation;
GRANT SELECT ON DBA_TAB_COMMENTS TO alation;
GRANT SELECT ON DBA_SEGMENTS TO alation;
GRANT SELECT ON DBA_LOBS TO alation;
Permission |
Purpose |
---|---|
GRANT SELECT ON DBA_OBJECTS TO alation; |
Required for table extraction. |
GRANT SELECT ON DBA_TAB_COLUMNS TO alation; |
Required for table and column extraction. |
GRANT SELECT ON DBA_CONS_COLUMNS TO alation; |
Required for column extraction. |
GRANT SELECT ON DBA_CONSTRAINTS TO alation; |
Required for primary key and foreign key extraction. |
GRANT SELECT ON DBA_VIEWS TO alation; |
Required for view extraction. |
GRANT SELECT ON DBA_IND_COLUMNS TO alation; |
Required for index extraction. |
GRANT SELECT ON DBA_INDEXES TO alation; |
Required for index extraction. |
GRANT SELECT ON DBA_SYNONYMS TO alation; |
Required for synonym extraction. |
GRANT SELECT ON DBA_COL_COMMENTS TO alation; |
Required for source comments extraction. |
GRANT SELECT ON DBA_MVIEW_COMMENTS TO alation; |
Required for view comments extraction. |
GRANT SELECT ON DBA_TAB_COMMENTS TO alation; |
Required for table comments extraction. |
GRANT SELECT ON DBA_SEGMENTS TO alation; |
Required for table iteration. |
GRANT SELECT ON DBA_LOBS TO alation; |
Required for table extraction. |
Table Profiles¶
GRANT SELECT on [SCHEMA or TABLE]
Query History¶
Active Session History (ASH, sometimes called AWR tables).
Active session history is recorded by default and is the most commonly used method for capturing query history for Alation. ASH only records some of the queries because it samples them on an interval (one every ten seconds). This means that the Lineage feature is not fully supported when using ASH. Some lineage may appear but many links will be missing. Popularity will not be as accurate with ASH compared to audit but over many samples it should still be a good approximation.Oracle Enterprise Edition and the Diagnostics and Tuning option are required for Active Session History.
ASH query ingestion requires access to a view on top of:
dba_hist_active_sess_history
dba_users
dba_hist_sqltext tables
See appendix A.4 Oracle Query Log View <Appendix_4> for the view definition.
Driver¶
Refer to Support Matrix for the certified driver version.
Enable the Default Schema Extraction¶
Applies from 2020.4
When configuring metadata extraction on the Metadata Extraction tab of the Settings page, enable the Enable default schema extraction checkbox to extract all the default schemas.
Important
Enable this checkbox only if required since extracting all the default schemas will make the metadata extraction process slower.
