Re: Move 8i instances into 9R2 schemas

You could try an IMP SHOW=Y FULL=Y LOG=SOMEFILE.TXT

Then edit the SOMEFILE.TXT and modify the grants with the correct user names (as they have changed), extract those commands from the file and execute them at the SQLPLUS prompt.

Or you could use a SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || USERNAME ..... FROM USER_ROLE_PRIVS
type of instructions in a spool file and execute them, but that would probably take you longer.

HTH

-- 
Syltrem

OpenVMS 7.3-1 + Oracle 8.1.7.4
http://pages.infinit.net/syltrem (OpenVMS related web site, en français)
---zulu is not in my email address---
"Marc Eggenberger" <marc.eggenberger_at_remove.itc.alstom.com> a écrit dans le
message de news:MPG.1c47199968e49f119896a3_at_iww.cacti.ch.alstom.com...


> Hi.

>

> I have the following situation:

>

> 3 instances of Oracle 8i (8.1.7.0) on Windows 2000 Server SP4 for 3

> applications. The schemas of the 3 instances are identical except for

> the data. They are used for an application for 3 different groups.

>

> Now I want to move them into one 9R2 (9.2.0.6 on Windows 2000 Advanced

> Server SP4) instance into different schemas.

>

> The database is very simple. Some tables, and some foreign constraints.

> Nothing else, No procedures, no java, no triggers.

>

> What I tried is create the new instance and all the schemas.

>

> It looks like this:

>

> Old_Instance1:

> schema1

> schema2

> schema3

>

> Old_Instance2:

> schema1

> schema2

> schema3

>

> Old_Instance3:

> schema1

> schema2

> schema3

>

>

> New_Instance:

> group1_schema1

> group1_schema2

> group1_schema3

> group2_schema1

> group2_schema2

> group2_schema3

> group3_schema1

> group3_schema2

> group3_schema3

>

> I then used exp on the old server to expert the schemas on Old_instance1

> and imp and the same server (I read that I always should use the lower

> version of two different instances when exp/imp) to the new instance.

>

> The data is ok but on the old instance user schema3 hat grants on

> schema1, same with schema2 on schema1.

>

> These grants failed with the import. I used the following cmdlines:

>

> exp sys/xxx_at_old_instace1 file=e:\export\old_instance1.dmp log=e:\export

> \old_instance1.log owner=(schema1, schema2, schema3)

>

> then I issued the following imp cmds:

>

> imp privuser/xxx_at_new_instace file=e:\export\old_instance1.dmp log=e:

> \export\imp.log fromuser=schema1 touser=group1_schema1

>

> imp privuser/xxx_at_new_instace file=e:\export\old_instance1.dmp log=e:

> \export\imp.log fromuser=schema2 touser=group1_schema2

>

> imp privuser/xxx_at_new_instace file=e:\export\old_instance1.dmp log=e:

> \export\imp.log fromuser=schema3 touser=group1_schema3

>

>

> but as I said the grants fail because imp tries grants to schema2 on the

> new instance but this does not exist.

>

> How can I solve this? Am I doing it completely wrong? What should I do

> instead?

>

>

> Thanks for any hints.

>

>

>

>

>

>

> -- 

> mfg

> Marc Eggenberger