How Set To Broken Jobs From Other Users

Of course, you should not use dbms_job any more, but how many times are we working on an installation or a database that already has them? It would be best to migrate these jobs to dbms_scheduler. In this case I will post a small script to put all jobs to broken, without having to log in as the owner of the same.

 
SET SERVEROUTPUT ON

DECLARE
   UserID        NUMBER;
   Sentence      VARCHAR2 (500);
   Result        PLS_INTEGER;
   HandleCursor  PLS_INTEGER;
   debug         BOOLEAN := FALSE;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);

  FOR i IN (SELECT JOB, SCHEMA_USER FROM DBA_JOBS WHERE BROKEN != 'Y') LOOP
      if debug then  DBMS_OUTPUT.PUT_LINE ('DEBUG (JOB)         : ' || I.JOB);  end if;
      if debug then  DBMS_OUTPUT.PUT_LINE ('DEBUG (SCHEMA_USER) : ' || I.SCHEMA_USER);  end if;

      SELECT USER_ID INTO UserId FROM DBA_USERS WHERE USERNAME = RTRIM (LTRIM (i.SCHEMA_USER));
      Sentence := 'BEGIN DBMS_JOB.BROKEN (' || i.JOB ||', TRUE); END; ';

      HandleCursor := sys.dbms_sys_sql.open_cursor ();
      sys.dbms_sys_sql.parse_as_user (HandleCursor, Sentence, dbms_sql.native, UserId);
      Result := sys.dbms_sys_sql.execute (HandleCursor);
      sys.dbms_sys_sql.close_cursor(HandleCursor);

      DBMS_OUTPUT.PUT_LINE ('Executing :   ' || Sentence);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE (CHR (10) || 'Please, remember execute commit to close the actual transaction...');
END;
/

The trick here is to make the parseo as the owner user.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s