This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,573 Users
  • 2,269,776 Discussions
  • 7,916,824 Comments

Discussions

Creating Synonym for Schema(User)

TSharma-Oracle
TSharma-Oracle Member Posts: 1,900 Silver Trophy
edited Jan 11, 2016 6:20PM in Database Ideas - Ideas

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.

TSharma-OracleArpit Jain -OracleborneselManish ChaturvediJitendractriebCallmeSoumyaCarsten Kaftan2640833RobertOrtelmarkmevanscarajandbberxLothar FlatzJagadekaraPravin Takpirevinaykumar2Zlatko SiroticbhagatsinghAish13sysassysdbaLudovicoCaldaraulohmannSven W.N.B.tonibony7Andreas HuberGbenga Ajakayebargenuser1149860
36 votes

Active · Last Updated

«1

Comments

  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    Which versions does this apply to?

    Thanks,
    Brian

  • TSharma-Oracle
    TSharma-Oracle Member Posts: 1,900 Silver Trophy

    Which versions does this apply to?

    Thanks,
    Brian

    11g and 12c

    getamy
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge
    edited May 11, 2015 3:41AM

    in the dwh area it is handy to have a schema switch. You have one schema that is currently updated by an ETL job, the other schema could be in use by query uses. Once the new data is completely load you switch the schemas.

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    So this is a workaround for a mistake or typo?

    Why not fix the mistake or typo?

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    So this is a workaround for a mistake or typo?

    Why not fix the mistake or typo?

    Top.Gun wrote:
    
    So this is a workaround for a mistake or typo?
    
    Why not fix the mistake or typo?
    

    How?

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown
    Top.Gun wrote:
    
    So this is a workaround for a mistake or typo?
    
    Why not fix the mistake or typo?
    

    How?

    If you made a mistake or typo, why not correct it before using it?

    ie addressing the root cause rather than throw in a work around.

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    If you made a mistake or typo, why not correct it before using it?

    ie addressing the root cause rather than throw in a work around.

    How do you correct a mistyped schema name? Do you understand what this topic is all about?

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    How do you correct a mistyped schema name? Do you understand what this topic is all about?

    Normally in a large enterprise environment there are tests and verification to prevent mistakes going into production.

    So maybe this is not a large enterprise environment and DBAs are throwing in stuff into production ad-hoc on the fly......

    Could be time that the DBAs have some process to follow to stop stupid mistakes going into production.....

  • Gerald Venzl-Oracle
    Gerald Venzl-Oracle Senior Principal Product Manager San FranciscoMember, Moderator Posts: 85 Employee

    As mentioned by the author of this idea: "This Feature is not officially supported."

    We advise NOT to set hidden parameters unless explicitly asked by Oracle Support.

  • bargen
    bargen Member Posts: 9 Blue Ribbon

    I can easily imagine a possible use-case - however, this doesn't work with the current (unsupported) implementation.

    The use-case is as follows:

    I'd like to have an IMPL schema which contains the data and logic without restrictions and an API schema which provides the interface to the user.

    The API schema basically contains views with instead-of triggers for the tables in IMPL and PL/SQL logic which checks the users access riights and then calls the corresponding logic in the IMPL schema.

    That is, the API schema is to implement very fine-grained access control and auditing features (note: row-level-security is sometimes not quite enough and it's expensive).

    OK, so USER -> API -> IMPL.

    At the user level, one can call ALTER SESSION SET CURRENT_SCHEMA=API.

    On the API level, however, one cannot alter the current_schema once more, so for accessing the IMPL objects the API logic has to use the IMPL prefix, e.g. SELECT ... from IMPL.EMP.

    Here, the IMPL prefix is hard-coded.

    In some environments, two or more instances of the same application reside in the same database, say test und production.

    In that case, one would have two ore more schema couples, say (API_TEST, IMPL_TEST) and (API_PROD, IMPL_PROD).

    If inside the API_TEST schema one could create a PRIVATE synonym IMPL for IMPL_TEST, this scenario would work.

    Unfortunately, with CREATE SCHEMA SYNONYM this does not work, because "there can be only one", to cite from the ancient Highlander film.

    BTW:

    CREATE SYNONYM SCOTTY for SCOTT;

    does not raise an error in Oracle 11 or 12, but it is just not useful.