How to determine synonym validity in SB?

Howdy,

We had a (planned) table drop and recreate in our Prod DB last night. Today, dev calls that they can’t access the table, getting:

ORA-04045: errors during recompilation/revalidation of MYSCHEMA.MYSYN
ORA-16000: database open for read-only access

The dev’s app is connected to our Active Data Guard physical standby that’s opened read-only. The app accesses the table via a local synonym, MYSCHEMA.MYSYN, which points to MYAPP.MYTABLE. Going into Toad 13.0.0.69 Schema Browser, Synonyms, everything looks fine, including data retrieval. After doing a “SELECT * FROM DBA_INVALID_OBJECTS” in the Editor, I see that the synonym is invalid, which makes sense from the app’s standpoint – the table was dropped and recreated, invalidating the synonym, but the app can’t validate the synonym from its connection on the read-only standby.

But in the SB, there’s no indication that the synonym is invalid. Clicking on the RHS Data tab, the SQL pulls directly from the synonym’s referenced object, instead of through the synonym. And there’s no “Valid” column in the LHS. Because the SB’s RHS Data tab SELECTs directly from the underlying object, the synonym will not validate. That made this a bit of a head-scratcher. Thankfully, my nails are trimmed, or I could have left gouges in my bald pate.

Any thoughts on making the validity of synonyms apparent in the SB?

Thanks!
Rich

Possible SQL recreation, adding a column to the underlying table (this is what our ERP does to add columns to tables):

CREATE TABLE MYAPP.MYTABLE (MYCOL1 NUMBER);

INSERT INTO MYAPP.MYTABLE (MYCOL1) VALUES (1);
COMMIT; --yes, the DCL that follows will implicitly commit. Do it anyway!

GRANT SELECT ON MYAPP.MYTABLE TO MYSCHEMA;

CREATE SYNONYM MYSCHEMA.MYSYN FOR MYAPP.MYTABLE;

DROP TABLE MYAPP.MYTABLE PURGE;

CREATE TABLE MYAPP.MYTABLE (MYCOL1 NUMBER, MYCOL2 NVARCHAR2(32));

INSERT INTO MYAPP.MYTABLE (MYCOL1, MYCOL2) VALUES (1,N’WHEEEEEE!’);
COMMIT;

GRANT SELECT ON MYAPP.MYTABLE TO MYSCHEMA;

Synonym MYSCHEMA.MYSYN is now invalid, but the SB won’t show it.

IMHO, validity of private synonyms and accessibility through them are two different points.

I can confirm that invalid PRIVATE synonym is perfectly functioning in Oracle, regardles invalid state.
It’s recompile, will meke it valid and usage would be the same.
So final point:

invalid private synonym may not confirm that synonym cannot be used.

Hey Rich,

We do list the invalid synonyms in SB-Invalid Objects. You can recompile from there too. Looks like Oracle won’t let me recompile another user’s synonym (not even one that I created!) This is Oracle 11 anyway. I didn’t try it in 12.

I don’t indicate it in SB-Synonyms because that would joining DBA_SYNONYMS to DBA_OBJECTS, which would be slow for some. I suppose I could run a separate query, but as Damir pointed out, a status of INVALID for synonyms doesn’t necessarily indicate a problem. Let me know if you’d like an indicator in SB-Synonyms, and I can do that once we get back into betas again.

-John

but as Damir pointed out, a status of INVALID for synonyms

Just a small correction:

  • invalid state for private synonym do not explicetly means that synonym is invalid.
  • invalid state for public synonyms, means that public synonym needs to be recompile explicitly. othervise it will not translate properly value underneath
    brg

Damir