Re: Migrating Userids

From: Brett Neumeier <neumebm_at_hpd.abbott.com>
Date: 1997/09/09
Message-ID: <3415D3D4.57C9C03B@hpd.abbott.com>#1/1

Debor19071 wrote:

> We have ALOT of Oracle userids we need to copy from 1 instance to another.
> The tools we are using do not copy the passwords properly.
>
> Do you know of a way to properly pass the passwords over from 1 instance
> to another in the least laborious way as possible? We don't want to export
> every user.

Not a problem! Here's the script I use to generate a "create user" script (actually this is a subset of it; the real script also handles database quotas)

HTH, -bn

-----cut here------
set termout off
set feedback off
set heading off
set pagesize 0
set pause off
set space 1
column dummy1 noprint
column dummy2 noprint
spool c_user.sql
prompt REM File: c_user.sql
prompt REM This temporary SQL script was generated by g_user. select TO_CHAR(sysdate,'"REM Generated on" MM/DD/YYYY "at" HH24:MI')  from DUAL
/
select username dummy1, 10 dummy2,
 'CREATE USER ' || username || ' IDENTIFIED BY VALUES ''' ||  password || ''''
 from DBA_USERS
 where username NOT IN ('SYS','SYSTEM')
UNION
select username, 20,

 ' DEFAULT TABLESPACE ' || default_tablespace ||
 ' TEMPORARY TABLESPACE ' || temporary_tablespace ||
 ' PROFILE ' || profile || ';'

 from DBA_USERS
 where username NOT IN ('SYS','SYSTEM')
order by 1, 2
/
spool off
exit
-----------cut here-------- Received on Tue Sep 09 1997 - 00:00:00 CDT