We help IT Professionals succeed at work.

i want to copy a schema from one user to another

lucinda77 asked
how do i  copy a schema from one user to another
Watch Question

Use export and import
The export facility can be used to "dump" the objects and data for a user to an export file:
exp userid=system/manager file=my_db.dmp log=my_db.log owner=SCOTT
The OWNER parameter exports a schema. In my example, that would be the SCOTT schema

Import the dump file:
imp userid=system/manager file=my_db.dmp log=imp.log fromuser=SCOTT

The FROMUSER clause tells imp which schema to import. The TOUSER clause tells it the name of the schema into which the data should be loaded.

This assumes the export/import is done on the same machine.  If not, then simply ftp the export file using binary mode to the machine onto which it is to be imported.


thanks for your reply, but i'm still a little confused do i do this in oracle enteprise manager console and if i do where do i go to do this or doi type it in sql plus
You can run export and import through Oracle Enterprise Manager or you can run it from the command prompt.  import and export are two executables that are installed in the $ORACLE_HOME/bin directory when you install the Oracle database (or database client).

The examples I have given you assume your are running them interactively at a command line prompt.
Hi Lucinda,  have you managed to copy the schema?


no not yet,

i did not understand your reply this is what i have done and what i want to do.

i created a user like SCOTT in the same database(LUCINDA) called HELEN
i want to copy the example SCHEMA in SCOTT(i.e the tables EMP, DEPT, SALGRADE) into HELEN

I tried doing this in SQL PLUS:
COPY FROM SCOTT/roseline@LUCINDA  to  HELEN/roseline@LUCINDA create'||' '|| EMP ||' '||'using select * from'||' '||EMP ||';';

I get this error msg
SQL> COPY FROM SCOTT/roseline@LUCINDA  to  HELEN/roseline@LUCINDA create'||' '|| EMP ||' '||'using select * from'||' '||EMP ||';';
   usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel
  <db>   : database string, e.g., scott/tiger@d:chicago-mktg
  <opt>  : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
  <table>: name of the destination table
  <cols> : a comma-separated list of destination column aliases
  <sel>  : any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.
You want to copy tables between a schema.

Try the following:

COPY FROM scott/roseline@lucinda to helen/roseline@lucinda create emp using select * from emp_details_view;

lucinda is the name (sid) of your database.  Not sure why you included the concatenation symbols etc. but they aren't needed.

The export and import commands I refered to is a way to copy a whole schema.  For copying a few tables you are probably just as well using the copy command as you have done, but be aware this won't be available in future versions of Oracle!

You could login as helen and run the following:

create table emp
as select * from scott.emp;

Providing scott has granted select privilege to helen or public on emp this would work.

This can be done by logging into scott and running:

grant select on emp to helen;
grant select on emp to public;

public gives select privilege to everyone.




or rather it doesn't copy the constraints