?
Solved

ORA-04052: error occurred when looking up remote object

Posted on 2008-09-29
4
Medium Priority
?
4,668 Views
Last Modified: 2013-12-18
I am getting error when I user DB_LINK

0/0      ORA-04052: error occurred when looking up remote object
         SCOTT.PASS_REP@TOM.target.COM
         ORA-00604: error occurred at recursive SQL level 1
         ORA-28000: the account is locked
         ORA-02063: preceding line from TOM.TARGET.COM

Any help

Thanks
CREATE OR REPLACE PROCEDURE SCOTT.change_pass (
   p_username     VARCHAR2,
   p_password     VARCHAR2,
   old_password   VARCHAR2
)
IS
   return_value   BOOLEAN;
   v_password     VARCHAR2 (30);
   v_host         VARCHAR2 (30) := SUBSTR (SYS_CONTEXT ('USERENV', 'SERVER_HOST'), 1, 3);
   v_sqlerrm      VARCHAR2 (32000);
   v_dblink       VARCHAR2 (30);
BEGIN
   EXECUTE IMMEDIATE    'ALTER USER '
                     || p_username
                     || ' identified by '
                     || p_password
                     || ' replace '
                     || old_password;
 
   SELECT PASSWORD
     INTO v_password
     FROM dba_users
    WHERE username = p_username;
 
   IF v_host = 'san'
   THEN
 
         INSERT INTO SCOTT.pass_rep@sandi.target.com
                  (username, new_password, old_password, dt_created,
                   hostname, error_message
                  )
           VALUES (p_username, v_password, old_password, SYSDATE,
                   v_host, NULL
                  );   
   
   ELSE
      INSERT INTO SCOTT.pass_rep@tom.target.com
                  (username, new_password, old_password, dt_created,
                   hostname, error_message
                  )
           VALUES (p_username, v_password, old_password, SYSDATE,
                   v_host, NULL
                  );   
   END IF;
   
   COMMIT;
--  RETURN (NULL);
EXCEPTION
   WHEN OTHERS
   THEN
      v_sqlerrm := SQLERRM;
 
      INSERT INTO SCOTT.pass_rep
                  (username, new_password, old_password, dt_created,
                   hostname, error_message
                  )
           VALUES (p_username, v_password, old_password, SYSDATE,
                   v_host, v_sqlerrm
                  );
 
      COMMIT;
END;
/
 
ERROR
=====
 
0/0      ORA-04052: error occurred when looking up remote object
         SCOTT.PASS_REP@TOM.target.COM
         ORA-00604: error occurred at recursive SQL level 1
         ORA-28000: the account is locked
         ORA-02063: preceding line from TOM.TARGET.COM

Open in new window

0
Comment
Question by:basirana
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 22599413
I would guess that this is your real error:

ORA-28000: the account is locked
0
 

Author Comment

by:basirana
ID: 22600549
yes thats a real error when I try to compile
0
 
LVL 15

Accepted Solution

by:
Shaju Kumbalath earned 1000 total points
ID: 22603079
login to remote db
alter user   SCOTT  account unlock;
0
 

Author Closing Comment

by:basirana
ID: 31501294
thank you
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question