We have a database that is refreshed every week from production. After one
of such refreshes, developers complained some packages were invalid. The
pattern was that those packages had declarations such as this
v_end_date gl.gl_periods.end_date%type.
Package could not be compiled because you get invalid identifier
g.gl_periods.
But the following would work.
select end_date from gl.gl_periods
select end_date from gl_periods (there was a synonym from apps schema to gl
schema)
select end_date from apps.gl_periods;
It only failed when you called it as part of plsql variable declaration.
A simple test case was
declare
v_end_date gl.gl_periods.end_date%type;
begin
null;
end;
/t
I compared the table privileges, role privileges and sysem privileges
between production and this test and there was no difference.
Checked init.ora,environment variables, flushed buffer cache, shared pool,
bounced database too as last resort. Nothing helped
Basically we could not find any pattern (finally refreshed again from
production and it worked).
But what can cause this? Any clues?
We suspect a developer did something that caused this issue becuase all
the developers have access to 'apps' schema in this development instance.
But I am curious to know 'what was that something that they did'..
Also, this is not a refresh that we have been doing for the past 2 weeks.
This was something that is running for more than 5 years now and we have
not changed the scripts. Also after the incident, we refreshed atleast
couple of times and have not faced this issue again.
Thanks for your time
Kumar
