Re: 9iR2 exp -> 10g imp

From: Arch <send.no_at_spam.net>
Date: Sat, 14 Jun 2008 12:06:41 -0400
Message-ID: <r8q75413bab6dk8fog4870tbmnhjirtq1l@4ax.com>


On Thu, 12 Jun 2008 16:09:32 GMT, GS <GS_at_GS.com> wrote:

>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

This little project is proving to be a bit more challenging than I had anticipated. There doesn't seem to be a single process that gives me what I need.

GS's suggestion to use the "Create Like" in the em GUI is intriguing. It does include the grants. It's quite tedious for several hundred users, however.

Noons' suggestion to list out the dump file made with full=y from source database is likewise, very useful. I can get user creates and grants from that. The listing doesn't come out in a "usable" script format, so there is lots of editing to do.

To simply create a script to create the users, the easiest method seems to be simply spool a select username and password from dba_users. That, of course, doesn't help with the grants.

All in all, it has been a learning experience for me. Thanks to all who offered suggestions. Users expect to go live in the new database on Monday. Received on Sat Jun 14 2008 - 11:06:41 CDT

Original text of this message