Creating Numerous Synonyms in a Single Bound
Another tedious and exhausting task is creating numerous synonyms, whether they are public or private. Only a DBA or a user who has the CREATE PUBLIC SYNONYM privilege can create public synonyms, but any user can create private synonyms.
The following example creates public synonyms for all tables owned by RYAN:
SQL> SET ECHO OFF SQL> SET FEEDBACK OFF SQL> SET HEADING OFF SQL> SPOOL PUB_SYN.SQL SQL> SELECT 'CREATE PUBLIC SYNONYM ' || TABLE_NAME || ' FOR ' || 2 OWNER || '.' || TABLE_NAME || ';' 3 FROM SYS.DBA_TABLES 4 WHERE OWNER = 'RYAN' 5 / CREATE PUBLIC SYNONYM ACCT_PAY FOR RYAN.ACCT_PAY; CREATE PUBLIC SYNONYM ACCT_REC FOR RYAN.ACCT_REC; CREATE PUBLIC SYNONYM CUSTOMERS FOR RYAN.CUSTOMERS; CREATE ...
Get Sams Teach Yourself SQL in 21 Days, Fourth Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.