Automatically Create All Missing Synonyms in an Oracle Database

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*/

begin
for rec_objects in cur_objects loop
begin
dbms_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 then
null;
end;
end loop;
end;
/