Re: Renaming SCHEMA

From: Norman Dunbar <Norman_at_Dunbar-it.co.uk.REMOVETHIS>
Date: Thu, 10 Feb 2005 08:52:23 +0100
Message-ID: <cuf73k$563$3$8302bc10@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. Received on Thu Feb 10 2005 - 01:52:23 CST