Oracle 12c export and import schema with different data tablespace and index tablespace

exportimportoracleoracle-12ctablespaces

Suppose that I exported the schema objects from my Oracle 12c database using the original export utility as below:

exp userid=<export_schema_name>/<export_schema_password>@<db_string> file=<file_name>.dmp statistics=none log=<file_name>.log

The export_schema_name uses different tablespaces for data and indexes, let's called data_tbs_1 and idx_tbs_1. All tables in the export_schema_name are created as below:

create table <table_name>
(
    -- columns definition
)
tablespace <data_tbs_1>
/
--
alter table <table_name>
add constraint <constraint_name> primary key(<key_name>)
using index tablespace <idx_tbs_1>

Now I want to import the exported dump file into a new schema that I created as below (I already created a new data tablespace data_tbs_2):

create user <import_schema_name> identified by <import_schema_password>
default tablespace <data_tbs_2>

The imporst command I am going to use is:

imp userid=SYSTEM/<import_system_password>@<db_string> file=<file_name>.dmp statistics=none log=<file_name>.log fromuser=<export_schema_name> touser=<import_schema_name>

My questions are:

  1. If I do this, where are the data and indexes in stored? Are they still in the data_tbs_1 and idx_tbs_1 or they all in the data_tbs_2.
  2. If the indexes of the are still stored in idx_tbs_1, how do I switch them to a new index tablespace (say idx_tbs_2) that I will create?

Best Answer

I wonder why would you use EXP when you have EXPDP-more efficient and performant tool- on your hand?

Using IMPDP

If I do this, where are the data and indexes in stored? Are they still in the data_tbs_1 and idx_tbs_1 or they all in the data_tbs_2.

Yes. Since the import job executes the DDL to create those tables and the DDL includes TABLESPACE clause to define where to create the table not the default tablespace of the user. The objects will be created in their original tablespace regardless of the default tablespace of the new user.

This can be confirmed by exporting the dump file to SQL file. IMPDP Command-

impdp system@orclpdb directory=dp_dir dumpfile=expdp_jay.dmp sqlfile=jay_ddl.sql

DDL-

Here is an excerpt from the SQL file. 

CREATE TABLE "JAY"."TBL1"
   (    "TEXT" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ; -- Here you can see it has defined the tablespace to create the table.

If the indexes of the are still stored in idx_tbs_1, how do I switch them to a new index tablespace (say idx_tbs_2) that I will create?

You can use REMAP_TABLESPACE parameter while importing the dump.

Here is the DDL for the same table imported with the REMAP_TABLESPACE clause.

IMPDP command-

impdp system@orclpdb directory=dp_dir dumpfile=expdp_jay.dmp sqlfile=jay2_ddl.sql remap_tablespace=USERS:DATA

DDL-

CREATE TABLE "JAY"."TBL1"
   (    "TEXT" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA" ;

REMAP_TABLESPACE

Update:

The reason I still use the original exp and imp utilities is because my company still have that legacy data on previous version Oracle database.

If that is the case, I would import the objects in the same tablespace and later on I would migrate to another tablespace using ALTER INDEX REBUILD TABLESPACE <new_tbs> command.

Using IMP

If you have to use the original import tool then-

  • First of all, you need to extract the DDL from the dump file using
    indexfile parameter along with fromuser and touser parameter(Same schema name for fromuser and touser).

Example: imp system@orclpdb file=dumpfile.dmp fromuser=jay touser=jay indexfile=DDL.sql Note: It takes some efforts to edit the SQL file which contains REM keywords as well as output for the number of rows.

Sample:

[oracle@oracdbuat fra]$ cat DDL.sql

REM  CREATE TABLE "JAY"."EMP2" ("ID" NUMBER) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 TABLESPACE "DATALRG" LOGGING NOCOMPRESS ;
REM  ... 0 rows
REM  CREATE TABLE "JAY"."EMPLOYEE" ("ID" NUMBER) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 0 rows

  • Edit the SQL file generated by the import tool to change the tablespace for the objects.

  • Run the SQL script using SQLPLUS.

  • Disable all the constraints

  • Import the dump file with the parameter IGNORE=Y to ignore the warning about the already existed objects.

  • Enable the constraints.

Related Question