Home » SQL & PL/SQL » SQL & PL/SQL » ORA-04052 Error - URGENT (Oracle 11g)
ORA-04052 Error - URGENT [message #621737] Sun, 17 August 2014 19:33 Go to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

All,

I am facing an issue while executing the below command through an SQL,

EXECUTE IMMEDIATE 'BEGIN MV_REFRESH@EPT_CTA('''||'CGT_WLT_SALE'''||','||'''C'''||'); END;';


Below is the error thrown,

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

Please help me how to research and fix this issue ASAP.
Re: ORA-04052 Error - URGENT [message #621738 is a reply to message #621737] Sun, 17 August 2014 19:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>ORA-04052 Error - URGENT

why is it URGENT for us to solve this problem for you?

The standard advice when (ab)using EXECUTE IMMEDIATE is to compose the SQL statement in a single VARCHAR2 variable
Then print the variable before passing it to EXECUTE IMMEDIATE.
COPY the statement & PASTE into sqlplus to validate its correctness.

04052, 00000, "error occurred when looking up remote object %s%s%s%s%s"
// *Cause:  An error occurred when trying to look up a remote object.
// *Action: Fix the error.  Make sure the remote database system has run
//          CATRPC.SQL to create necessary views used for querying or looking up
//          objects stored in the database.


>ORA-01017: invalid username/password; logon denied
Believe this error!

[Updated on: Sun, 17 August 2014 19:43]

Report message to a moderator

Re: ORA-04052 Error - URGENT [message #621739 is a reply to message #621738] Sun, 17 August 2014 19:56 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

BlackSwan,

Sorry for posting it as urgent. Yes, you're correct. Invalid username/password is the issue. I assume EPT_CTA is a database link and its password is incorrect. I have below questions,

1)How to confirm that EPT_CTA is an database link?
2)Where to locate correct/valid password for EPT_CTA? How to update the password, if required?
3)I did not issue the password in the EXECUTE IMMEDIATE statement, so where does it pull the password from?

Thanks.
Re: ORA-04052 Error - URGENT [message #621740 is a reply to message #621739] Sun, 17 August 2014 20:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> select count(*) from user_objects@orcl;

  COUNT(*)
----------
        68


so you can test your DBLINK using SQL below:

select count(*) from user_objects@EPT_CTA;


>2)Where to locate correct/valid password for EPT_CTA? How to update the password, if required?
http://docs.oracle.com/database/121/SQLRF/statements_5006.htm#i2061505
Re: ORA-04052 Error - URGENT [message #621741 is a reply to message #621740] Sun, 17 August 2014 20:07 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

I am getting the below error while executing query,

select count(*) from user_objects@EPT_CTA;

ORA-02019: connection description for remote database not found
Re: ORA-04052 Error - URGENT [message #621742 is a reply to message #621741] Sun, 17 August 2014 20:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
select count(*) from user_objects@EPT_CTA.WORLD;
Re: ORA-04052 Error - URGENT [message #621743 is a reply to message #621742] Sun, 17 August 2014 20:15 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

BlackSwan,

Still throwing the same error.
Re: ORA-04052 Error - URGENT [message #621744 is a reply to message #621743] Sun, 17 August 2014 20:19 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post results from SQL below


SQL> connect / as sysdba
Connected.
SQL> desc dba_db_links
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 DB_LINK                                   NOT NULL VARCHAR2(128)
 USERNAME                                           VARCHAR2(30)
 HOST                                               VARCHAR2(2000)
 CREATED                                   NOT NULL DATE

SQL> select owner, db_link from dba_db_links where db_link like 'EPT_CTA%';

Re: ORA-04052 Error - URGENT [message #621745 is a reply to message #621744] Sun, 17 August 2014 20:23 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

BlackSwan,

I don't have DBA rights on the database. I will reach out the Infra team to execute the above query and send you the o/p.
Re: ORA-04052 Error - URGENT [message #621746 is a reply to message #621745] Sun, 17 August 2014 20:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
select db_link from user_db_links where db_link like 'EPT_CTA%';
Re: ORA-04052 Error - URGENT [message #621747 is a reply to message #621746] Sun, 17 August 2014 20:31 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

No results for the above given query.

select db_link from user_db_links where db_link like 'EPT_CTA%';
Re: ORA-04052 Error - URGENT [message #621748 is a reply to message #621747] Sun, 17 August 2014 20:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

select owner, db_link from all_db_links where db_link like 'EPT_CTA%';
Re: ORA-04052 Error - URGENT [message #621749 is a reply to message #621748] Sun, 17 August 2014 20:42 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

No results for this query as well.
Re: ORA-04052 Error - URGENT [message #621750 is a reply to message #621749] Sun, 17 August 2014 20:46 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

Got the output from my Infra team for below query,

select owner, db_link from dba_db_links where db_link like 'EPT_CTA%';

OWNER DB_LINK
------------------------------

USR_OM_CH02 EPT_CTA.WORLD
Re: ORA-04052 Error - URGENT [message #621751 is a reply to message #621750] Sun, 17 August 2014 20:52 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

select * from dba_db_links where db_link like 'EPT_CTA%';

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ---------------------------
USR_OM_CH02 EPT_CTA.WORLD USR_PT_CH02 s221asd02 28-MAR-12
Re: ORA-04052 Error - URGENT [message #621752 is a reply to message #621751] Sun, 17 August 2014 21:18 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Oracle_Walker wrote on Sun, 17 August 2014 20:52
select * from dba_db_links where db_link like 'EPT_CTA%';

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ---------------------------
USR_OM_CH02 EPT_CTA.WORLD USR_PT_CH02 s221asd02 28-MAR-12



BlackSwan has been feeding you lots of hints. Did you follow up by looking up the documentation of DBA_DBA_LINKS to see what each column indicates?

You said earlier that you do not have DBA rights and had to get someone else to run queries against the DBA_* views. So have you engaged your DBA to assist? In more than just running a query that some stranger on the web gave you?


The above result indicates that the db link named 'EPT_CTA.WORLD', owned by local db user 'USR_OM_CH02' will allow the local database to act as a client connecting to the database indicated by the tnsnames.ora entry 's221asd02', using the credentials of user 'USR_PT_CH02' as defined in that remote database. No one here can tell you what the password for that user is. It is kept in encrypted format in an un-documented table. For understandable security reasons Oracle goes to great length to NOT expose passwords.
Re: ORA-04052 Error - URGENT [message #621753 is a reply to message #621752] Sun, 17 August 2014 21:41 Go to previous messageGo to next message
Oracle_Walker
Messages: 71
Registered: January 2012
Location: United States
Member

EdStevens,

Unfortunately i don't have a DBA for me to assist. The person who executed the query is an application admin who has DBA rights.

If the password is stored in an un-documented table and encrypted, then no one would have updated it. Also i have the password located in user_db_links view, Is it the password that Oracle uses to connect?
Re: ORA-04052 Error - URGENT [message #621754 is a reply to message #621753] Sun, 17 August 2014 23:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Oracle_Walker wrote on Mon, 18 August 2014 08:11
Also i have the password located in user_db_links view, Is it the password that Oracle uses to connect?


Per documentation USER_DB_LINKS,

Quote:

It also displays an additional column, PASSWORD, which is no longer used and for which nothing is returned. The PASSWORD column is maintained for backward compatibility only.


Re: ORA-04052 Error - URGENT [message #621756 is a reply to message #621737] Mon, 18 August 2014 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why do you use a complex EXECUTE IMMEDIATE and not just?
MV_REFRESH@EPT_CTA('CGT_WLT_SALE','C');


Re: ORA-04052 Error - URGENT [message #621797 is a reply to message #621753] Mon, 18 August 2014 08:00 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Oracle_Walker wrote on Sun, 17 August 2014 21:41
EdStevens,

Unfortunately i don't have a DBA for me to assist. The person who executed the query is an application admin who has DBA rights.


So who IS the DBA? Surely, if you have a database, someone is responsible for DBA tasks.


Quote:
If the password is stored in an un-documented table and encrypted, then no one would have updated it.


Not correct. The actual password for the referenced account is stored in the database of that account, to be used when authenticating attempts to connect with that account. All that's needed is to go to that database with proper authority and issue 'ALTER USER SCOTT IDENTIFIED BY LION'. That does NOT update ANY information that ANY client has regarding what credentials said client is supposed to use when connecting as SCOTT.

Quote:
Also i have the password located in user_db_links view, Is it the password that Oracle uses to connect?

If your version of Oracle shows anything other than nulls, then that is encryption of the password that the client database will use when it presents a connection request to the target of the db link. That is NOT guaranteed to be the CORRECT password. As I just said,
someone could change the actual password of the target user, and your link would never know.
Previous Topic: Bind Variables
Next Topic: Insert as select from Table
Goto Forum:
  


Current Time: Fri Apr 19 20:16:38 CDT 2024