developers

    Forum Stats

  • 3,874,064 Users
  • 2,266,670 Discussions
  • 7,911,712 Comments

Discussions

Can I run stored procedure from scheduled job as sysdba?

User_7WOTL
User_7WOTL Member Posts: 1 Green Ribbon
edited Aug 1, 2022 3:56PM in SQL & PL/SQL

I have user c##admin in my core DB (Oracle 19c), with "sysdba" privileges.

I want it to select from a pdb using a database link without giving a password.

assuming the following:

 - PDB Name: PDB_TEST 

 - Schema Name: TEST 

 - Table Name: TABLE1

This is the database link:

CREATE DATABASE LINK TEST_DBLINK USING 'PDB_TEST'

Assuming I want to run the following query:

select * from "TEST"."TABLE1"@TEST_DBLINK;

When I'm connected to c##admin as sysdba, the query runs and returns the results.

When I connected to c##admin (not as sysdba) or when I run it from a scheduled job it failed with the following error:

> ORA-01017: invalid username/password; logon denied

So, my question is: can I run a scheduled job as sysdba?

BTW: A NO is also an acceptable answer.

Answers

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,160 Gold Trophy

    Overall, your question is very confusing as you've worded it. That would limit the number of people who will respond to it. Let me try to re-word some of it and you can confirm/clarify.

    You have a common user (i.e. begins with c##) defined in your container DB (i.e. CDB). You have a procedure called test_proc compiled into the c##admin schema within the CDB that references a DB link. You created a DB link within the CDB that points to a PDB called PDB_TEST.

    When you exec something when logged in as some unknown user it works. No clue if you mean test_proc or not. You see a count. Do you mean on your screen or in this LOG_TABLE1 or what? When you create a scheduler entry via an unknown user it doesn't work. When you log in as c##admin to an unknown DB, it doesn't work

    You've not given us a clear picture of the problem given there are two users and two DBs in play with an unknown version of Oracle, see?

    Possible issues: I've never used a CREATE DATABASE LINK without including CONNECT TO to tell Oracle how you want to connect into the remote DB. The connection should be using your locally connected user in this case but you can also try with CONNECT TO user IDENTIFIED BY password if you want to make extra sure.

    A common user has no default privs in a PDB. Per 12c documentation "A common user need not have the same privileges in every container". Have you setup privs in the PDB so that c##admin has rights to do what you are trying to have it do, such as connect and query a table?

    Remember, we can't see your screen so you have to provide a clear example, ideally using code, that demonstrates the problem.

developers