Re: How to rename a schema

Joel Garry wrote:
>
> "Timmy Sin" <tswsin_at_yahoo.com> wrote in message news:<b59gr0$f51$1_at_news.hk.linkage.net>...
> > Hi All,
> >
> > What is the fastest method to rename a schema with public synonym? The
> > scenario is as follows.
> >
> > I have a database named dev1 with schema dev1owner. This schema owns all the
> > tables. View and public synonym are created for other schema to access the
> > tables.
> >
> > Another database dev2 is cloned from dev1. Now I want to change the schema
> > dev1owner in the new database to dev2owner.
> >
> > Grateful if someone can give me some advice.
> >
>
> Besides what others posted, I'd add using public synonyms is probably
> a bad idea. You might want to consider writing scripts to generate
> private synonyms and appropriate grants for the other schemata.
>
> You might also want to write a view that contains simply the schema
> name.
> > Many thanks.
> >
> > Regards,
> > Timmy
>
> jg
> --
> @home is bogus.
> And don't try to migrate grants and synomyms with a gui tool.

Just to add you what Joel has said, you could also mess about with

alter session set current_schema = ...

using a logon trigger to possibly avoid the synonym overhead

hth

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"