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.