Oracle: Copy schema from one user to another user

Here are the steps to copy schema from one user to another.

1. Unlock the source Oracle user account. Login Oracle as SYSDBA and execute the following command.

SQL> Alter user <fromUsername> identified by <fromPassword> account UNLOCK;

2. Create the directory where you would like to export the schema.

SQL> CREATE OR REPLACE DIRECTORY schema_dump AS '/usr/myDirectory/oracleSchema';

SQL> GRANT READ, WRITE ON DIRECTORY schema_dump TO <user need to access>;

The schema_dump is a name you can use in your command. The physical location of the directory is '/usr/myDirectory/oracleSchema'.

quit SQL*Plus.

3. Export the source schema to the dump directory.

$ expdp <fromUsername> / <fromPassword> schemas=<schema name> directory=schema_dump dumpfile=schema.dmp logfile=dumplog.txt

The <fromUsername> is the source user Oracle login username and the <fromPassword> is the source user Oracle login password.

4. Import the schema and map the schema to the destination user.

$ impdp <toUsername> / <toPassword> directory=schema_dump dumpfile=schema.dmp logfile=dumplog.txt remap_schema=<fromUserSchemaName>:<toUserSchemaName>

---------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.