Solved

ORA-04052: error occurred when looking up remote object

Posted on 2008-09-29
4
4,598 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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

733 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