Thanks for the question, Gautam.
Asked: July 09, 2018 - 5:46 pm UTC
Last updated: July 12, 2018 - 10:21 am UTC
Version: 11.2.0.4.8
Viewed 1000+ times
You Asked
Hello,
I need some help understanding the synonym behavior. We have a table in production database with a global synonym, which is being referenced by other objects in the database. As part of a weekly process that runs every weekend, we drop this table and recreates with new data. However, global synonym does not get dropped as part of this process. Since, table is recreated with the same name, our assumption is that it should be using the same synonym without any issue.
Please, let me know if there's a reason we should be recreating the synonym after table is recreated every week?
We recently observed an issue where although I can access data using the synonym, but package was throwing error when referencing the table with synonym. And when we recreated the synonym, issue was fixed.
and Chris said...
Sorry, I can't reproduce this behaviour:
create table t (
x int
);
insert into t values ( 1 );
commit;
create public synonym t for chris.t;
grant select on t to u;
create or replace procedure u.p as
begin
for rws in (
select * from t
) loop
dbms_output.put_line ( rws.x );
end loop;
end p;
/
conn u/u
exec p;
1
conn chris/chris
drop table t cascade constraints purge;
conn u/u
exec p;
ORA-06550: line 1, column 7:
PLS-00905: object U.P is invalid
conn chris/chris
create table t (
x int
);
insert into t values ( 1 );
commit;
grant select on t to u;
conn u/u
exec p;
1
Can you give us a test like (including create users, like above) that shows this problem?
Of course, there's always the question:
Can you use direct schema references instead?
Is this answer out of date? If it is, please let us know via a Comment