Import table data to nonexistant or renamed table in Oracle

Posted on 2007-08-04
Last Modified: 2013-12-27
Hi I'm trying to import a table from one database that has a an extra column third from the bottom ot table. The table I'd like to import it to does not have this column this a test database. So what I did was renamed the target table so that I won't lose the table definition and I started the import. Will the import not see this table create the table and insert the data? Do you know how I can do this. I don't have a lot of time. And I'm only looking a  500,000 rows.
Question by:7Souls
    LVL 7

    Accepted Solution

    If the table does not exist then the import will create the table.

    If the table already exist then it will throw error .... and to overcome this you can specify ignore=y option during import.

    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Just to add:

    The "ignore=y" won't work for you since the definitions don't match.

    If you can rename the original table, that would probably be the easiest.  You will probably want to just import the data and not all the grants/constraints/??? that may be in the export file.

    The other way is to import the table into another schema ( FROMUSER/TOUSER parameters) then just pull the data over with a simple 'insert into select' statement.

    Author Comment

    Thanks guys, I went ahead and tried this before I got your responses and it worked. I remember from my early days with Oracle that the export dump files created the objects if they didn't exist. I guess I was worried about the rename process and how the data dictionary handled the importing of a object that was renamed.

    Thanks for your responses,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    737 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

    20 Experts available now in Live!

    Get 1:1 Help Now