Re: Users migrate

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 12 Mar 2007 07:35:02 -0700
Message-ID: <1173710102.007419.87130@30g2000cwc.googlegroups.com>

On Mar 12, 8:19 am, yazooPL <y..._at_hacking.pl> wrote:
> How I can migrate all users and role between base Oracle 8.1 and 9?Please
> help me.Thx.
>
> Slawek

Do you mean you need to create all the users and roles found in one database (version 8.1) in another database that happens to be version 9?

Or

Do you need to upgrade an existing database to version 9+?

A full export can be used to create users and roles but if there are objects and/or users that you do not want to copy then you can generate the create user statement from the rdbms dictionary views.

set echo off
rem
rem SQL*Plus script to generate create user statements for all users in
rem in the database.
rem
rem 19980902 m d powell New script.
rem
set feedback off
set pagesize 0
set verify off
set trimspool on
spool cre_users.sql
select 'create user '||lower(username)||' '||

        decode(password,'EXTERNAL','identified externally ',
       'identified by values '''||password||''''),
       'default tablespace '||default_tablespace,
       'temporary tablespace '||temporary_tablespace||';'

from sys.dba_users
where username <> 'SYS'
and username <> 'SYSTEM'
/
spool off

You can do basically the same for roles.

HTH -- Mark D Powell -- Received on Mon Mar 12 2007 - 09:35:02 CDT