I found this on DBA-Village which is exactly what I was after, with
instructions on how to generate a create user script:
this is the basic idea.
spool the output and run.
it is not complete. It has some missing grantee objects.
Change it to your fit.
scott_at_9i > @cr_user_like
Enter user to model new user to: SCOTT
Enter new user name: ANOTHERSCOTT
Enter new user's password: ANOTHERTIGER
create user ANOTHERSCOTT identified by ANOTHERTIGER default tablespace
USERS temporary tablespace TEMP profile DEFAULT;
grant DBA to ANOTHERSCOTT;
grant CONNECT to ANOTHERSCOTT;
grant RESOURCE to ANOTHERSCOTT;
grant UNLIMITED TABLESPACE to ANOTHERSCOTT;
grant SELECT ANY DICTIONARY to ANOTHERSCOTT;
alter user ANOTHERSCOTT default role DBA;
alter user ANOTHERSCOTT default role CONNECT;
alter user ANOTHERSCOTT default role RESOURCE;
scott_at_9i > get cr_user_like
1 set pages 0 feed off veri off lines 500
2 accept oldname prompt "Enter user to model new user to: "
3 accept newname prompt "Enter new user name: "
4 accept psw prompt "Enter new user's password: "
5 -- Create user...
6 select 'create user &&newname identified by &&psw'||
7 ' default tablespace '||default_tablespace||
8 ' temporary tablespace '||temporary_tablespace||' profile '||
9 profile||';'
10 from sys.dba_users
11 where username = upper('&&oldname');
12 -- Grant Roles...
13 select 'grant '||granted_role||' to &&newname'||
14 decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
15 from sys.dba_role_privs
16 where grantee = upper('&&oldname');
17 -- Grant System Privs...
18 select 'grant '||privilege||' to &&newname'||
19 decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
20 from sys.dba_sys_privs
21 where grantee = upper('&&oldname');
22 -- Grant Table Privs...
23 select 'grant '||privilege||' on '||owner||'.'||table_name||' to
&&newname;'
24 from sys.dba_tab_privs
25 where grantee = upper('&&oldname');
26 -- Grant Column Privs...
27 select 'grant '||privilege||' on '||owner||'.'||table_name||
28 '('||column_name||') to &&newname;'
29 from sys.dba_col_privs
30 where grantee = upper('&&oldname');
31 -- Set Default Role...
32 select 'alter user &&newname default role '|| granted_role ||';'
33 from sys.dba_role_privs
34 where grantee = upper('&&oldname')
35* and default_role = 'YES';
using export and import
You need to take an export.
and duing import
option 1: use show=y and logfile=somelog.log
now somelog.log has all the information you want.
The actuall import IS NOT done.
option 2: just do a plain import to the new instance with rows=n .
Import will be done,without any rows.
Just precretae the user and tablespace.
Received on Fri Mar 09 2007 - 16:24:10 CST
