Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Renaming SCHEMA

Re: Renaming SCHEMA

From: Robert Sturzenegger <nospam_at_nowhere.net>
Date: Thu, 10 Feb 2005 13:27:50 +0100
Message-ID: <cufk0b$egh$1@news.hispeed.ch>

"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US