3

procedure involving dblinks gets invalidated regularly .we need to recompile every time.How to find what is causing the problem

in my case

procedure p1()--resides in db1
is
begin
....
select col1 from table1@db2;

...
end
/

for some reasons the procedure gets invalidated and the dblinks doesnt work and i get the following errors

ORA-04052
ORA-00604
ORA-02019

and i recompile all the invalid objects this seem to work.

i need to figure why the proc in db1 gets invalidated?

  • if the db link doesn't work, (ora-2019) indicates that db-link perhaps changed? that alone is probably sufficient to invalidate your procedure P1. Check who/why the db link is getting changed? – Raj May 13 '14 at 11:53
  • hi raj,how can i check who/why the db link changed.u mean auditing – user37143 May 13 '14 at 12:05
  • can REMOTE_DEPENDENCIES_MODE = SIGNATURE be used to overcome this problem – user37143 May 13 '14 at 13:13
  • the table1 in "table1@db2", is that a synonym? if the stack trace includes kqlhdlod you may be hitting bug 10210507. Workaround is to change table1 to a view and then use view1@db2. YMMV, do test carefully. – Raj May 13 '14 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.