-
import users with same password
Hi Friends,
I am planning to do migration from 8.1.7 openVMS to 10g Windows
there are several users in the database .
how can i import just these users into the new database with same userid and password.
sorry for taking your time for such a small question.
Thanks
-
Run:
select 'create user '||username||
decode(password,'EXTERNAL',' identified externally',
' identified by values '||''''||password||'''')||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||';'
from sys.dba_users
where username NOT IN ('ADAMS', 'BLAKE', 'CLARK', 'JONES', 'SCOTT',
'ANONYMOUS','AURORA$JIS$UTILITY$','AURORA$ORB$UNAUTHENTICATED',
'CTXSYS', 'DBSNMP', 'LBACSYS', 'MDSYS', 'MTSSYS',
'ODM', 'ODM_MTR', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS',
'OSE$HTTP$ADMIN', 'OUTLN', 'OAS_PUBLIC',
'HR', 'OE', 'PM', 'QS', 'QS_ADM', 'QS_CB', 'QS_CBADM',
'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS', 'SH',
'PERFSTAT', 'REPADMIN', 'RMAN',
'SYS', 'SYSTEM', 'TRACESVR', 'WEBSYS',
'WKPROXY', 'WKSYS', 'WMSYS', 'XDB')
order by username
/
and edit as required.
-
executing this script would create
script something like this ...
create user test identified by values 'FDA8A2DA36DFA7E7' default tablespace TS_DATA temporary tablespace TEMP
but i want to have same password used in earlier database ...
how to capture password
-
*identified by values *
Run it on target. See for yourself what happens.
-
It seems it is not working in 10g ( 10.1.0.3.0 ) version
but working in erlier versions ...
any other method i can look at .
-
-
Re: import users with same password
Originally posted by prakashs43
Hi Friends,
I am planning to do migration from 8.1.7 openVMS to 10g Windows
there are several users in the database .
how can i import just these users into the new database with same userid and password.
sorry for taking your time for such a small question.
Thanks
You need to do a full database export and import it on the new machine. So why worry about changing the password. It will be there and same as it was when you import the database.
Amar
"There is a difference between knowing the path and walking the path."
-
Originally posted by prakashs43
It seems it is not working in 10g ( 10.1.0.3.0 ) version
but working in erlier versions ...
any other method i can look at .
Can you detail what isn't working? I confess the script has only been tested on Oracle 7-9, not 10G.
-
Results from 10g
Code:
SQL> conn thomas/thomas
Connected.
SQL> alter user thomas identified by oracle;
User altered.
SQL> select password from dba_users
2 where username='THOMAS';
PASSWORD
------------------------------
63D3859B93B1E55D
SQL> alter user thomas identified by thomas;
User altered.
SQL> conn thomas /thomas
Connected.
SQL> alter user thomas identified by values '63D3859B93B1E55D' ;
User altered.
SQL> conn thomas/oracle
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
-
Originally posted by Thomasps
Results from 10g
Code:
SQL> conn thomas/thomas
Connected.
SQL> alter user thomas identified by oracle;
User altered.
SQL> select password from dba_users
2 where username='THOMAS';
PASSWORD
------------------------------
63D3859B93B1E55D
SQL> alter user thomas identified by thomas;
User altered.
SQL> conn thomas /thomas
Connected.
SQL> alter user thomas identified by values '63D3859B93B1E55D' ;
User altered.
SQL> conn thomas/oracle
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
Praksh, can you detail your problem, I can't see why the script will not work on 10G.
Barry
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|