Drop database link in another schema

Today I wrote this script :

drop_database_link.sql

accept owner char prompt "Enter database link owner : "
accept db_link char prompt "Enter link name : "

begin
dbms_scheduler.create_job(
job_name=>'&owner..drop_database_link',
job_type=>'PLSQL_BLOCK',
job_action=>'BEGIN execute immediate ''drop database link &db_link'';END;'
);
dbms_scheduler.run_job('&owner..drop_database_link',false);
dbms_lock.sleep(2);
dbms_scheduler.drop_job('&owner..drop_database_link');
end;
/

I am using the scheduler to run a job as another user. The database link owner does not need to have any privilege, neither CREATE SESSION nor CREATE JOB. It could locked and expired.

Published by Laurent Schneider

Oracle Certified Master

Join the Conversation

5 Comments

  1. Hello,

    You can use the procedure

    exec sys.kupp$proc.change_user(‘MYUSER’);

    In case of the following error, you shoud run the query select sys.kupp$proc.disable_multiprocess from dual;
    ERROR at line 1:
    ORA-31625: Schema SYSTEM is needed to import this object, but is unaccessible
    ORA-06512: at “SYS.KUPP$PROC”, line 37
    ORA-06512: at “SYS.KUPP$PROC”, line 597
    ORA-06512: at line 1

    Regards,

Leave a comment

Your email address will not be published.