SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

alter a user password via a database link

660270Sep 17 2008 — edited Jun 24 2009

Hi,

I need to alter a users password in plsql to a remote database.
I have a db link setup but cannot see how to execute the Alter user command so it willl execute in the remote database.

I was hoping to do ALTER USER@dblink1 user IDENTIFIED BY newpasssword but Oracle does not like the @dblink in the alter command.

Does anyone know a way around this?

Thanks

This post has been answered by MichaelS on Sep 17 2008

Jump to Answer

221158

Create a procedure on the remote database to alter the user password. Parameterise password and username and call it over a db link

Christopher Soza
Oracle BI DBA
Orix Consultancy Services Ltd
b: http://sozaman.blogspot.com

MichaelS

Answer

You can try something like

declare
 job binary_integer;
begin
 dbms_job.submit@dblink1(job,'begin execute immediate ''alter user xy identified by ..... ''; end;');
end;

Marked as Answer by 660270 · Sep 27 2020

660270

Thank you very much, this was exactly what I was after, you are a super star. Using the dbms_job.submit works a treat.

338570

I used this method. I don't have errors, but password is not changed.
What is my problem?
Mary

1 person found this helpful

1 - 4

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 22 2009

Added on Sep 17 2008

#oracle, #plsql, #sql

4 comments

6,294 views

SQL & PL/SQL

alter a user password via a database link

Comments

Post Details