Re: Renaming SCHEMA

"Norman Dunbar" <Norman_at_Dunbar-it.co.uk.REMOVETHIS> schrieb im Newsbeitrag news:cuf73k$563$3$8302bc10_at_news.demon.co.uk...
> Robert Sturzenegger wrote:
> > Is it possible, to rename a DB SCHEMA in an Oracle instance without
> > "export - import - drop" and if yes, how?
> >
> > Tanks a lot!
> > Robert Sturzenegger
> >
> >
> Hi Robert,
>
> ALTER SCHEMA old_name RENAME TO new_name;
>
> Only kidding - sorry - you don't have the ability (yet) to rename a
schema.
>
> However, you can create a new one with the desired name, and create a
> logon trigger which uses ALTER SESSION SET DEFAULT SCHEMA = old_name. I
> can't remember the actual command, but it's something like that.
>
> That will at least save you the trouble of export and import. Change the
> password on the old schema to stop any undesirables logging in directly
> and Bob's your uncle.
>
> Be changing the default scheme, Oracle will 'look' in new_schema for any
> unqualified objects that it can't find in new_schema. So, for example :
>
> old_schema.table_a
> old_schema.table_b
> old_schema.table_c
>
> new_schema.table_a
> new_schema.table_d
>
> If the query is on 'table_a' then 'new_schema.table_a' will be used.
> If the query is on 'table_b' then 'old_schema.table_b' will be used.
> If the query is on 'old_schema.table_a' then 'old_schema.table_a' will
> be used.
>
> And so on.
>
>
> I'm not 100% sure what will happen when you do an exp and imp of
> new_schema though - best tested first :o)
>
>
> Cheers,
> Norm.
>
>

Thank you! This might help me.
Robert Received on Thu Feb 10 2005 - 06:27:50 CST