How to SELECT all JOBS that using a DBLINK on oracle?

Is my pleasure to say helo to all of you. I need help on oracle to select all JOBS that using a DBLINK on oracle, to stop them during maintenance. I will appreciate your help.

Answer

TRY THIS:

SELECT JOB_NAME 
FROM ALL_SCHEDULER_JOBS, ALL_DB_LINKS
WHERE JOB_ACTION LIKE '%'|| DB_LINK ||'%'
OR DB_LINK LIKE '%'|| JOB_ACTION ||'%'; --this line is more like just in case...

Maybe you will have to play with upper like this:

SELECT JOB_NAME 
FROM ALL_SCHEDULER_JOBS, ALL_DB_LINKS
WHERE upper(JOB_ACTION) LIKE '%'|| DB_LINK ||'%'
OR DB_LINK LIKE '%'|| upper(JOB_ACTION) ||'%';

1. I have created a database link

   CREATE DATABASE LINK local 
   CONNECT TO hr IDENTIFIED BY password
   USING 'local';
   

2. I have created a job where I have used a name of the database link in the job_action section (name of the database link is LOCAL).

   BEGIN
      -- Job defined entirely by the CREATE JOB procedure.
      DBMS_SCHEDULER.create_job (
      job_name        => 'Update_Status',
      job_type        => 'PLSQL_BLOCK',
      job_action      => 'BEGIN ;LOCAL; END;',
      start_date      => SYSTIMESTAMP,
      repeat_interval => 'freq=minutely; bysecond=0;',
      end_date        => NULL,
      enabled         => TRUE,
      auto_drop          =>   FALSE,
      comments        => 'Job defined entirely by the CREATE JOB procedure.');
   END;
   

3. I have run this query from above:

   SELECT JOB_NAME 
   FROM ALL_SCHEDULER_JOBS, ALL_DB_LINKS
   WHERE JOB_ACTION LIKE '%'|| DB_LINK ||'%'
   OR DB_LINK LIKE '%'|| JOB_ACTION ||'%'; 
   

4. The result of the query was:

   |    JOB_NAME     |
   +-----------------+
   |  UPDATE_STATUS  |
   

If you want to search all objects to find your db link then try this:

select *  
from all_source
     , ALL_DB_LINKS
where text like '%'|| DB_LINK ||'%';