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

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.

    Cheers,
    Marko

  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

    Regards,

Leave a Reply