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

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>