|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to rename a user [message #463088 is a reply to message #463086] |
Tue, 29 June 2010 12:01   |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Does that cover synonyms?
Though to be honest you should have scripts for all this stuff in source control - in which case modifying said scripts for the new user shouldn't be all that difficult.
If you don't have the scripts in source control then maybe you should create them.
|
|
|
|
| Re: How to rename a user [message #463093 is a reply to message #463088] |
Tue, 29 June 2010 12:10 |
CajunVarst
Messages: 55
Registered: April 2010
Location: Washington, D.C.
|
Member |
|
|
Yep.
create table t1 as (select user c1 from dual);
Table created.
create synonym s1 for t1;
Synonym created.
select * from t1;
T1
----------
TESTUSER
select * from s1;
S1
----------
TESTUSER
select dbms_metadata.get_ddl('SYNONYM','S1') myDDL from dual;
DDL
----------------------------------------------------
CREATE OR REPLACE SYNONYM "TESTUSER"."S1" FOR "TESTUSER"."T1"
|
|
|
|
|
|
| Re: How to rename a user [message #463097 is a reply to message #463095] |
Tue, 29 June 2010 12:18 |
CajunVarst
Messages: 55
Registered: April 2010
Location: Washington, D.C.
|
Member |
|
|
you can run the query as sys and specify the user.
select dbms_metadata.get_ddl('SYNONYM','S1','TESTUSER') myDDL from dual;
If you have to, you can query the data dictionary to find the synonym owner and object owner.
Like I said, it takes some effort, but it can be done.
[Updated on: Tue, 29 June 2010 12:24] Report message to a moderator |
|
|
|
|
|
| Re: How to rename a user [message #463108 is a reply to message #463105] |
Tue, 29 June 2010 12:51 |
CajunVarst
Messages: 55
Registered: April 2010
Location: Washington, D.C.
|
Member |
|
|
to beat a dead horse:
select
'select dbms_metadata.get_ddl('SYNONYM','','''||SYNONYM_NAME||''','''||owner||''') from dual;' GETMYDDL
from dba_synonyms where table_owner='TESTUSER';
Will create select statements to get ALL synonyms, including public, for supplied user.
Where there is a will, there is a way. Even if it goes beyond the worth of the level of effort.
|
|
|
|
| Re: How to rename a user [message #464738 is a reply to message #463108] |
Fri, 09 July 2010 02:34 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I am stabbing in the dark here. That said, I did a simple GOOGLE and found these links on how to "clone a schema".
Burleson (not everyone likes this guy but you can't google Oracle stuff without hitting one of his pages) Note that he claims not to have ever used the script so who knows if it actually works, and also note that the date is 2007 so it might be too old anyway depending upon your version. Be careful with this one. Heck be carefull with all of these.
one of our own
A little commentary on the REMAP_SCHEMA option of IMPDB Note in particular the "might not do everything right" commentary; like the "won't find and fix references to embedded schema names in code". Jeesh... what good is it then I ask you. The one thing we really need and it won't do it for us. Then again, anyone who is hard coding schema names in their code deserves these kinds of problems. There are few reasons to hardcode a schema name in plsql and scripts. I have only really needed it for one thing in my career, for building historical aware applications. I use schema management as one of the linch-pins of my historical implementation strategy so when inside an app I want to view a current and historical perspective at the same time, I hard code the historical schema name. However, I have never used REMAP_SCHEMA in an effort to clone a schema so don't call me, I'll call you.
Cloning a schema, aka copying a user completely and catching everything was never easy.
Kevin
[Updated on: Fri, 09 July 2010 02:49] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
