Categories
dba sql

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.

By Laurent Schneider

Oracle Certified Master

5 replies on “Drop database link in another schema”

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.