| migrating users form one Databas to another [message #59132] |
Thu, 30 October 2003 14:56  |
Lance Pris
Messages: 40
Registered: January 2002
|
Member |
|
|
I am not a oracle DBA but have been placed in the role.
My situation is as follows I need to migrate all the users from one database to another.
I am working on Oracle 9i and noticed there is a file called user.dbf that looks like the right one. Is it possible to just move the folder or is there a procedure to follow.
If anyone knows where to find some good documentation about this task it would be appreciated.
Thank you in advance
Lance
|
|
|
|
| Re: migrating users form one Databas to another [message #59135 is a reply to message #59132] |
Thu, 30 October 2003 22:47  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
Dont copy that user.dbf file to another database, bcz it will not solve ur problem. To migrate all of ur users from one database to another u have to recreate them in newer one. Bcz in ur existing database the users will have different priveleges on different database objects which may not be present in ur newer database.
|
|
|
|
| Re: migrating users form one Databas to another [message #59143 is a reply to message #59132] |
Fri, 31 October 2003 07:56 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
user.dbf is just a datafile belonging to one of your tablespaces and has got nothing to do with your Oracle users(except that your users could have their default tablespace assigned to say USERS tablespace which has user.dbf datafile).
Are you talking about just 'users' who do not own any database objects ? In that case,extract the user creation scripts from your source database and modify accordingly(tablespace,quota,password,privileges etc) if necessary and run them on your target database.
If these users own database objects(ie schema), precreate the users on your target database with necessary privileges,quota on tablespaces etc and do a user level export of these owners from the source database and do a user level import into the target database ..to migrate their schemas over.
Refer to Utilities guide for more info on export/import.
-Thiru
|
|
|
|
|
|
| Re: migrating users form one Databas to another [message #59186 is a reply to message #59148] |
Mon, 03 November 2003 05:27 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
I generally use some tools for reverse engineering anything..like Toad etc...
Something like this can be used :
set heading off verify off feedback off echo off term off linesize 200 wrap on
spool Recreate_Users.sql
SELECT 'create user ' || username ||
' identified ' ||
DECODE(password, NULL, 'EXTERNALLY', ' by values ' || '''' || password || '''') ||
' default tablespace ' || default_tablespace ||
' temporary tablespace ' || temporary_tablespace ||
' profile ' || profile || ';'
FROM dba_users
ORDER BY username;
spool off
This will just create the users with the same password .You will then need to take care of roles,grants etc..(scripts again )
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
