Generate Create Synonym Statements (create_synonym.gen)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This generator script creates CREATE SYNONYM statements for tables. It can generate either public synonyms or private synonyms owned by a specific user.

The Script

rem  create_synonym.gen
rem
select 'create ' || decode(upper('&public_private'), 'PUBLIC', 'public ', '') ||
       'synonym ' || decode(upper('&public_private'), 'PUBLIC', '', upper('&synonym_owner') || '.') ||
       table_name || ' for ' || owner || '.' || table_name || ';'
  from sys.dba_tables
 where owner like upper('&table_owner')
   and table_name like upper('&table_name')
   and owner not in ('SYS','SYSTEM','MDSYS','CTXSYS','XDB','ORDDATA','APEX_040200')
 order by owner, table_name
/

Usage

-- Basic usage
@create_synonym.gen

-- When prompted, enter:
-- table_owner: Table owner pattern (use % for wildcard)
-- table_name: Table name pattern (use % for wildcard)
-- public_private: PUBLIC or PRIVATE
-- synonym_owner: Owner for private synonyms (ignored for public)

Parameters

The script prompts for:

• &table_owner - Table owner pattern (use % for wildcard)
• &table_name - Table name pattern (use % for wildcard)
• &public_private - PUBLIC or PRIVATE synonym type
• &synonym_owner - Owner for private synonyms (only used if PRIVATE)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_TABLES
-- Plus CREATE SYNONYM or CREATE PUBLIC SYNONYM

Sample Output

-- Public synonyms:
create public synonym EMPLOYEES for HR.EMPLOYEES;
create public synonym DEPARTMENTS for HR.DEPARTMENTS;

-- Private synonyms:
create APP_USER.EMPLOYEES for HR.EMPLOYEES;
create APP_USER.DEPARTMENTS for HR.DEPARTMENTS;

Key Features

• Public/Private Support: Generate either type of synonym
• System Schema Protection: Excludes critical system schemas
• Pattern Matching: Use wildcards for bulk synonym creation
• Automatic Naming: Synonyms use same name as base table

Common Use Cases

Create Public Synonyms for Schema

@create_synonym.gen
-- Enter: HR for table_owner, % for table_name, PUBLIC for public_private

Create Private Synonyms for Application User

@create_synonym.gen
-- Enter: HR for table_owner, % for table_name, PRIVATE for public_private, APP_USER for synonym_owner

Related Scripts

• Grant Select Generator - Grant access before creating synonyms
• Table Analysis - View table information