DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: import users with same password

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    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

  2. #2
    Join Date
    Jul 2002
    Posts
    335
    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.

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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

  4. #4
    Join Date
    Oct 2002
    Posts
    807
    *identified by values *

    Run it on target. See for yourself what happens.

  5. #5
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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 .

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    what isnt working

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    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."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Jul 2002
    Posts
    335
    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.

  9. #9
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  10. #10
    Join Date
    Jul 2002
    Posts
    335
    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
  •  


Click Here to Expand Forum to Full Width