Finding a table in ODI logs

ODI is the one of the most common ETL tool , especially in Turkey. It is very user friendly , easy to learn , tons of docs at web and there are many other advantages.

Some times when we make a technical analysis about our ETL processes. And we need to find an ODI scenario that includes our table as a source or as a target. We can check our scenarios manually but when it is Oracle , there are always easier ways.

We can use this script for find which scenario log has our tables name in it.

SELECT sess_beg, sess_name 
FROM snp_session 
where 1=1
--AND to_char(sess_beg,'YYYYMMDD')='20191101'
  AND sess_beg >TRUNC(SYSDATE-2)
  AND sess_no in (select distinct sess_no from SNP_SESS_TASK_LOG 
 where upper(def_txt) like '%<<YOUR_TABLE_NAME>>%') 
ORDER BY sess_beg ;

There is one little rule for using this script successfully , scenario must run at least one time. That is a must , because we are using the log that generated by ODI. So it must be generated.

You can download ODI demo VM that link below ;

https://www.oracle.com/middleware/technologies/data-integrator/odi-demo-downloads.html