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.

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