PUBLIC SYNONYM value updated but not the mapped physical table

[redbadgers]

Hi!! I may have a server configuration issue here since it only happened to one of our Oracle 11.1.0.7 Servers but not the other Servers at the exact same level.

We use PUBLIC SYNONYM to update table data. On this particular Oracle 11.1.0.7 Server, the UPDATE operation was successful and we could see that values was modified when querying by the synonym name. But if we followed the alias to the physical table and queried <table_owner>.<table_name>, then it showed the old values.

It may have to do with the flashback feature, but this 11.1.0.7 server has the out of box configurations, identical to some of the other 11.1.0.7 servers. The only difference is that this Server is on Solaris 10. The others are on different Windows and Linux platforms.

Has anyone seen this behavior on an Oracle 11.1.0.7 on Solaris 10?
Thank you.
Regards, Nancy

 

[Thargy]

Ok,
I'll start with the easy one, were the changes in the session using the synonym committed? If not, then naturally they would be unseen in a different session, such as one accessing the table directly.

Regards

T

 

[redbadgers]

Yes, there was a commit. That's why I was so confused. It only failed to propagate the change from SYNONYM to the physical table on this one particular Oracle 11.1.0.7 Server on Solaris 10.

Originally our DBA said Oracle has changed SYNONYM and roles in Oracle 11g, but I proved everything was working as expected on other Oracle 11g Servers on other OS.

It was as if there is a temporal shift on this Oracle server.

I would like to know if this is considered a feature or a bug. And better yet, if anyone else has seen it on their Oracle 11g.

Thank you.
Regards, Nancy

 

[Thargy]

I find it hard to believe that Oracle have changed synonyms and roles, as backward compatibility is usually maintained, with almost religious fervour. As a full-time DBA I strive very hard never to BS developers and always check things.

Anyway, if the commit was not the issue then clearly the SYNONYM must be pointing to a different table. If a select * from the synonym gets different rows that selecting directly from the table then I suspect the two are different.

Is FGAC in use and restricting data access behind the scenes? Are privileges to view the table being awarded by some other role? Is the db identical to all others of its version? If yes, how has this been determined?

I'm basically saying that you should be suspicious and look for unexplained differences here.



Regards

T

 

[redbadgers]

Hello, T,
I was using SYSTEM user when updating through SYNONYM. Yes, there were a role granted to modify this table and another role granted to select from this table, but user SYSTEM who has DBA role should be able to do anything on this table. The fact that same SYSTEM user can update and see the result on other Oracle 11g servers proved the rules for the roles did not change in this aspect.

We are looking into Flashback too but could you give me some pointers what to look for? Our Oracle DBA is on vacation this week. I'm afraid I will have to investigate this flashback all by myself. Any pointers are very much appreciated.

Regards, Nancy

 

[Thargy]

Nancy,

there is unfortunately such a thing as having too many privileges. As a DBA, I am a member of the DBA group on our servers, which means that I can log in as sysdba without passwords etc. This means that I can update tables and do whatever I want.

This can cause confusion, because you can do things that apparently you ought not to. To eliminate system from the difficulties, log in to the schema using the username and password of the schema owner, and not as system or sysdba. Then from within that schema, and using that account, see if the synonym gives different results.

As an example, if you select table_name from user_tables when logged in as say SCOTT/TIGER, you just see the tables in the SCOTT schema. If you log in as SCOTT/DUDPASSWORD AS SYSDBA, you may still gain access. However, if you then run the same query as sys, you see all the tables, because for SYS, USER_TABLES is the same as DBA_TABLES. This might be what's happening to you.

I don't think I explained myself very well there, so if anyone else can give a better example, please do so.

Regards

T

 

[redbadgers]

Thank you, T. Your concerns were perfectly valid; one should not abuse the usage of SYSDBA role.

That being said, our DBA admitted Flashback feature has been enabled on this server while the other Oracle 11g servers had out of the box configurations. He and I are going to find out whether if we should disable this feature since we need to use this Oracle Server for both functional tests and for performance tests.

Thanks again for the reminder and Merry Christmas.

Regards, Nancy

 

[Thargy]

Nancy,

from my admittedly limited experience, I don't believe that flashback is the culprit, unless someone is doing a query with "as of" in it somewhere, and that's altering the returned row count.

Flashback on is ok for purely functional testing, but for performance, it should be off. After all, you wouldn't run a production box with flashback would you? And I presume that you want to test roughly what prod will be like on your test box.

To try it, log in as sys, and

shutdown immediate;
startup mount;
alter database flashback off;
alter database open;

Run your tests, then when you've had enough

shutdown immediate;
startup mount;
alter database flashback on;
alter database open;

Let me know how you get on.

Regards

T

 

[Beilstwh]

If you committed then you have to be pointing to another table when your logged onto system. Run the following queries as the system user.

select table_owner,table_name,db_link,OWNER
from all_synonyms
where SYNONYM_NAME = 'MY_TABLE_NAME';

select table_name
from user_tables
where table_name = 'MY_TABLE_NAME';

A public synonym will have PUBLIC as owner. a local synonym in system would have SYSTEM as the user. A local synonym will always override a public synonym. A local table will always override a synonym.

Bill
Lead Application Developer
New York State, USA

 

[Beilstwh]

By the way... NEVER, NEVER use SYS or SYSTEM for production. They are special accounts for the use of the oracle database ONLY.

Bill
Lead Application Developer
New York State, USA