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).

snp_scenall currently available scenarios and their version
snp_scen_stepall steps of a scenario
snp_scen_taskall tasks of the scenarios, incl. code
snp_sball scenarios (even historic), with versions and snapshots
snp_sb_stepall scenarios steps
snp_sb_taskall scenarios tasks
snp_scen_reportall scenario executions
SNP_SESSIONall scenario or mapping executions, incl. duration, state, data volume, parent reference, errors, warnings and variables
SNP_SESS_TASK_LOGruntime information on tasks
snp_param_sessinternal parameters for running sessions
SNP_LPI_RUNLoad 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:

order by 
    scen_version desc