[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

i want to copy a schema from one user to another

Posted on 2008-01-29
10
Medium Priority
?
8,955 Views
Last Modified: 2013-12-19
how do i  copy a schema from one user to another
0
Comment
Question by:lucinda77
  • 6
  • 4
10 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 20767148
Use export and import
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 20767197
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.
0
 
LVL 1

Author Comment

by:lucinda77
ID: 20767315
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 16

Expert Comment

by:Milleniumaire
ID: 20767556
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.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 20775931
Hi Lucinda,  have you managed to copy the schema?
0
 
LVL 1

Author Comment

by:lucinda77
ID: 20789951
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.
0
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 20795178
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!
0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 500 total points
ID: 20795304
Alternatively,

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;
or
grant select on emp to public;

public gives select privilege to everyone.
0
 
LVL 1

Author Comment

by:lucinda77
ID: 20806798
THIS DOESN'T INCLUDE THE CONSTRAINTS HOW DO I INCLUDE THEM
0
 
LVL 1

Author Comment

by:lucinda77
ID: 20806803
or rather it doesn't copy the constraints
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses

611 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question