移除多个schema中job

Sometimes you need to execute SQL (or PL/SQL) as some other user. Say you need to remove a job residing in some other user’s schema. Or you need to create a database link in a different schema. You can’t do things like these without becoming the other user. If you don’t know the other user’s password then you basically have two choices:
1. Remember the user’s password hash value, then change the password, then log in and do your job, then put the password back using identified by values (you can skip the first and last parts if you don’t care about keeping original password in place).
2. Create a procedure with definer rights (which is the default) in another schema and use it to execute statements, effectively behaving as you are the other user.

A third method exists: use dbms_sys_sql. It is similar to dbms_sql except that it allows you to parse and execute statements as another user. That is, the dbms_sys_sql.parse() function accepts an additional parameter, which is userid. Here is how you can use it to remove jobs residing in schemas “A” and “B”:
SQL>create user a identified by oracle;

User created.

SQL>create user b identified by oracle;

User created.
SQL>select job, schema_user from dba_jobs where schema_user in ('A','B');

       JOB SCHEMA_USER
---------- ------------------------------
         2 A
         3 B
         4 A

SQL>declare
     l_cursors dbms_sql.Number_Table;
     l_result number;
    begin
     for cur in (
      select j.JOB, u.user_id
       from dba_jobs j, dba_users u
       where j.SCHEMA_USER=u.username
        and j.SCHEMA_USER in ('A', 'B')
    ) loop
     --parse the cursor only if we haven't already
     if ( not l_cursors.exists(cur.user_id) )
     then
      l_cursors(cur.user_id):=dbms_sys_sql.open_cursor;
      --parsing anonymous PL/SQL block as a job owner
      dbms_sys_sql.parse_as_user(
       c => l_cursors(cur.user_id),
       statement => 'begin dbms_job.remove(:job); end;',
       language_flag => dbms_sql.native,
       userid => cur.user_id
      );
     end if;
     --bind the job number
     dbms_sys_sql.bind_variable(
      c => l_cursors(cur.user_id),
      name => 'job',
      value => cur.job
     );
     --remove the job by executing
     l_result:=dbms_sys_sql.execute(l_cursors(cur.user_id));
    end loop;
    end;
    /  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33 

PL/SQL procedure successfully completed.

SQL>select job, schema_user from dba_jobs where schema_user in ('A','B');

no rows selected

Keep in mind that this package is undocumented, so any behavior. cannot be guaranteed. Keep an eye on who is granted an execute privilege on it, since as you have seen, it is a very powerful tool.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/628922/viewspace-688439/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/628922/viewspace-688439/