die Seilerwerks

Something new I found out today. When performing an ALTER TABLE statement (in this case to add a column) in another schema for which you have a synonym (public or private), you must refer to it with the schema qualifier, e.g. HR.EMPLOYEES rather than just EMPLOYEES. It seems the DDL statement doesn’t bother itself with synonyms. Probably a good thing, as DDL is nothing to be taken lightly and it’s best to fully spell out what it is you are mucking about with.

In the example, my user has been granted the ALTER ANY TABLE privilege, as well as SELECT on HR.EMPLOYEES. I’ve made a private synonym in my own schema, also named EMPLOYEES. Let’s see what happens:

SQL> alter table employees add foo number;
alter table employees add foo number
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> alter table hr.employees add foo number;

Table altered.