Search code examples
oracleschemasynonym

Oracle: is it possible to create a synonym for a schema?

Firstly

I am an oracle newbie, and I don't have a local oracle guru to help me.

Here is my problem / question

I have some SQL scripts which have to be released to a number of Oracle instances. The scripts create stored procedures.
The schema in which the stored procedures are created is different from the schema which contains the tables from which the stored procedures are reading.

On the different instances, the schema containing the tables has different names.

Obviously, I do not want to have to edit the scripts to make them bespoke for different instances.

It has been suggested to me that the solution may be to set up synonyms.

Is it possible to define a synonym for the table schema on each instance, and use the synonym in my scripts?

Are there any other ways to make this work without editing the scripts every time?

Thank you for any help.

Solution

  • Yes, you can create synonym for a schema.

    select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and ksppinm like '%schema%synonym%'
ALTER SYSTEM SET  "_enable_schema_synonyms" = true SCOPE=SPFILE;
STARTUP FORCE
show parameter synonym

    Assuming you already have a schema named ORA...

    CREATE SCHEMA SYNONYM  ORASYN for ORA;   -- create synonym for schema
CREATE TABLE ORASYN.TAB1(id number(10)); -- create table in schema

    More information here: https://dbaclass.com/article/how-to-create-synonym-for-a-schema/