Automatically Create All Missing Synonyms in an Oracle Database

Automatically Create All Missing Synonyms in an Oracle Database

Synonyms are used in place of schema objects in professional database applications because the application user is typically different from the object owner schema.

The script below creates synonyms for all the relevant objects in your schema?if they dont exist automatically. This saves you from having to write hundreds of create synonym statements for the objects and from the possibility of forgetting statements which would then cause problems due to the missing synonyms.

declare   cursor cur_objects is select object_name , owner from all_objects where object_type in('TABLE','VIEW','SEQUENCE') and owner in('&&SCHEMA_OWNER'); /* this script will prompt you for the schema owner*/beginfor rec_objects in cur_objects loopbegindbms_output.put_line(rec_objects.object_name);execute immediate('create public synonym ' || rec_objects.object_name || ' for ' || rec_objects.owner ||'.'||rec_objects.object_name )exception when others thennull;end;end loop;end;/
See also  How to Avoid Money Transfer Scams  

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist