Creating Synonym for Schema(User)

TSharma-Oracle

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.

BPeaslandDBA

Which versions does this apply to?

Thanks,
Brian

TSharma-Oracle

Which versions does this apply to?

Thanks,
Brian

11g and 12c

Lothar Flatz

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

So this is a workaround for a mistake or typo?

Why not fix the mistake or typo?

Sven W.

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 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.

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

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

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

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.