Import table data to nonexistant or renamed table in Oracle

Posted on 2007-08-04
Medium Priority
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

Accepted Solution

gattu007 earned 1500 total points
ID: 19633329
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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19638847
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

ID: 19639474
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,

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

807 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