Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We had an OEM job that would run a PL/SQL block using a particular database users credentials. The credentials are the same for two different databases, so there was no problem for the block to use a private database link without credentials to pull data from the second database into the first.

We migrated the job to DBMS_SCHEDULER, but the job fails with the following error:

ORA-04052: error occurred when looking up remote object PATIENT.PTTRANS@BILL10G 
ORA-00604: error occurred at recursive SQL level 2 ORA-01017: invalid username/password; logon denied 
ORA-02063: preceding line from BILL10G 

I haven't found definitive information, but the things I have read seem to indicate that since scheduler jobs do not have a login, they cannot use database links that use passed credentials. That would make sense since I get the same error whether the job is owned by SYSTEM or by the user that normally runs the block (and still can when logged in).

Can anyone provide further explanation of this situation or more importantly explain ways to work around this issue? I can think of several, but I'm not sure I like any of them.

share|improve this question
2  
not seen this one myself Leigh, most dblinks I've seen rely on hard coded passwords (which have their own issues). – Niall Litchfield Feb 8 '12 at 13:46
    
@Niall Thanks for the comment. I've used both, but this is the first time with dbms_scheduler. – Leigh Riffel Feb 8 '12 at 14:21

The only way to get around this was to use credentials in the database links definition. Not nice if you ask me but it is what I found. This was in 10g and 11, did not test with 12c.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.