Oracle create private public synonym command

How to create synonym in Oracle 

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment.  

Private Synonym:

1. Login as scheman user under where should create synonym

2.  schemaname is equal The Schema name for example. 

     TEST01

     TEST02

The user schema TEST01 should get synonym from all tables unter the schema TEST02 located.

Login as  TEST01

sqlplus TEST01

set echo off
set verify off
set pagesize 0
set trimspool on
set headsep off
set linesize 100
set heading off
set feedback off
set timing off
set time off
set termout off
set headsep off
spool create_private_synonom.sql
SELECT 'create public synonym ' || object_name || ' for TEST02.' || object_name || ' ;' FROM dba_objects
WHERE owner = 'TEST02'
AND object_type IN ('TABLE', 'SEQUENCE', 'VIEW', 'PROCEDURE', 'PACKAGE');
spool off

Verify the script before you execute. Examples line

...
create synonym EMPLOY_HR for TEST02.EMPLOY_HR ;

...

Execute the script as follow.

sqlplus TEST01

SQL> @create_private_synonom.sql

Or use simple different way.

set echo off

set feedback off; 

set heading off; 

set term off

set pagesize 1000

set linesize 100

SET PAGES 0

spool create_synonom_table.sql

select 'create synonym ' || table_name || ' for ' || owner || '.' || table_name || ';'  FROM all_tables WHERE owner='TEST02';

spool off

select 'create synonym ' ||view_name || ' for ' || owner || '.' || view_name || ';'  FROM all_views WHERE owner='TEST02';

Public Synonym:

For Public synonym we need to just add the keyword public.

SELECT 'create public synonym ' || object_name || ' for TEST02.' || object_name || ' ;' FROM dba_objects

WHERE owner = 'TEST02'

AND object_type IN ('TABLE', 'SEQUENCE', 'VIEW', 'PROCEDURE', 'PACKAGE');

Your comments are most valuable and it would help me to come up with better posts.