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.
Hi.
I like it. 🙂
I usually do this,
http://www.oracle-base.com/dba/script.php?category=script_creation&file=logon_as_user.sql
But I prefer the job idea. 🙂
Cheers
Tim…
You can also use DBMS_SQL for this as shown here:
https://marcusmonnig.wordpress.com/2011/09/21/how-to-change-a-dbms-job-owned-by-another-user-as-user-sys/
@Tim: A user on my blog referenced http://blog.sydoracle.com/2010/10/temporary-access-to-database-accounts.html which shows that you are stuck if there is a password reuse policy enabled.
I used to login via proxy
https://laurentschneider.com/2006/12/su-in-sqlplus.html
but this breaks for locked accounts
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
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,