• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

Import schema as new name

I have an Oracle 10g database on Windows running in no-archive log mode.  I am being asked to restore a schema as a different schema name from Monday (1/4/2010).  The only way I can think of to do this is to restore the database to a point in time, export the schema, restore the database to its previous state then import the schema.  I am not even sure if it is possible to restore the schema as a new name.  Please give me the syntax for any commands, this is new to me!  Thanks!
0
PsychoDazey
Asked:
PsychoDazey
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
johnsoneSenior Oracle DBACommented:
I would restore the backup from 1/4 on a different machine.  If you can identify the tablespaces involved, you should only have to restore those tablespaces and can offline drop any others.  This could save on space needed for the restore.  As this is a temporary database there is no need for everything.

Then you can export the user.  On the import, you specify the FROMUSER and TOUSER parameters to change the owner.
0
 
PsychoDazeyAuthor Commented:
Thanks johnsone;
What is the syntax (using RMAN) to only restore the tablespaces I need?
0
 
x77Commented:
Use export / inport utility.
You can export  an schema (owner), then import (IMP)  with parameters Fromuser=OldSchema ToUser=NewSchema
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
raju1105Commented:
Hi,

If you take daily export backups then your job is simply done. Restore the backup to the new machine and import only the required schema after you create a new database on the sever.

imp system/manager fromuser=abc touser=xyz file=abc.dmp log=abc.dmp statistics=n ignore=y
0
 
johnsoneSenior Oracle DBACommented:
You would have to determine which file numbers were involved in the tablespace and restore only those files.  You may have to do the recovery yourself as I'm not sure that rman can handle that type of recovery itself.

I don't have access to a server I can get you a script from at the moment.  I'll see if I can make up a sample.
0
 
sventhanCommented:
Have you thought about remap schema?
impdp system/tiger REMAP_SCHEMA=SCOTT:JKOOPMANN DIRECTORY=datapump DUMPFILE=dept.dmp LOGFILE=dept.log
http://www.orafaq.com/node/74 
0
 
PsychoDazeyAuthor Commented:
Thanks to all for your input.  I awarded johnsone partial points because he was the first to mention the fromuser/touser.  But I also read the article from sventhan and ended up using parts from both.  Thanks again!
0
 
sventhanCommented:
glad we could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now