Re: How to share a schema with other users?

On Sun, 14 Jul 2002 14:19:35 +0800, Benjamin Lai <ywlaiben_at_so-net.com.hk> wrote:

>Thank you for the hint Sybrand!
>
>I've looked up the documentation for the use of
>synonyms. I think what I can do is:
>
>1] connect as a dba
>2] create synonym <schema1>.<table_name> for <schema2>.<table_name>;
>
>Am I right?
>
>It seems I have to repeat this procedure for every single
>table I have created. Is there really a once-for-all method to achieve
>the same effect ? I really want to share the whole schema instead
>of the tables one by one. Hope that you don't feel I am too demanding.
>I just feel that this is a common problem that many DBAs have
>experienced.
>

You can't share schemas by configuring something at the schema level. You can however run the following piece of code (assuming 8i)

define fromuser = '&1'
define touser='&2'
begin
for r in (select owner, table_name from dba_tables where owner ='&fromuser') loop
execute immediate 'drop synonym &touser..'||r.table_name; execute immediate 'create synonym &touser..||r.table_name||' for '||'&fromuser..||r.table_name;
end loop;
end;
/
and you should be set up

That is relatively easy,isn't

hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Jul 14 2002 - 06:08:33 CDT