hi everyone,

I'm not sure whether this is the right NG for my questions.
If not, please let me know which NG would be the better one.

My Q:
We have about 20GB of data in one schema and we want to move this
data to another schema on the same db.

Is there a quick way to change the owner of all these objects?
(Quick way might be w/o exporting all data and importing into the new
schema)

E.g. To change the owner of those objects in the data dictionary. Or that
not a good idea to do so (if not, why?).

We running Oracle 9i V.9.2 on Windows platform.

Any help much appreciated.


TIA
Fred

"dbyy" <noname (AT) fooled (DOT) com> wrote

Read about transportable tablespaces. This might help you. You just export
the metadata and import the metadata back with fromuser = and touser= . I
hope the objects for the schema are in separate tablespaces away from other
schema objects.

On 2004-12-10, dbyy <noname (AT) fooled (DOT) com> wrote:
Good question:-)

Proper way of doing that - yes, it is imp/exp
However, it is not always suitable in real life
So,
- create new_user
- alter user current_owner account lock
- create synonyms in new_user schema pointing to current_owner objects
(obviously, by script - something like
select 'create synonym dd2.'||object_name||' for dd1.'||object_name||
';' from dba_objects where owner='DD1'

Your idea about renaming user by updating data dictionary table directly
is a VERY bad. You can do that and it *may* work (for a while), and then
something will be broken beacuse of internal unintegrity of data in DB Dict

Never update data dictionary directly

Have a great day!

dbyy <noname (AT) fooled (DOT) com> wrote in
news:Xns95BAC31AFB91Ddbyyatcanadadotcomni (AT) 207 (DOT) 35.177.134:

I think I found the answer already (here and on AskTom) the only way will
be either exp/imp or create table from.....

I just want to make sure I don't miss any other opportunity.

TIA
Fred

To get TTS working, you must turn it into RO mode first
(that is even worse than export in production environment)
Second, there is "sanity check" script to get sure there are
no dependant objects (and there will be), so you can end
with "transporting whole DB to itself :-)

And even if all works :-), the main reason of not doing this:
how you are going to unplug and plug datafiles from original DB
to itself? Different name? 2x space?
To me sounds like not working idea at all, sorry:-)

On 2004-12-10, Oradba Linux <techiey2k3 (AT) comcast (DOT) net> wrote:

On 2004-12-10, dbyy <noname (AT) fooled (DOT) com> wrote:
.... and both of them will give you huge IO load ...
If it is production env - I would use synonyms (in fact, I always do)
If it is development - use direct export , not CTAS

Cheers!

dbyy <noname (AT) fooled (DOT) com> wrote in
news:Xns95BAC31AFB91Ddbyyatcanadadotcomni (AT) 207 (DOT) 35.177.134:

Thank you guys for your advise,

I think we will go the way to exp/imp the data.
Cheers
Fred