Oracle database

Catalog and documentation

Data catalog

Dataedo documents metadata of the following objects:

• Tables
• Views
• Materialized views
• Procedures
• Packages
• Functions
• Sequences
• Jobs
• Database links

For more details, go to the Connector specification section.

Descriptions, aliases, and custom fields

When technical metadata is imported, users will be able to edit descriptions of each object and element, provide meaningful aliases (titles), and document everything with additional custom fields.

Import and export comments

When importing metadata from Oracle, Dataedo reads table, view, and column comments.

Business Glossary

Users will be able to link a Business Glossary term to any Oracle object.

Table relationships and keys

Dataedo imports table relationships (foreign keys), primary and unique keys with their columns.

ER Diagrams

Using imported and manually created foreign keys, Dataedo allows you to create your own ER diagrams (ERDs) manually.

Data Profiling

Users will be able to run data profiling for a table, view, or materialized view, then save selected data in the repository. This data will be available from Desktop and Web. Profiling requires Select permission over the profiled object.

Lookups / Reference data

Users will be able to build Lookups for columns in Oracle tables and views and feed them with distinct values from a column.

Data Classification

Users will be able to run classification on the Snowflake database in the repository in search of columns containing potentially sensitive data. All built-in functions are supported.

Importing changes and schema change tracking

Changes to descriptions in Dataedo Desktop and Web Catalog are tracked and saved in the repository.

Description changes

Changes to descriptions in Dataedo Desktop and Web Catalog are tracked and saved in the repository. Users are able to manually edit object descriptions.

Share in Web Catalog or export to HTML, PDF, or Excel

Documentations can be exported by sharing them in the web catalog or generating HTML, PDF, or Excel.

Subject areas

Users can manually create multiple ERDs in subject areas, as a diagram of the whole database or only part of it.

Database Links

The Oracle Connector imports Database Links into Linked Sources, capturing the connection details with the name assigned to each Database Link. While the import process automatically associates the source database with the linked server, it does not automatically link the source to the corresponding objects. Users have the option to manually assign the linked server to the desired objects.

Important note

If a view is created using a Database Link connection object, the user must assign the linked server and parse the script to establish lineage.

How to Connect

1. Connection type - Choose connection type (Direct or Oracle client).
2. Host - Provide a host name or address where a database is located. E.g., server17, server17.ourdomain.com, or 192.168.0.37.
3. Service name - Type in the Oracle instance Service name.
4. Port - Type in the Oracle instance port name.
5. User and password - Provide your username and password.
6. Schema - You can choose schema(s) that you want to import into your documentation.

Connector specification

Supported versions

Oracle 9i R1, 9i R2, 10g R1, 10g R2, 11g R1, 11g R2, 12c R1, 12c R2, 18c, 19c, 21c, 23c.

Supported editions

Express, Standard, and Enterprise.

Imported objects

Object	Imported as
Table	Table
View	View
Materialized view	View
Procedure	Procedure
Package	Package
Function	Function
Sequence	Sequence
Job	Task/Job
Database link	Linked Source

Imported metadata

	Imported	Editable
Tables	✅	✅
Columns	✅	✅
Data types	✅
Nullability	✅
Column comments	✅	✅
Table comments	✅	✅
Foreign keys	✅	✅
Primary keys	✅	✅
Unique keys	✅	✅
Triggers	✅
Views, Materialized Views	✅	✅
Script	✅	✅
Columns	✅	✅
Data types	✅
Column comments	✅	✅
View comments	✅	✅
Procedures, Cubes	✅	✅
Script	✅	✅
Parameters	✅
User-defined Functions	✅	✅
Script	✅	✅
Input arguments	✅
Output results	✅
Function comments	✅	✅
Sequences	✅	✅
Sequence comments	✅	✅
Jobs	✅	✅
Job definition	✅
Job comments	✅	✅
Parameters	✅
Data types	✅
Database link	✅	✅
Connection details	✅

Supported features

Feature	Imported
Import comments	✅
Write comments back	✅
Data profiling	✅
Reference data (import lookups)	✅
Importing from DDL	✅
Generating DDL	✅
FK relationship tester	✅
Writing back comments	✅

Comments

Dataedo reads comments from the following Oracle objects:

Object	Read	Write back
Table comments	✅	✅
Column comments	✅	✅
View comments	✅	✅
Columns	✅	✅
Procedures and Cubes comments	✅	✅
Function comments	✅	✅
Sequence comments	✅
Job comments	✅

Data profiling

Dataedo supports the following data profiling in Oracle:

Profile	Support
Table row count	✅
Table sample data	✅
Column distribution (unique, non-unique, null, empty values)	✅
Min, max values	✅
Average	✅
Variance	✅
Standard deviation	✅
Min-max span	✅
Number of distinct values	✅
Top 10/100/1000 values	✅
10 random values	✅

Read more about profiling in the Data Profiling documentation.

Data Lineage

Source	Method	Version
Views - object level	From dependencies	✅
Views - object level	From SQL parsing	✅
Views - column level	From SQL parsing	✅

Required access level

Importing database objects requires a certain access level in the documented database. The user specified in the "Import objects from database" window should at least have the "Connect" role and "Create session" system privilege.

Importing object definitions from a different schema requires either SELECT_ANY_DICTIONARY (recommended) or SELECT permission on any table, view, and Execute on any procedure, function, sequence, and job that are to be documented.

No data is altered in the source database during the synchronization process.

The following objects are accessed during the schema import process:

• all_constraints
• all_cons_columns
• all_objects
• all_tab_columns
• all_tab_comments
• ALL_ARGUMENTS
• ALL_CONSTRAINTS
• ALL_CONS_COLUMNS
• ALL_INDEXES
• ALL_IND_COLUMNS
• ALL_SOURCE
• ALL_TRIGGERS
• ALL_SEQUENCES
• ALL_SCHEDULER_JOBS
• dual
• ALL_DB_LINKS (only for database links import)

Known issues and limitations

The following schema elements currently are not supported:

• Check constraints (vote)
• Non-unique indexes (vote)