|
|
|
|
|
|
| Author |
Message |
Proteus
*nix forums Guru Wannabe
Joined: 16 Jul 2005
Posts: 203
|
 Posted: Thu Feb 10, 2005 11:27 am Post subject:
Re: Renaming SCHEMA
|
|
|
"Norman Dunbar" <Norman@Dunbar-it.co.uk.REMOVETHIS> schrieb im Newsbeitrag
news:cuf73k$563$3$8302bc10@news.demon.co.uk...
| Quote: | 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  )
Cheers,
Norm.
|
Thank you! This might help me.
Robert |
|
| Back to top |
|
 |
Norman Dunbar
*nix forums beginner
Joined: 09 Feb 2005
Posts: 24
|
| Posted: Thu Feb 10, 2005 6:58 am Post subject:
Re: Renaming SCHEMA
|
|
|
Robert Sturzenegger wrote:
| Quote: | 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 )
Cheers,
Norm. |
|
| Back to top |
|
|
Proteus
*nix forums Guru Wannabe
Joined: 16 Jul 2005
Posts: 203
|
| Posted: Thu Feb 10, 2005 6:58 am Post subject:
Renaming SCHEMA
|
|
|
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 |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Jan 08, 2009 12:04 am | All times are GMT
|
|
Web Advertising | Guitar Lessons | Personal Finance | Online Advertising | Credit Cards
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
»
Server
