How to Correct Invalid Synonyms
430280Jan 26 2005 — edited Jan 26 2005Hi All,
Before everyone jumps to conclusions, I am not a newbie and I am NOT getting "ORA-00980: synonym translation is no longer valid".
I am aware of and very familiar with scripts, like http://www.oracle.com/technology/oramag/code/tips2003/113003.html , that detect and drop synonyms that are pointing to objects that do not exist. That isn't my problem either.
My problem is that I have thousands on synonyms on my 10.1.0.3 database that point to valid objects but claim to be invalid (SELECT STATUS FROM DBA_OBJECTS). If I reference one of these synonyms (say by selecting from it)it becomes valid.
The Documentation, http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_2.htm#i2295590, claims that there is no "alter synonym" command. Yet garbage alter commands return:
ORA-00940: invalid ALTER command
While running "ALTER SYNONYM owner.synonym COMPILE;" as system returns: "ORA-01031: insufficient privileges"
So what is going on here? And how is a DBA supposed to make all of the synonyms valid?
Steven