Find Stored Procedure By Table Name (Oracle)

Need help. Every morning at 4 o’clock a table is created in the database, I need to understand what sources are used to create it, so I tried to find a stored procedure that creates this table using all_source or dba_source (select * from all_source where upper(text) like ‘%TABLE_NAME%’, but the result was returned empty. I think this has to do with access restrictions. Is there any other way to solve my problem? Thanks. Oracle 12c/ plsql developer. I only have table name and schema

Answer

You can use:

select owner,
       job_name,
       job_style,
       job_type,
       program_name,
       job_action,
       start_date,
       repeat_interval,
       schedule_name,
       last_start_date,
       next_run_date,
       state
from   all_scheduler_jobs
WHERE  NEXT_RUN_DATE >= TRUNC(SYSDATE) + INTERVAL '27' HOUR
AND    NEXT_RUN_DATE <  TRUNC(SYSDATE) + INTERVAL '29' HOUR
order by
       owner,
       job_name;

To look for scheduled jobs that are next due to run after 03:00 tomorrow and before 05:00 tomorrow and then check the action that it invokes.

If that returns no rows then you can either widen the time range or remove the time filter and look at all the jobs.

Alternatively, you can check to see if a script is being run from the operating system via a cron job.