Oracle 11g - Export/Import as different user

Origin Machine

• User to export: user1
• System credentials: SYSTEM/pass1

Execute de following command:

exp system/pass1 FILE=user1.dmp OWNER=user1 LOG=user1-dump.log

Now you have the dump file, wich contains all user database info (views includes). In order to see the text content you can use this command:

strings user1.dmp | more

If you also need to export de user itself, you can use de followings SQL commands;

SQL> CONNECT SYSTEM/pass1;

SQL> SELECT DBMS_METADATA.GET_DDL('USER','SCOTT') FROM dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT') from dual;

Destination Machine

• User to import: user2
• System credentials: SYSTEM/pass2

The user2 is empty.

Execute de following command:

imp system/pass2 FILE=user1.dmp FROMUSER=user1 TOUSER=user2 LOG=user2-load.log