i want to copy a schema from one user to another

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.

Aaaahhhh!
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!

THIS DOESN'T INCLUDE THE CONSTRAINTS HOW DO I INCLUDE THEM

or rather it doesn't copy the constraints