export/import username with password 1

[patrick118]

We have an oracle 8i server.

In the database we have around 60 users defined. I want to create a test server that has the same information.

My problem is how do i export the users with there passwords and import them again with the same password as in the other database. Is this possible?

Patrick

 

[KenCunningham]

Your user information should be contained within the datafiles etc you need to copy to the new test server. What is your plan for this copy process?

 

[patrick118]

Complete story is we are going to migrate the database to a different server and different oracle version 9i.

So we will be using the export/import utility but we want an export of the schema and an export of the users.

I allready have the testserver running with the schema export and import. Worked but now we want the other users to be imported.

Patrick

 

[magvivek]

I dont understand what the problem is here?
A full database export will export all the users and their schema ( along with their passcodes etc).
Before import you can use the show=y option to get ddl of all the users and the passwords (import is NOT really done, but all DDL contents are dumped into a textfile and passwords are hashed).
Else,
You can also get the same from the source database itself.!.

take a full export.
do a dummy import like this.

imp mag/mag show=y log=somefile.sql file=fullexport.dmp

the conents onf somefile.sql will have something like.

 "CREATE USER "R_SELF" IDENTIFIED BY VALUES '9BFC22078B779B4F' DEFAULT TABLES"
 "PACE "USERS" TEMPORARY TABLESPACE "TEMP""
 "CREATE USER "QUERY" IDENTIFIED BY VALUES '865802F409982AD8' DEFAULT TABLESP"
 "ACE "USERS" TEMPORARY TABLESPACE "TEMP""
 "CREATE USER "GUEST" IDENTIFIED BY VALUES '1C0A090E404CECD0' DEFAULT TABLESP"
 "ACE "TOOLS" TEMPORARY TABLESPACE "TEMP""
 "CREATE USER "TEST" IDENTIFIED BY VALUES '7A0F2B316C212D67' TEMPORARY TABLES"
 "PACE "TEMP""

else
get the information from source database itself.
like this

  1* select 'create user '||username||' identified by values '''||password||'''default tablespace '||default_tablespace||';' from dba_users
mag@mutation_mutation > /

'CREATEUSER'||USERNAME||'IDENTIFIEDBYVALUES'''||PASSWORD||'''DEFAULTTABLESPACE'||DEFAULT_TABLESPACE|
----------------------------------------------------------------------------------------------------
create user SYS identified by values '4DE42795E66117AE'default tablespace SYSTEM;
create user SYSTEM identified by values '1C4DEB81D4E4B2B4'default tablespace SYSTEM;
create user DBSNMP identified by values 'E066D214D5421CCC'default tablespace SYSTEM;
create user DBADMIN identified by values '1177FD6FD635EB27'default tablespace USERS;
create user MAG identified by values '5CCF41F4CCBA0699'default tablespace TOOLS;
create user TEST identified by values '7A0F2B316C212D67'default tablespace SYSTEM;
create user GUEST identified by values '1C0A090E404CECD0'default tablespace TOOLS;
create user QUERY identified by values '865802F409982AD8'default tablespace USERS;
create user R_SELF identified by values '9BFC22078B779B4F'default tablespace USERS;
create user OUTLN identified by values '4A3BA55E08595C81'default tablespace SYSTEM;
create user WMSYS identified by values '7C9BA362F8314299'default tablespace SYSTEM;

Note the password is hashed and is encrypted equilavelent of the original password.

Mahesh Rajendran

 

[patrick118]

Thanks That is completely what i need

Patrick