IMP - Importing a table. 1

[Giddygoat]

Hi
I want to import a table from an old version of our database into our current version.

imp <user>/<password>@<oraname> file=<exportfile> tables=table1 full=n

The problem is that I want to import the table with a new name. i.e. table2.
The most obvious solution is to rename the table once it has been imported, I have a table with the same name in the current database that I must leave intact.

Any help gratefully received.

John

 

[THoey]

Is this a production database? If not, maybe you could rename what you have in the DB now, import the old from the EXP and then rename the two tables to something else. I know, kinda circular, but might get around the problem that you are having... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[did02]

Hi,

Another way is to create a DBlink between the old BD and the current DB. Then 'import' your data with a simple query like :
insert into new_table
select * from old_table@Dblink_oldDB;

Rgds,
Did02

 

[Giddygoat]

Thanks for your replies.
Our problem is that our clients have production databases that where upgraded a while ago, unfortunately we made a bit off a gaff and need to cleanse the live data in a table using data from a table in the export file that the clients made before the last upgrade.

We need two versions of the same table in the database, unfortunately, some of our clients don't read the instructions in our release notes so we need to automate as much of the process as possible.

I think the steps I need to take are;
1. Rename the live table <table1> to <live_table1>.
2. Import the old version of <table1> to the live database.
3. Run our migration/cleansing sql script.
4. Drop <table1>
5. Rename <live_table1> back to <table1>

The fix will be performed by our clients DBA's, who have varying levels of competence. I need to come up with a simple, fool proof, bullet proof solution.

Questions
If I rename a table and rename it back again will all of the tables constraints, primary keys and sequences stay intact?

Can I do all of this from the command prompt? and maybe put it all in a batch file?

Thanks again for your help

John

 

[did02]

Hi,

As far as I know you will have to disable the foreign keys that reference fields from table1.
So :
- disable FK
- create live_table1 as select * from table
- truncate table1
- import data into table1
- clean up table1
- insert data from live_table1 into table1 (if needed)
- drop live_table1
- enable FK (take care in your clean up to keep all needed values...)

Hope it helps
Did02

 

[carp]

&quot;If I rename a table and rename it back again will all of the tables constraints, primary keys and sequences stay intact?&quot;

Yes. All you are doing in renaming a table is changing the value that is viewed by humans. Oracle's constraints, indexes, etc are based on the tables OID, which doesn't change. However, sequences are separate, independent objects, so even if you dropped the table the sequence would stay intact.

 

[miniboat]

the alternative is to import the table into a new schema, then you can do want you want