Copy a Schema in Oracle

I don’t know why I found it so hard to find a straight answer to the following question:

How do you copy all the objects of a given schema to a new schema in the same Oracle database? Or, more simply, how do you make a copy of a schema in an Oracle database?

The short answer is that you use the export/import command line utilities EXP and IMP. Most examples show how to copy a schema from one database to another, but in my case, I wanted to make a copy of the schema within the same database. I ended up discovering that the IMP command can take FROMUSER and TOUSER arguments that allow you to specify which schema objects should be copied from, and which schema to copy those objects to. Here’s the procedure I used:

  1. Create the user/schema that you want to copy the objects to
  2. Use the EXP command to export all the objects from your source schema
  3. Use the IMP command with the FROMUSER/TOUSER arguments

One thing to keep in mind is on what tablespace the copied database objects will be created on. In my case, I wanted them created on the new user’s default tablespace as opposed to the tablespace that the source objects were created on. To do this, simply set the import user’s quota on both the original tablespace(s) and the system tablespaces is 0:

alter user [import_user] quota 0 on system;
alter user [import_user] quota 0 on [orig_tablespace];

For more on using the export/import commands, see the Oracle documentation.