Knowledge Base : Oracle Data Integrator Design Repository Metadata

181 Juni 16, 2022

This page explains the most common Oracle Data Integrator (ODI) Design Repository Metadata tables and views and how to use them (for debugging and SDK programming).

table_name contents
snp_scen all currently available scenarios and their version
snp_scen_step all steps of a scenario
snp_scen_task all tasks of the scenarios, incl. code
snp_sb all scenarios (even historic), with versions and snapshots
snp_sb_step all scenarios steps
snp_sb_task all scenarios tasks
snp_scen_report all scenario executions
SNP_SESSION all scenario or mapping executions, incl. duration, state, data volume, parent reference, errors, warnings and variables
SNP_SESS_TASK_LOG runtime information on tasks
snp_param_sess internal parameters for running sessions
SNP_LPI_RUN Load Plan instance runs. Every time an attempt is made to re-start a load plan, data is captured here.

These tables are located in schema …_ODI_REPO.

The complete ODI 11g and 12c Repository structure is also available on MOS as DocID 1903225.1 .

See also: ODI 11g and 12c Repository Structures Available on MOS.

Show the last ODI mapping executions and error messages:

select * from ODIxxxBI_ODI_REPO.snp_session order by sess_beg desc;

Show steps with their SQL code for a mapping:

select s.scen_name, s.scen_version, t.task_name1, t.task_name2,t.def_txt, s.last_date 
from ODIxxxBI_ODI_REPO.snp_scen s 
join ODIxxxBI_ODI_REPO.snp_scen_task t on t.scen_no = s.scen_no
where s.scen_name like '%MAPPINGNAME%' 
order by t.SCEN_TASK_NO;

Show scenarios sorted by version:

select 
    scen_version,
    scen_name
from 
    ODIxxxBI_ODI_REPO.snp_scen
order by 
    scen_version desc
    ;