Hi Tom,

My department has 2 Oracle database servers at different city, let's say db1 and db2. Data exchange between database is only using Oracle jobs which either refreshing materialized views or delete-insert tables.

Few days ago there was network problems in my office.
Also one of the database harddisk partition storage ran out of space.
One of the DBA compressed the archive log to spare some space during the problem happened.
But it has been fixed now.

One of the outcome was that now from db1 I CANNOT select * one table of db2 using schema name prefix.

For example if I run query from one of the schema/user in db1, either from Toad software or Sqlplus:

select * from office.room@db2

The query runs forever and never stops. Usually it's not a problem. Selecting * on other tables on db2 from db1 are no problem at all. Only this one table.

I can select the table with no problem if I add WHERE filter or I select the columns, for example :

select * from office.room@db2 where roomnumber='A';

or

select roomname, rumnumber from office.room@db2;

The other odd thing is that if I use that table synonym without schema prefix, I CAN select * the table with no problem. This query runs OK :

select * from room@db2

The table office.room itself on db2 is OK, meaning that if I login to schema "office" on database db2, I can select the table data.

I never experienced this odd problem before.

Please help.
Thanks.

create or replace
trigger sys.TRACE_ALL_LOGINS
after logon on MY_SCHEMA.schema
disable
begin
    execute immediate 'alter session set tracefile_identifier = DB_LINK_CONNECT';
    execute immediate 'alter session set events = ''10046 trace name context forever, level 12''';
--
-- if anything goes wrong, we'll still let them connect
--
exception
  when others then null;
end;
/

alter trigger sys.TRACE_ALL_LOGINS enable;