ORA-00995
ORA-00995 means that the synonym name you specified in the statement is either invalid or not found, you should make sure the synonym identifier is valid and correct.
Let's see some error patterns of ORA-00995.
1. CREATE SYNONYM
Tried to create a private synonym, but it failed with ORA-00995.
SQL> create synonym 2022_countries for hr.countries;
create synonym 2022_countries for hr.countries
*
ERROR at line 1:
ORA-00995: missing or invalid synonym identifier
Synonym is a type of schema object, its naming rule should follow Oracle's Database Object Names and Qualifiers. For a non-quoted identifier, it must begin with an alphabetic character. Furthermore, it's safer to limit the length of identifier to 30 bytes.
Solution
In this case, we moved digits to the last of the synonym name in order to align with the naming rule for non-quoted identifiers.
SQL> create synonym countries_2022 for hr.countries;
Synonym created.
As for differences between quoted and non-quoted naming rules, we have talked a lot in How to Resolve ORA-00904 Invalid Identifier.
In summary, an invalid column name results in ORA-00904, whereas an invalid synonym name results in ORA-00995.
If you insist to use an identifier beginning with digits or containing special characters, you should use double quotation marks to enclose the name.
SQL> create synonym "2020_countries" for hr.countries;
Synonym created.
Since you need more attention and effort to manipulate them carefully, quoted identifiers should be created in special cases with enough reasons to do that.
2. ALTER SYNONYM
Tried to alter a private synonym, but it failed with ORA-00995.
SQL> alter synonym 2020_countries compile;
alter synonym 2020_countries compile
*
ERROR at line 1:
ORA-00995: missing or invalid synonym identifier
Solution
As I said earlier, quoted identifier should always be sued as quoted.
SQL> alter synonym "2020_countries" compile;
Synonym altered.
3. ALTER PUBLIC SYNONYM
Tried to alter a public synonym, but it failed with ORA-00995.
SQL> alter synonym public.paybill compile;
alter synonym public.paybill compile
*
ERROR at line 1:
ORA-00995: missing or invalid synonym identifier
In Oracle, PUBLIC is a very special group and it's not a valid schema name, it can only be use to create only PUBLIC SYSNONYM and PUBLIC DATABASE LINK objects for every user to access.
In other words, we cannot treat it as a normal schema to compile it, so using the concatenation of schema name and identifier as usual to specify a public object does not work here.
Solution
To properly mange public's objects, we should use the right syntax specific by explicitly declaring it.
SQL> alter public synonym paybill compile;
Synonym altered.
The correct syntax to compile a public synonym is ALTER PUBLIC SYNONYM statement.