Import schema as new name

Posted on 2010-01-07
Last Modified: 2013-12-18
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!
Question by:PsychoDazey
    LVL 34

    Accepted Solution

    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.
    LVL 6

    Author Comment

    Thanks johnsone;
    What is the syntax (using RMAN) to only restore the tablespaces I need?
    LVL 15

    Expert Comment

    Use export / inport utility.
    You can export  an schema (owner), then import (IMP)  with parameters Fromuser=OldSchema ToUser=NewSchema
    LVL 3

    Expert Comment


    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
    LVL 34

    Expert Comment

    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.
    LVL 18

    Assisted Solution

    Have you thought about remap schema?
    impdp system/tiger REMAP_SCHEMA=SCOTT:JKOOPMANN DIRECTORY=datapump DUMPFILE=dept.dmp LOGFILE=dept.log
    LVL 6

    Author Closing Comment

    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!
    LVL 18

    Expert Comment

    glad we could help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    Title # Comments Views Activity
    Extracting Specific String 11 48
    SQL Query 6 53
    Oracle query tuning help required 9 51
    Sql to get orphans 7 47
    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    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…
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now