Skip to content
Advertisement

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.

Advertisement

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 ||'%';
Advertisement