Oracle — Import data into a table with a different

问题:

I have a large (multi-GB) data file exported from an Oracle table. I want to import this data into another Oracle instance, but I want the table name to be different from the original table. Is this possible? How?

Both importing and exporting systems are Oracle 11g. The table includes a BLOB column, if this makes any difference.

Thanks!

UPDATES:

The idea here was to update a table while keeping the downtime on the system that's using it to a minimum. The solution (based on Vincent Malgrat's answer and APC's update) is:

1. Assuming our table name is A
2. Make a temp schema TEMP_SCHEMA
3. Import our data into TEMP_SCHEMA.A
4. CREATE REAL_SCHEMA.B AS SELECT * FROM TEMP_SCHEMA.A
5. DROP TABLE REAL_SCHEMA.A Rename REAL_SCHEMA.A to REAL_SCHEMA.A_OLD
6. Rename REAL_SCHEMA.B to REAL_SCHEMA.A
7. DROP REAL_SCHEMA.A_OLD

This way, the downtime is only during steps 4 and 5, both should be independent of data size. I'll post an update here if this does not work :-)

回答1:

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.

回答2:

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.

回答3:

Use the option REMAP_TABLE=EXISITNG_TABLE_NAME:NEW_TABLE_NAME in impdp. This works in 11gR2.

回答4:

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

回答5:

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.