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.

5 thoughts on “Drop database link in another schema

  1. Marko Sutic

    Laurent,

    great workaround – thanks for sharing!
    Didn’t know that you can use job to drop/create db link in another schema.

    Very elegant solution.

    Cheers,
    Marko

  2. laurent

    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,

Comments are closed.