Database Reference
In-Depth Information
Once the synonym exists, you can change the table it references by using
the CREATE OR REPLACE form of the CREATE SYNONYM com-
mand. For example, if you had misspelled ARTIST in the previous com-
mand, you could fix it by running the following command:
CREATE OR REPLACE PUBLIC SYNONYM CD FOR MUSICCD;
Note:
The act of creating a public synonym does not actually give access to
the underlying table's data. Although the synonym is available to all public
users, the underlying table is not available, unless specifically granted to a
user or a role. Granting privileges is covered in Chapter 23.
22.2.2
Creating Private Synonyms
To create a private synonym, simply create a synonym as before, excluding
the PUBLIC keyword, as shown in the following command. Note that if
you attempt to create a synonym called ARTIST, Oracle Database will
return an error because you cannot create a synonym with the same name as
an already existing object, the ARTIST table.
CREATE OR REPLACE SYNONYM MYARTISTS FOR MUSIC.ARTIST;
The most common use for private synonyms is to create a synonym for a
table in another schema. If the table does not have a public synonym, and
you use it in queries, you must include the schema name and the table
name. Creating a private synonym is like creating an alias that you can use
instead of the full schema and table name in your own queries. For exam-
ple, you can create a private synonym for an object (in this case, for a public
synonym) with a long name, as shown in the following command:
CREATE SYNONYM MYCOLS FOR USER_TAB_COLUMNS;
22.2.3
Using Synonyms
We have already created a private synonym in the MUSIC schema called
MYARTISTS, a synonym for the ARTIST table. Let's prove that it works.
The following commands should suffice. See the result in Figure 22.9.
--Select from the ARTIST table
SELECT ARTIST_ID, NAME FROM ARTIST WHERE NAME LIKE '%u%';
