i want to copy a schema from one user to another

how do i  copy a schema from one user to another
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
lucinda77Author Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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?
lucinda77Author Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lucinda77Author Commented:
lucinda77Author Commented:
or rather it doesn't copy the constraints
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.