Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

    Unanswered: Export Users and Passwords from 9i

    Hi,
    I am trying to upgrade to 10g but the scripts are not working correctly, but thats a different issue!

    I have created a blank 10201 database and I want to import our application into that, which I can do fine.

    However, I need to export all the users and passowords as we have about 2000 users across 60 databases and I can't ask them all what their passwords are! How can I do this as I have tried exp with the owners parameter but this does not recreate the users. None of the users have any objects so it is only the usernames and passwords I need to migrate across

    Many thanks
    Robert
    There are 10 types of people in the world, those that know Binary and those that don't.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Try this:
    Code:
    ----------------------------------------------------------------
    --  db_migrate_users.sql
    --
    --  Create DDL to migrate users with no objects
    --
    ------------------------------------------------------------------
    cl col
    col db new_value sid
    select name db from v$database;
    set pages 0 lin 80 feed off ver off recsep off
    set term on
    col sq noprint
    spo /tmp/&sid._migrate_users.sql
    Select username||to_char(100+rownum) sq,
    'Create User '||u.username||' identified by values '
    ||chr(39)||password||chr(39)||chr(10)
    ||' Default tablespace '||default_tablespace
    ||' Temporary tablespace '||temporary_tablespace||';'||chr(10)
      from dba_users u 
     where lock_date is null
       and username not in ('SYS','SYSTEM')
       and username not like 'OPS$%'
    UNION
    Select username||to_char(200+rownum) sq, 'Alter User '||username
    ||' quota '||decode(max_bytes,-1,'UNLIMITED',max_bytes/1024||'K')
    ||' ON '||tablespace_name||';'||chr(10)
    from dba_ts_quotas q
    order by 1
    /
    spo off
    exit


    Last edited by LKBrwn_DBA; 03-28-07 at 10:25.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    fantastic, thanks I didnt realise it would be so simple!
    There are 10 types of people in the world, those that know Binary and those that don't.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    you can't export FULL=Y and then IMPORT full=y to get all the users imported?
    That is news to me since I just did this exact thing yesterday.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    I can't do a full=y import from my 9i database into my 10g database as that is going to overwrite all the 10g sys tables / procedures etc etc
    There are 10 types of people in the world, those that know Binary and those that don't.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Why would it overwrite anything?
    I don't think the code uses CREATE or REPLACE.

    even if it overwrites them, everything for me works perfectly and recompiles fine (I have done 9i to 10g).

    also, you could use tables=no, indexes=no, etc.

    sounds like you found a solution so I guess it doesn't matter.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •