So simple
Jay, February 28, 2002 - 4:05 am UTC
Brilliant. Exactly what I was looking for. My only problem is: why didn't any of the documentation that I looked at have this in it?
Anyway - thanks.
February 28, 2002 - 10:15 am UTC
You didn't read the SQL reference manual then?
The syntax for create synonym is:
create [PUBLIC] synonym [SCHEMA.]synonym FOR [SCHEMA.]object[@dblink]
Additionally the create synonym "prereqs" state:
Prerequisites
To create a private synonym in your own schema, you must have CREATE SYNONYM
system privilege.
To create a private synonym in another user?s schema, you must have CREATE ANY
SYNONYM system privilege.
Read:
</code>
http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem2d.htm#2060937 <code>
to verify that this is so....
Yeah, yeah
Jay, February 28, 2002 - 10:34 am UTC
Tom
I didn't really expect a follow up to that. I revisited the manuals armed with the answer and reverse engineered my interpretation of the command explanation. With that I realised my error. But had I found the answer on first pass of the manuals then I wouldn't have asked the question and consequently all your other readers would not have the potential benefit of your solution.
Cheers!
Private synonym Performance
nvssk, July 09, 2002 - 2:00 pm UTC
Hi Tom,
What is the impact on the performance, if we use private synonyms instead of schema.table ?
I have an oracle server with single instance having multiple schemas. Distributed option is enabled on this server.
If i want to access the tables in the same way as i do in my application, (schema.table name) from the other database server what options do i have?
Assumption is Many application instances(connected through database users) will have access to the second database server.
Can i achive the same through creating private synonyms for each user? OR any other options are there ?
July 10, 2002 - 6:58 am UTC
You can use private synonyms -- the overhead for them is marginal at best. Translation during parse, but not much else at all.
I sort of like views as well as they import the meta-data from the remote site to the local site, populating user_tab_columns and such.
Views vs private synonyms
nvssk, July 10, 2002 - 11:37 am UTC
Hi Tom,
Can you put light into these words.
"I sort of like views as well as they import the meta-data from the remote site to the local site, populating user_tab_columns and such. "
How can we achieve import data from remote to local online?
Is there an option available ?
Also our requirement is that we should maintain data at one place only, but should access from other server.
July 10, 2002 - 2:20 pm UTC
It imports the "meta data" about the remote object (not the data itself)
if you go:
create synonym foo for foo@remote_site;
select * from user_tab_columns where table_name = 'FOO';
you'll get no rows. but if you
create view foo as select * from foo@remote_site;
select * from user_tab_columns where table_name = 'FOO';
you'll get rows (and rows in the user_tab_columns and such as well).
That is what I meant.
To actually "copy" or import the data, just
create table local as select * from foo@remote;
but that is not what you wanted. So, this is just the difference between a synonym and a view -- a view imports the META data, a synonym does not.
which is best view of synonym
Habibulla, September 01, 2003 - 9:00 am UTC
To refer tables of another schema, which is best?
VIEW of SYNONYM
Is there any difference in performance point of view.
(I cannot user alter.. currentschema, since multiple tables referred in queries from multilple schemas)
Pl advise.
September 01, 2003 - 9:27 am UTC
trick question -- you did not include the obvious answer -- use the schema name.
I prefer views myself when forced.
I have the same situation - can you help please ?
Sonali, October 03, 2003 - 10:49 am UTC
We have 2 schema's (we are trying to integrate these 2 application schemas) both have tables, triggers, procedures etc.. I cannot use alter schema, as we need to access tables from both, as you suggested many times synonynms are bad, we would use views with create view as schema.obj_name.. This should be fine except when I have following situation...
Say
I have 4 schemas on one database, they are called prod, prodback, dev, devback.
I have some triggers on prod schema, which have hardcoded references to prodback schema. But then I backup and restore prod to dev schema. After I do this, the dev schema now has hardcoded triggers with references to prodback instead of devback.
To fix this I have to manually change all these hardcoded values in the dev schema to point to devback.
I will have same issues with views and procedures...
Is there a function I can write which will return a shema name ( I can hard code it here, but it will be only one place).. which then I call in all these triggers, views, procedures to get the schema name as varaible and
then do something like this -
insert into fun_devback_name.work values(x,y,z);
instead of manually changing it all over ?
Thanks
Sonali
October 03, 2003 - 11:04 am UTC
you need 2 databases -- you cannot run prod and dev in the same instance -- that would be what one might term "a really utterly hugely bad idea".
Yes, agreed
sonali, October 03, 2003 - 11:47 am UTC
Suppose I have 2 databases, but still one can have different schema names in both.. then whaT ? How do I cross reference 2 schemas ? I have written this as an example of what the customers can do and have... I wanted to have simple export import process for this and not ask them to manually edit all the triggers, views and tables in both schemas that are cross referenced.. this can be a disaster if they don't do it right...Can I use sys context ? Can I write a function to get the schema name and use it in all of these, so they will have to change the schema name only in this function..
Thanks
Sonali
October 03, 2003 - 12:03 pm UTC
you would not have different schema names
or you would be forced to use synonyms.
so, i guess you are forced to either use VIEWS or SYNONYMS that you point to the right objects from each schema.
Is there any routine I can write
sonali, October 07, 2003 - 10:51 am UTC
Okay I get, I need to use views.. that would be best then..
Is there any script I can write that will check for the schema name in say dba_objects and replace the hard coded values for the schema references ?
Thanks
Sonali
October 07, 2003 - 10:56 am UTC
not that i am aware of.
what you can do is write your installation scripts to be parameterized
create view v
as
select * from &owner1..table1, &owner2..table2
where.....
so they prompt you for the schema names upon install.
Mark, April 25, 2007 - 1:15 pm UTC
Tom,
Any way to create a private DBLINK for another schema?
When we reload dev from production, we need to rebuild all db links to point to the DEV targets.. I would like to be able to script it out without having to connect to each schema..
Thanks...
April 25, 2007 - 1:39 pm UTC
Mark, April 25, 2007 - 2:43 pm UTC
How does Import util do it?
I connect as user with DBA role and import other users. The DB links are created under that user. How do they do it?
Is there a SWITCH_USER or something...
Thanks...
April 25, 2007 - 3:59 pm UTC
import becomes other users. it is an internal function, not exposed.
Acess to objects of one schema to multiple schemas
R, October 12, 2011 - 8:24 am UTC
Hi Tom,
In our 11g enterprise DB we are implementing peoplesof, we have around 25,000 tables and 17,000 views, say in schema A.
Now, I need to create multiple users who needs access to all those objects in schema A. Below is what I am planning to do, there will be objects added to schema A in the future and I want to automate the process of other users being able to select on those objects(tbles, views) automatically.
1. Create public synonyms on all obects in schema A.
2. Create required users
3. Create a role say R
4. Grant select on all the objects in schema A to R
5. Assign R to users.
6. create a "after CREATE" trigger in schema A, which will grant select on the newly created object to role R.
I want to know if there is a better alternative than this, if there is please advise.
Thanks...
October 12, 2011 - 10:16 am UTC
DO NOT DO STEP 1.
Please do not do that.
either:
a) use the schema name when referencing the objects, this is my preference by far.
b) have your other users issue: alter session set current_schema=that_schema - so that select * from t will default to that_schema.t instead of USER.T. Use a logon trigger if you want to set this default schema
but please do NOT use public synonyms. They represent a certain security risk (very easy to repoint them to something else - something malicious). They represent a performance overhead you don't need. They represent massive confusion. They represent a limited namespace - if an object by that name already exists - well - bummer.
2, 3, 4, 5 - ok
6, I'm not a fan of it. Trigger trickery is something I don't really like. Too many "and magic happens here". You would have to use dbms_job (not the scheduler, scheduler commits) to schedule a job to run after the create is done (you cannot do the grant in the create trigger - the object doesn't really exist officially yet). It would be messy - it would be magic.
And it would represent a potential "you just broke my upgrade" point of contention with the 3rd party vendor as well (they don't like you creating triggers on their stuff).
As you do not upgrade or patch all that often, I personally do not see an issue with just having a script you run after the upgrade to grant select on the tables/views to the role once again.
Acess to objects of one schema to multiple schemas
R, October 12, 2011 - 11:39 am UTC
Thanks Tom,
Ok. I agree with you on "1." above, but about "6." ss there a better way to automate the select on tables, views created by users.
Let me explain how this works. Nature of the peoplesoft application is that when ever ANY user creates an object(Table, view is what I am concerned with right now) in the DB using som peoplesoft tool, it always creates the object a particular shcema, in our case shcema A. Once this objects is created in A, all the other users, including the one who created it should be able to select from that object.
Thanks...
October 12, 2011 - 12:10 pm UTC
Ok. I agree with you on "1." above, but about "6." ss there a better way to
automate the select on tables, views created by users.you asked that initially, and I delivered my opinion. I'm simply not a fan of triggers and there isn't any "grant select any table on this schema" (well, database vault can help you there if you are interested in looking at that - an option to the database)
You can do the trigger, but do be prepared for potential issues during upgrades, for some amount of confusion by the people using and managing the database over time (it is not expected behavior - and before you say "that'll never happen", let me tell you how many questions/emails I get showing that it will happen...) and so on.
use dbms_job, use dbms_job wisely (bind friendly)
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:146612348066#1694448000346023358
A possible alternative?
djb, October 12, 2011 - 2:21 pm UTC
Tom, what would you think of this:
SCHEMA_A has all the objects you want USER_X to access (in a limited fashion)
Create SCHEMA_B, and grant the proper privileges from SCHEMA_A to SCHEMA_B
Create private synonyms in SCHEMA_B to point to the objects in SCHEMA_A
Grant a proxy connection on USER_X to SCHEMA_B (I think it would be a nice feature to allow proxy connections via a role).
October 12, 2011 - 3:43 pm UTC
why would you need private synonyms?
If you use a proxy user connection - you'll be logged in as schema_x after you identify yourself as schema_b.
It would work, but it would work very differently than they requested to work. Everyone would ultimately be logged in as user_x - and would be able to not only select from the tables, but update them, drop them, create new ones, etc. Probably not desirable in this case.
Getting data from serial ports using OraForms10g
Olivares, October 12, 2011 - 10:07 pm UTC
I need to know how to get data from rs232 (serial port) using Oracle Forms 10g.
If you have a sample please indicate me how to do it.
Thanks
October 13, 2011 - 7:25 am UTC
I have no idea.
Synonym for a schema
bijunator, November 29, 2012 - 10:47 am UTC
Hi Tom,
Apologies for being naive, but I had an argument with one of my friends. He said that we can create synonym for a schema i.e.
create or replace synonym my_dummy_schema for my_actual_schema;
I was logged in as my_user with create synonym privilege and executed the DDL. Oracle gave feed back that synonym created. I was stunned. Is it possible? Or is it that Oracle assumes my_user has a table or an object called my_actual_schema and created a synonym for that.
Thanks a lot for your time. Big fan of yours :D
November 30, 2012 - 4:02 am UTC
you can create a synonym for "anything" - any word that is an identifier - but it doesn't mean it will work in real life. for example:
ops$tkyte%ORA11GR2> create or replace synonym foobar for santa_claus;
Synonym created.
there is no Santa Claus in my database....
but the synonym exists
so, what happens if you do it for a 'schema'?
ops$tkyte%ORA11GR2> create or replace synonym foobar for scott;
Synonym created.
ops$tkyte%ORA11GR2> select * from foobar.emp;
select * from foobar.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
in short, nothing. it is NOT a synonym for a schema - we can see scott.emp exists:
ops$tkyte%ORA11GR2> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
see
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7001.htm#SQLRF01401 it lists what synonyms can be used for
Synonyms
Christos, March 13, 2014 - 2:21 am UTC
HEllo
1)I have user <admin> which has the privilege of creting synonym.
2) I have users under the schema <admin>. for example <manager>
I create a synonym with admin (create synonym customers for admin.customer). The synonym is created. But then when i login as <manager> and do select * from customers it comes back that table or view cannot be found.
I would use some help..
Kind regards
Chris