Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

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

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database