Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Creating Synonym for Schema(User)

TSharma-OracleFeb 20 2015 — edited Jan 11 2016

This would be really handy if you do a mistake or typo with a schema name and it started being heavily used.

You cannot make a synonym for any schema in normal scenario. The only way to create a synonym for a schema is to modify the hidden undocumented parameter. The name of the parameter is "_enable_schema_synonyms" and its default value is FALSE. You can find this with the following query;

select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and ksppinm like '%schema%synonym%';

To change this parameter value from FALSE to TRUE:

SQL> ALTER SYSTEM SET  "_enable_schema_synonyms" = true SCOPE=SPFILE;

System altered

Shutdown and restart or do startup Force after changing this parameter.

Now Lets say you have a schema named TEST and you want to create synonym for that schema TESTSYN

SQL> select user#,name,type#,ctime from sys.user$ where name like 'TEST%';

  USER#      NAME      TYPE#      CTIME

----------      -------------      ----------      ---------

  100          TEST           1           20-FEB-15

Note above , the type = 1. Now lets create a synonym for user TEST

SQL> CREATE SCHEMA SYNONYM  TESTSYN for TEST;

Schema synonym created.

SQL> select user#,name,type#,ctime,spare2 from sys.user$ where name like 'TEST%';

  USER#       NAME      TYPE#      CTIME      SPARE2

----------           ----------      ----------      ---------      ----------

  100           TEST           1        20-FEB-15

  101           TESTSYN    3        20-FEB-15   100

Note that for TESTSYN , type =3 which means its a schema synonym and if you see spare value which is 100 means TESTSYN is pointing to user# 100 which is user TEST.

Now if you want to create any table using the schema name , YOU CAN.

For Ex:

CREATE TABLE TESTSYS.TAB1(id number(10));

This table will be actually created in TEST schema. Its just using TESTSYS as a synonym.

This is not renaming a schema name but can be helpful where you mistype the schema name and realize it later when whole world starts using it.

This Feature is not officially supported.

Comments
Post Details
Added on Feb 20 2015
11 comments
15,440 views