CODEWITHSUNDEEP
oracle-databaseoracle11goracle-sqldeveloper
Bonzay
Bonzay

Reputation: 750

find out the loading mechanism for an oracle table

I am dealing with a black box. It is an oracle db. There is no documentation. On top of that i have no experience on oracle dbs, the only thing i know is that it is using odi to implement the loading flows. Is there any way to pick a table and back track the loading process? How am i going to find which package loads the table?

Is there any sql statement which can lead me to the loading method? How can i reverse engineer this?

For example, in sql server i would check the sql agent to find the job which loads the table, or query the SSIS metadata to find the package which includes the given table. Is there something similar in oracle?

Upvotes: 1

Views: 812

Answers (1)

vishnudattan
vishnudattan

Reputation: 476

For starters, I would try:

    select * from dba_source 
    where upper(text) like '%<your_table_name_in_caps>%' 
    and owner != 'SYS';

This might point you to objects (packages, functions, procedures, triggers etc.) that reference the table in question. Hopefully that gives you some direction. If dba_source (text source of all stored objects in the database) doesn't work, try all_source (objects accessible to the current user).

You may also be able to query some of the hist tables (dba_hist_sqltext, dba_hist_active_sess_history etc.) to check programs that recently accessed this table or check any scheduled jobs (dba_scheduler_jobs).

These are all blind hits though and may bring in irrelevant results depending on your specific case. Good luck and come back here if you need more help.

Upvotes: 1

Related Questions