Export of a single Oracle GIS table and import into a different table name

205
4
06-08-2018 03:59 AM
Highlighted
New Contributor III

Hi I am after the syntax to export and import a single GIS Oracle 9i table from 'live' to 'test' as part of our migration to ARC.

We currently dump the entire database out via a scheduled task as per below:

del e:\dumps\dev_sswdnet.dmp
exp SSW/****@DNET tablespaces=(GIS_DATA,GIS_ARCHIVE,GIS_INDEX) LOG=E:\Dumps\SSW_EXP.LOG FILE=DEV_SSW.DMP

Does anyone know how just to specify to export JUST the data in a single table from  sdf_asset_all.

I also need help with the import script if this is ok to ask

Reply
0 Kudos
4 Replies
Highlighted
New Contributor III

i have cracked the export

exp SSW/***@DNETTEST tables=(SDF_ASSET_ALL) LOG=E:\Dumps\SSW_EXP_SDF_ASSET_ALL.LOG FILE=DEV_SSW_SDF_ASSET_ALL.DMP

I now need to construct an imp script to take this data into a new table called SDF_ASSET_ALL_REPORTING rather than SDF_ASSET_ALL

Reply
0 Kudos
Highlighted
MVP Regular Contributor

Check these: REMAP_TABLE  & using the remap_table import option 

However, this rename will work during the import. Not if the table already exists.

Reply
0 Kudos
Highlighted
New Contributor III

will this work with a straight imp script not a impdp

imp ssw/***** tables=(sdf_asset_all) remap_table=sdf_asset_all_reporting

Reply
0 Kudos
Highlighted
MVP Regular Contributor

Why not just create a copy of the Table in the main database with whatever name you prefer, Export that out, import it in your test environment and then drop the copy in the live environment. That's way easier..

https://www.techonthenet.com/oracle/tables/create_table2.php  

CREATE TABLE New_Table_name AS SELECT * FROM Existing_table_Name;