Oracle

Capabilities

Connection settings

Create the connection on the Blindata Agent from Settings > Agents > Connections with the connection wizard, choosing Oracle in the technology step.

The Blindata Agent does not include this driver: download the vendor JDBC driver and copy the .jar into the agent drivers folder, as described in Add JDBC drivers. Driver properties can be added under Advanced settings in the connection wizard.

Prerequisites & permissions

• A Blindata Agent installed with network access to Oracle.
• The vendor JDBC driver installed on the agent (see Add JDBC drivers).
• CREATE SESSION and SELECT on the ALL_* dictionary views (or SELECT_CATALOG_ROLE to use DBA_* views).
• Objects appear in ALL_* views only if the user has a privilege on them: grant SELECT ANY DICTIONARY or object-level grants.
• SELECT on V$SQL / DBA_HIST_SQLTEXT for query history.
• SELECT on tables for data quality, profiling and Chat with your data.

Set up metadata crawling

1. Create the connection to Oracle on the agent (see Connection settings) and use Test to verify it.
2. Create or open the system that represents Oracle in the catalog and link it to the connection.
3. In the CRAWLING tab add a crawling job with strategy SQL and, if needed, replace the default queries with the ones suggested below.
4. Run a TEST to check the number of extracted objects, then RUN or schedule the job.

The job options and the column aliases of every extraction query are described in the Generic JDBC reference.

Suggested extraction queries

Oracle has no INFORMATION_SCHEMA: use the SQL strategy with the following queries on the data dictionary. Replace SCHEMA_NAME with the owners to document; filtering by owner keeps the extraction fast on large dictionaries.

Tables query

SELECT owner AS table_schema, table_name, 'TABLE' AS table_type
FROM all_tables where owner in ('SCHEMA_NAME')
UNION
SELECT owner AS table_schema, view_name AS table_name, 'VIEW' AS table_type
FROM all_views where owner in ('SCHEMA_NAME')

Columns query

SELECT owner AS table_schema, table_name, column_name, data_type, column_id AS ordinal_position
FROM all_tab_cols where owner in ('SCHEMA_NAME')

Physical constraints query

Primary keys, foreign keys and unique constraints from ALL_CONSTRAINTS and ALL_CONS_COLUMNS:

SELECT
  c.constraint_name AS name,
  c.owner AS schema,
  CASE c.constraint_type
    WHEN 'P' THEN 'PRIMARY_KEY'
    WHEN 'R' THEN 'FOREIGN_KEY'
    WHEN 'U' THEN 'UNIQUE'
  END AS type,
  c.owner AS target_physical_entity_schema,
  c.table_name AS target_physical_entity_name,
  cc.column_name AS target_physical_field_name,
  cc.position AS list_order,
  rc.owner AS referenced_physical_entity_schema,
  rc.table_name AS referenced_physical_entity_name,
  rcc.column_name AS referenced_physical_field_name,
  NULL AS clause,
  c.delete_rule AS delete_rule,
  NULL AS update_rule,
  NULL AS access_method
FROM all_constraints c
JOIN all_cons_columns cc
  ON cc.owner = c.owner
  AND cc.constraint_name = c.constraint_name
  AND cc.table_name = c.table_name
LEFT JOIN all_constraints rc
  ON rc.owner = c.r_owner
  AND rc.constraint_name = c.r_constraint_name
LEFT JOIN all_cons_columns rcc
  ON rcc.owner = rc.owner
  AND rcc.constraint_name = rc.constraint_name
  AND rcc.position = cc.position
WHERE c.constraint_type IN ('P', 'R', 'U')
  AND c.owner IN ('SCHEMA_NAME')

Routines query

Standalone procedures and functions, with the PL/SQL source rebuilt from ALL_SOURCE. Package members are stored in the package body: import them by adding 'PACKAGE BODY' to the object types.

SELECT
  o.object_name AS SPECIFIC_NAME,
  'put_catalog_name_here' AS ROUTINE_CATALOG,
  o.owner AS ROUTINE_SCHEMA,
  o.object_name AS ROUTINE_NAME,
  o.object_type AS ROUTINE_TYPE,
  (
    SELECT XMLCAST(XMLAGG(XMLELEMENT(e, s.text) ORDER BY s.line) AS CLOB)
    FROM all_source s
    WHERE s.owner = o.owner
      AND s.name = o.object_name
      AND s.type = o.object_type
  ) AS ROUTINE_DEFINITION,
  o.created AS CREATED,
  o.last_ddl_time AS LAST_ALTERED
FROM all_objects o
WHERE o.object_type IN ('PROCEDURE', 'FUNCTION')
  AND o.owner IN ('SCHEMA_NAME')

ALL_SOURCE returns the routine starting from its signature (PROCEDURE name (...) IS). That is enough to catalog the routine and to analyze its statements with SQL lineage from routines .

Lineage from query history

The agent can periodically extract executed statements from Oracle and send them to Blindata, where the SQL lineage engine turns them into dataflows. See the ready-made extraction query in Scheduling and statements extraction.

Related guides

• Metadata import with interface tables
• Data quality probes
• Automated SQL lineage
• Scheduling agent jobs

Updated on 16 Oct 2026