Copy Oracle Schema from one machine to another

From Devipedia

Jump to: navigation, search

This is the procedure for dumping a copy of an Oracle database and importing it for another user

As SYSTEM, dump the schema of interest.

exp SYSTEM/secretpassword@devserver:1521/myserver.com FILE=export.dmp OWNER=original_owner

Next create a tablespace for yourself if you don't already have one created.

create tablespace dvenable mytabllespace '/u02/oradata/tuld/mytablespace.dbf' SIZE 2000M reuse autoextend on next 1024m maxsize unlimited;

Create your user.

create user dvenable identified by xyz default tablespace mytablespace;

Give your user privileges.

grant dba to dvenable;

Import DB for new user.

imp SYSTEM/secretpassword@devserver:1521/myserver.com FILE=export.dmp FROMUSER=original_owner TOUSER=dvenable

If you use privileged packages, explicit permission must be given.

grant execute on dbms_crypto to dvenable
Personal tools