Menu
I am an oracle newbie, and I don't have a local oracle guru to help me.
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.
548
You cannot create a synonym for a synonym in the same database. The identifier of the synonym must be unique among the names of tables, temporary tables, external tables, views, and sequence objects in the same database. (See, however, the section Synonyms with the Same Name.)
Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym. Synonyms provide both data independence and location transparency.
Notes. You must have the CREATE SYNONYM privilege to create a private synonym in your own schema, and CREATE ANY SYNONYM to create a synonym in another schema. You must have the CREATE PUBLIC SYNONYM privilege to create a public synonym.
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: http://oracle-network.com/database/how-to-create-synonym-for-a-schema/
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
