Drop database link in another schema

Today I wrote this script :


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

    job_action=>'BEGIN execute immediate ''drop database link &db_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.

Author: Laurent Schneider

Oracle Certified Master

5 thoughts on “Drop database link in another schema”

  1. 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.


  2. 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


Leave a Reply