Re: 9iR2 exp -> 10g imp

From: GS <GS_at_GS.com>
Date: Thu, 12 Jun 2008 16:09:32 GMT
Message-ID: <0Tb4k.1117$L03.680@edtnps92>


Noons wrote:
> Arch wrote,on my timestamp of 12/06/2008 11:43 PM:
>

>>
>> Excellent!  This is a plan that I can follow.

>
> NO! Don't just "follow"! Use it as a base to
> adapt for your own needs, but don't follow:
> it does NOT cover all possibilities, mainly
> your specific case!
>
>> One question - to import schema by schema, I think the owners will
>> need to be created in advance.  Is there a way to import the owners
>> (with passwords, profiles, roles, etc)?

>
> Not that I know of. You might of course try to use
> the option to make imp full=y list all the statements
> it'd run without actually running anything (look it
> up, can't remember), then filter out the stuff you need
> with a judicious editing session and run the output as
> a script.
>
> Another option is to investigate DBMS_METADATA.
> There is an example in the 10g manual about
> getting all granted ddl for good ole SCOTT:
> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
> FROM DUAL;
>
> This:
> http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA
>
> gives an idea what else you can get.
>
> Nifty little package. I use it a lot.

fwiw, when I create a create_users.sql file for the new database I usually just copy and paste the ddl from using "create like" in the source database GUI into notepad. Be sure so use "create like" rather than "show object ddl" for the user, because "create like" will also do the neccesary grants & priv's etc.. whereas "show object ddl" will not.

This works fine if your users are few in number, but if you have lots of users to create then Noon's idea of using DBMS_METADATA.GET is a better route.

hth Received on Thu Jun 12 2008 - 11:09:32 CDT

Original text of this message