Oracle — Import data into a table with a different name?

I suppose you want to import the table in a schema in which the name is already being used. I don't think you can change the table name during the import. However, you can change the schema with the FROMUSER and TOUSER option.

This will let you import the table in another (temporary) schema.

I suppose you want to import the table in a schema in which the name is already being used. I don't think you can change the table name during the import. However, you can change the schema with the FROMUSER and TOUSER option.

This will let you import the table in another (temporary) schema. When it is done copy the table to the target schema with a CREATE TABLE AS SELECT. The time it will take to copy the table will be negligible compared to the import so this won't waste too much time.

You will need two times the disk space though during the operation. Update As suggested by Gary a cleverer method would be to create a view or synonym in the temporary schema that references the new table in the target schema. You won't need to copy the data after the import as it will go through directly to the target table.

I believe this will work! Thank you. One small wrinkle here is that the table is mostly BLOBs by volume, so the CREATE TABLE AS SELECT will take a while, but I can deal with that.

– scrible Dec 14 '09 at 17:16 1 IF you precreate the object in the TOUSER as a view with SELECT * FROM destuser. Newtable, the inserts will go through the view direct to the new table. At least that would work with IMP IGNORE=Y.

Worth trying with a synonym too. – Gary Myers Dec 14 '09 at 21:46 @Gary: good suggestion I updated my answer – Vincent Malgrat Dec 15 '09 at 9:59.

If you are using the old EXP and IMP utilities you cannot do this. The only option is to import into a table of the same name (although you could change the schema which owns the table. However, you say you are on 11g.

Why not use the DataPump utility introduced in 10g, which replaces Import and Export. Because in 11g that utility offers the REMAP_TABLE option which does exactly what you want. Edit Having read the comments the OP added to another response while I was writing this, I don't think the REMAP_TABLE option will work in their case.It only renames new objects.

If a table with the original name exists in the target schema the import fails with ORA-39151. Sorry. Edit bis Given the solution the OP finally chose (drop existing table, replace with new table) there is a solution with Data Pump, which is to use the TABLE_EXISTS_ACTION={TRUNCATE | REPLACE} clause.

Choosing REPLACE drops the table whereas TRUNCATE merely, er, truncates it. In either case we have to worry about referential integrity constraints, but that is also an issue with the chosen solution. I post this addendum not for the OP but for the benefit of other seekers who find this page some time in the future.

Thank you, I will keep that in mind for the next time. At this point I have to use the originally exported data file (produced by exp). – scrible Dec 14 '09 at 17:18 +1: nice, I was looking for it in the doc, I was pretty sure it is possible with expdp – Vincent Malgrat Dec 14 '09 at 17:20.

Just import it into a table with the same name, then rename the table.

1 Agreed. If the table already exist, you must rename it, then import the data, rename the new table to the name you want and then rename the original table back to its original name. – Aaron Digulla Dec 14 '09 at 17:03 I have a system that's using the table in question (call it "A") and I don't want to take it down for the time it will take me to do the import.

I was hoping to do this: import data into table "B", then drop "A" and rename "B" to "A". That would avoid hours of downtime for the import. – scrible Dec 14 '09 at 17:05.

Create a view as select * from ... the table you want to import into, with the view matching the name of the table in the export. Ignore errors on import.

AFAIK, the view cannot have the same name as the existing table. – Thorsten Dec 14 '09 at 17:52 No, you create the view with the name that the import file uses, and the table with the name you want the table to have. – David Aldridge Dec 14 '09 at 19:53.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.