********************************************
* Description: Creating synonym of a schema
* Compatiablity: RDBMS 11g, 12c
* Date: 11:24 AM EST, 03/28/2017
********************************************


<1> General - Schema synonym is fit for the case that schema name got mistype, and realize that later whole world is using that heavily.
              The feature has to be actived by updating one Oracle hidden parameter "_enable_schema_synonyms", which is undocumented officially.
			  
	
	
<2> Checking current value of target hidden parameter:
     |
     |__ SQL> select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and ksppinm like '%schema%synonym%'
      
                 KSPPINM                        KSPPSTVL
                 ----------------------------- ----------
                 _enable_schema_synonyms        FALSE	
				 
				 
				 
<3> Enable the feature by setting up hidden parameter:
     |
     |__ SQL> ALTER SYSTEM SET "_enable_schema_synonyms"=true SCOPE=SPFILE;			 
	 


<4> Bouncing the database:
     |
     |__ SQL> shutdown immediate;
         SQL> startup;

		 

<5> Verify parameter value got updated afterwards:
     |
     |__ SQL> select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and ksppinm like '%schema%synonym%'
      
                 KSPPINM                        KSPPSTVL
                 ----------------------------- ----------
                 _enable_schema_synonyms        TRUE


				 
<6> Create schema synonym:
     |
     |__ SQL> CREATE SCHEMA SYNONYM  TEST_SYN for TEST;
	 
	 

<7> CAUTION: in the future, if any object created under "TEST_SNY", and object actually exsits under TEST. Additionly, "TEST_SYN" can not log into database. 
     |			 
     |__ SQL> select user#,name,type#,ctime,spare2 from sys.user$ where name like 'DEMO%';
               
                   USER#    NAME       TYPE#     CTIME     SPARE2
                 ------- ---------- ---------- --------- ----------
                    111     DEMO         1     09-NOV-14
                    159     DEMO2        3     28-DEC-14     111