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;/
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: