Buscar este blog

lunes, 24 de julio de 2017

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

No hay comentarios:

Publicar un comentario