Establish Synonyms (Oracle)

If you have multiple users logging into multiple tablespaces, you must use the AfmoHcn_Synonyms.sql script to make certain they are accessing the right tables.

In the AfmoScm7/8.dmp and AfmoHq7/8.dmp files, public synonyms are created for each table owned by the user "AFM". When you import the .Dmp file into a new tablespace, the tables are owned by the new user, but the existing public synonyms still refer to the tables in the "AFM" tablespace.

By creating private synonyms, the AfmoHcn_Synonyms.sql script makes the synonyms reference the tables owned by the current user. All usernames (excluding the schema owners which default to the new tablespace) need to have private synonyms to access the new tablespace. For example, if NEWUSER is a new user accessing the HCN_P1 tablespace, private synonyms to the HCN owned tables should be granted to NEWUSER.

To establish these synonyms:

  1. Edit the start of the databases\Oracle\AfmoHcn_Synonyms.sql script from the DVD to reflect the user account.

CONNECT SYSTEM/MANAGER@wgs_DATASERVER_ORCL;
SPOOL SYNONYMS.LST
CONNECT NEWUSER/NEWUSER@wgs_DATASERVER_ORCL;

  1. Use the search and replace feature of your text editor to reflect the tablespace that owns the table.

CREATE SYNONYM AC FOR HCN.AC

  1. Use the following command line to execute the Synonyms.sql script file.

sqlplus @AfmoHcn_Synonyms.Sql