Re: 9iR2 exp -> 10g imp

From: Arch <send.no_at_spam.net>
Date: Mon, 16 Jun 2008 17:04:19 -0400
Message-ID: <44kd54lg7o99pdrhu7bqpb6b4g9drjaj61@4ax.com>


On Mon, 16 Jun 2008 17:09:56 GMT, GS <GS_at_GS.com> wrote:

>hpuxrac wrote:
>snip
>>
>> Scripting is what it is all about. You are making me nervous the the
>> "em GUI" remark.
>
>If I only have a few users to create, I just create my script with cut &
>paste from the GUI's "create like" - "show sql" window. If it's a lot of
>users then I suggested dbms metadata is a far better method

I created scripts for everything. I only used the GUI to check. I have several users with significant schemas. I found the GUI convenient for checking to make sure all of the needed grants were included in the scripts. I copy/pasted into a reference file then compared afterward.

Spooling a select from dba_users proved to be far and away the best way to create a script to create the users. I manually edited out the various "built in" accounts (sys, system, etc). The script included passwords, profiles and tablepaces. I couldn't figure out how to embed a literal quote character (') into the spool output, so I used _Q_, then a single global replace with an editor fixed the file.

Most everything else came out of the dump file - listed out. grep found all of the create rolls, there weren't all that many. Grants were a bit tougher. I grepped for GRANTS in the output file but the result needed a lot of manual editing.

The only needed GRANT that I found, that was not included in the list was CREATE VIEW. I'm not sure if that might be something different between 9.2.0.4 and 10.2.0.3? It caused some problems until I figured that out.

Ran into a problem with one of the user_jobs. The job number was 1, apparently in 10g, 1 is used for a system_job. Consequently the job did not get created - had to fix it manually.

A very successful endeavor. And, as I mentioned earlier, I learned a great deal in the process. Thanks to everyone for getting me on the right track. Received on Mon Jun 16 2008 - 16:04:19 CDT

Original text of this message