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.
I like it. 🙂
I usually do this,
But I prefer the job idea. 🙂
You can also use DBMS_SQL for this as shown here:
@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
but this breaks for locked accounts
great workaround – thanks for sharing!
Didn’t know that you can use job to drop/create db link in another schema.
Very elegant solution.
You can use the procedure
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