Copy Database Schemas To A New Database With Same Login Password ?

Print this post

 

How to copy database users from one database to another new database and keep the

login password and granted roles, privileges ?

 

1. Oracle10g and above: Use Data Pump. In Oracle10g you can use the Export DataPump and Import DataPump utilities. Example: -- Step 1: In source database, run a schema level Export Data Pump -- job and connect with a user who has the EXP-FULL_DATABASE role: % expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_scott.dmp \ LOGFILE=exp_scott.log SCHEMAS=scott -- Step 2: In target database, run a schema level Import Data Pump -- job which will also create the user in the target database: % impdp system/manager DIRECTORY=my_dir DUMPFILE=exp_scott.dmp \ LOGFILE=imp_scott.log SCHEMAS=scott Or you can create a logfile (SQLFILE) with all relevant statements. E.g.: impdp system/manager directory=my_dir dumpfile=exp_scott.dmp logfile=imp_scott.log schemas=scott sqlfile=imp_user.sql or: 2. In Oracle9i and above: query the Data Dictionary in the source database to obtain the required information to pre-create the user in the target database. Example: 2.1. Obtain the CREATE USER statement in the source database. E.g.: SET long 200000000 SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual; 2.2. Run other queries in the source database to determine which privileges, grants, roles, and tablespace quotas are granted to the users. E.g.: SET lines 120 pages 100 SELECT * FROM dba_sys_privs WHERE grantee='SCOTT'; SELECT * FROM dba_role_privs WHERE grantee='SCOTT'; SELECT * FROM dba_tab_privs WHERE grantee='SCOTT'; SELECT * FROM dba_ts_quotas WHERE username='SCOTT'; 2.3. Create a script file that contains the CREATE USER, CREATE ROLE statements, the GRANT statements, and the ALTER USER statements for tablespace quotas. 2.4. Pre-create the tablespaces for this user with SQL*Plus in the target database. Note that the original CREATE TABLESPACE statement can be obtained in the source database with DBMS_METADATA.GET_DDL. E.g.: SET long 200000000 SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') FROM dual; 2.5. Run the script of step 2.3. to create the user in the target database. 2.6. Run a user level export. E.g.: exp system/manager file=exp_scott.dmp log=exp_scott.log owner=scott 2.7. Import this export dumpfile into the target database. E.g.: imp system/manager file=exp_scott.dmp log=imp_scott.log fromuser=scott touser=scott or: 3. If all users need to be copied into the new target database: use a full database export from the source database and a full database into the target database. Example: 3.1. Run a full database export. E.g.: exp system/manager file=exp_f.dmp log=exp_f.log full=y or in Oracle10g: expdp system/manager directory=my_dir dumpfile=expdp_f.dmp logfile=expdp_f.log full=y 3.2. Pre-create the tablespaces with SQL*Plus in that target database if they have a different directory structure on the target server. If the directory structure on the target is the same as on the source, ensure that this directory structure is in place (import does not create directories when creating tablespaces). Note that the original CREATE TABLESPACE statement can be obtained with dbms_metadata.get_ddl (see the example in step 2.4. above). 3.3. Import the data into the target database with: imp system/manager file=exp_f.dmp log=imp_f.log full=y or in Oracle10g: impdp system/manager directory=my_dir dumpfile=expdp_f.dmp logfile=impdp_f.log full=y