Below is a script written several years ago to copy users from one instance to another.. After using these scripts, you can uese exp/imp to copy tables. Obviously in 10g you can use datapump for this. /******************************************************************************************/ /* The move_users script needs the source database and the target database defined */ /* then it creates the following output scripts: */ /* */ /* CREATE_PROFILES.SQL: This script creates a profile in the target database that is not */ /* in the source database. */ /* ALTER_PROFILES.SQL: This script moves changes in profiles from the source database */ /* to the target database. */ /* CREATE_USERS.SQL: This script creates a user in the target database that is not in */ /* the source database. */ /* CHANGE_USERS.SQL: This script moves changes in the user (PASSWORD, */ /* DEFAULT_TABLESPACE, A.TEMPORARY_TABLESPACE, A.PROFILE) from the */ /* source database to the target database. */ /* ADD_ROLES.SQL This script grants roles to users in the target database that */ /* were in the source database. */ /* CREATE_ROLES.SQL This script creates roles in the target database that where */ /* in the source database. */ /******************************************************************************************/ SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF DEFINE sourceDB = '@SRCINST' DEFINE targetDB = '@'DESTINST /*******************************CREATE_PROFILES.SQL****************************************/ SPOOL CREATE_PROFILES.SQL SELECT 'CREATE PROFILE '||PROFILE||' LIMIT '||RESOURCE_NAME||' '||LIMIT||';' FROM DBA_PROFILES&sourceDB. WHERE PROFILE IN ( SELECT PROFILE FROM DBA_PROFILES&sourceDB. MINUS SELECT PROFILE FROM DBA_PROFILES&targetDB. ) AND ROWNUM < 2; SPOOL OFF /*******************************ALTER_PROFILE.SQL******************************************/ SPOOL ALTER_PROFILE.SQL SELECT 'ALTER PROFILE '||B.PROFILE||' LIMIT '||A.RESOURCE_NAME||' '||A.LIMIT||';' FROM DBA_PROFILES&sourceDB. A, DBA_PROFILES&targetDB. B WHERE A.limit != B.limit AND A.profile = B.profile AND A.RESOURCE_NAME = B.RESOURCE_NAME; SPOOL OFF /*******************************CREATE_USERS.SQL*******************************************/ SPOOL CREATE_USERS.SQL SELECT 'CREATE USER '||USERNAME||' IDENTIFIED BY VALUES '''||PASSWORD|| ''' DEFAULT TABLESPACE '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE ' ||TEMPORARY_TABLESPACE||' PROFILE '|| PROFILE||';' FROM DBA_USERS&sourceDB. WHERE USERNAME IN ( SELECT USERNAME FROM DBA_USERS&sourceDB. MINUS SELECT USERNAME FROM DBA_USERS&targetDB. ); SPOOL OFF /*******************************CHANGE_USERS.SQL*******************************************/ SPOOL CHANGE_USERS.SQL SELECT 'ALTER USER '||b.USERNAME||' IDENTIFIED BY VALUES '''||A.PASSWORD|| ''' DEFAULT TABLESPACE '||A.DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE '|| A.TEMPORARY_TABLESPACE||' PROFILE '||A.PROFILE||';' FROM DBA_USERS&sourceDB. A, DBA_USERS&targetDB. B where A.USERNAME = B.USERNAME AND A.PASSWORD||A.DEFAULT_TABLESPACE||A.TEMPORARY_TABLESPACE||A.PROFILE!= B.PASSWORD||B.DEFAULT_TABLESPACE||B.TEMPORARY_TABLESPACE||B.PROFILE; SPOOL OFF /****************************CREATE_ROLES.SQL**********************************************/ SPOOL CREATE_ROLES.SQL SELECT 'CREATE ROLE '||ROLE||';' FROM DBA_ROLES&sourceDB. WHERE ROLE IN ( SELECT ROLE FROM DBA_ROLES&sourceDB. MINUS SELECT ROLE FROM DBA_ROLES&targetDB. ); SPOOL OFF /*******************************ADD_ROLES.SQL**********************************************/ SPOOL ADD_ROLES.SQL SELECT 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||';' FROM DBA_ROLE_PRIVS&sourceDB. WHERE (GRANTEE, GRANTED_ROLE) IN ( SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS&sourceDB. MINUS SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS&targetDB. ); SPOOL OFF /******************************************************************************************/ SPOOL ADD_SYS.SQL SELECT 'GRANT '||PRIVILEGE||' TO '||GRANTEE||';' FROM DBA_SYS_PRIVS&sourceDB. WHERE (GRANTEE, PRIVILEGE) IN ( SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS&sourceDB. MINUS SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS&targetDB. ); SPOOL OFF /******************************************************************************************/ /******************************************************************************************/ /**************************************************************************/ move db_links select 'create public database link '||db_link||' using '''||db_link||''';' from dba_db_links@oltpqa where USERNAME is null and db_link not in (select db_link from dba_db_links); /* @CREATE_PROFILES.SQL @ALTER_PROFILES.SQL @CREATE_USERS.SQL @CHANGE_USERS.SQL @ADD_ROLES.SQL @ADD_SYS.SQL */ UNDEFINE sourceDB UNDEFINE targetDB SET FEEDBACK ON @LOC -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.'