Page last modified 13:13, 28 Jan 2014 by alang

Import the tables to a different tablespace

內容表格

1. Pre-Create the Tablespace & Owner
2. Import the dump file using INDEXFILE= option
3. Edit the idexfiles against your database
4. Import entire tables

版本為 13:13, 30 Nov 2024

Oracle offers no parameter to specify a different tablespace to import data into. Objects will be re-created in the tablespace they were originally exported from. One can alter this behaviour by following procedure:

Pre-Create the Tablespace & Owner

Create the new different Tablespace on target DB
Create the new owner(schema) and set the default tablespace as the new Tablespace above

Import the dump file using INDEXFILE= option

#> imp system/oracle fromuser=WIN_FDC_CDB touser=MYTEST file=./exp_WIN_FDC_CDB.dmp log=./imp_WIN_FDC_CDB.log indexfile=./idx_WIN_FDC_CDB.sql

Edit the idexfiles against your database

indexfile: idx_WIN_FDC_CDB.sql

#> sed -e 's/^REM//g' idx_WIN_FDC_CDB.sql -e '/rows$/d' -e 's/"CDB"/"MYTEST_DB"/g' > idx_WIN_FDC_CDB.sq.ok

#> sqlplus mytest/mypass @idx_WIN_FDC_CDB.sql.ok | tee idx_WIN_FDC_CDB.sql.ok.log

Import entire tables

imp system/oracle fromuser=WIN_FDC_CDB touser=MYTEST file=./exp_WIN_FDC_CDB.dmp log=./imp_WIN_FDC_CDB.log ignore=y

TIPs:

Add the option ignore=y