9

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.

Community
  • 1
  • 1
AJ.
  • 13,461
  • 19
  • 51
  • 63

3 Answers3

5

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/

rajkishore patro
  • 78
  • 1
  • 5
  • If possible add relevant text along with link. As link may change in future – Panther Aug 10 '15 at 16:58
  • 1
    **since 12c!** http://blog.dbi-services.com/create-schema-synonym-in-oracle-unsupported-feature/ – Andreas Covidiot Apr 07 '16 at 08:39
  • Please put all relevant content on this post so that when the article disappears some context of the answer remains available to future visitors. - The link here has died. – jcolebrand Oct 21 '20 at 21:32
2

Yes, there is a hidden way to create a schema synonym.

There is a hidden parameter _enable_schema_synonyms. It's false by default , but you can set it to true and create a synonym.

Barett
  • 5,826
  • 6
  • 51
  • 55
Dmitry
  • 21
  • 2
  • **since 12c!** http://blog.dbi-services.com/create-schema-synonym-in-oracle-unsupported-feature/ – Andreas Covidiot Apr 07 '16 at 08:38
  • **but they are global :-/, so you can't have like-named synonyms in different schematas**, e.g. having a mixed prod/test instance with schemas *customer_mgmt_prod*, *customers_mgmt_test*, *products_mgmt_prod*, *products_mgmt_test*, with "localized synonyms" *customers_mgmt* and *products_mgmt* which should refer to the corresponding *test* or *prod* schema. – Andreas Covidiot Apr 19 '16 at 06:37
2

It'd help to know what version of Oracle, but as of 10g--No, you can't make a synonym for a schema.
You can create synonyms for the tables, which would allow you not to specify the schema in the scripts. But it means that the synonyms have to be identical on every instance to be of any use...

The other option would be to replace the schema references with variables, so when the script runs the user is prompted for the schema names. I prefer this approach, because it's less work. Here's an example that would work in SQLPlus:

CREATE OR REPLACE &schema1..vw_my_view AS
  SELECT *
    FROM &&schema2..some_other_table

The beauty of this is that the person who runs the script would only be prompted once for each variable, not every time the variable is encountered. So be careful about typos :)

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Variables sound like a very sensible option. Thank you very much for the suggestion. I'll give it a try! – AJ. Sep 23 '10 at 16:10
  • 1
    To make this work, you need the references to be `&&schema1..vw_my_view`. Periods are used to indicate the end of variable names by default, so you need two when using them for schema: one to indicate the end of the variable and a second to act as the schema/object separator. – Allan Sep 23 '10 at 16:11
  • @Allan: Updated, I don't remember that being an issue on 10g but I also wasn't using them for schema placeholders. – OMG Ponies Sep 23 '10 at 16:14