Page last modified 14:23, 27 Aug 2014 by alang

Import the tables to a different tablespace

內容表格

1. Pre-Create the Tablespace & Owner
2. Generate the indexfile with imp
3. Edit the idexfiles against your database
4. Import entire tables

版本為 13:16, 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
For example:
1. Source Tablespace: CDB
2. Target Tablespace: MYTEST_DB
Create the new owner(schema) and set the default tablespace as the new Tablespace above
For example:
1. Target Owner: MYTEST
2. Default Tablespace: MYTEST_DB

Generate the indexfile with imp

#> 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' -e '/rows$/d' -e 's/"CDB"/"MYTEST_DB"/g' idx_WIN_FDC_CDB.sql > idx_WIN_FDC_CDB.sql.ok

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

TIPs:

CDB is the source tablespace

MYTEST_DB is the target tablespace

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