2

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?
  • Why do you want to use the old export tool? From a comment I learned that you want to import to or export from a database with lower version. So what do you want to do, what version are involved and what happens if you try this out for a single table with an index? – miracle173 Jan 15 '18 at 8:36
1

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.

  • Thanks for your answer. 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. Does the remap_tablespace method works on original imp utility? Also, from the DDL created when using the impdb utility, it seems that it remaps only data tablespace. Can I also remap the index tablespace because my tables also use the "using index tablespace" clause. – phamhoangsy2909 Jan 15 '18 at 6:05
  • 1
    concerning the old exp/imp: No, the index file does not contain the ddl of the objects of the dump, it contains only the ddl of the indexes of the dump.There is the SHOW=YES option you can try to create SQL statements, but this needs some edit of this file before you can run it with sqlplus. Maybe there is a better approach then creating the objects with sqlplus. How do you know that the OP wants to import in an 8i Oracle? – miracle173 Jan 15 '18 at 8:54
  • @miracle173: Thanks. Yeah, indexfile alone is not enough to generate DDL for both tables and indexes. According to OP's comment, "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", I outlined the solution. I would love to know if it is possible to use impdp to import the dump file exported using exp. I didn't mention that he has to import it in Oracle 8i. Instead, I said if OP needs to use imp to import the dump file exported using exp. – JSapkota Jan 15 '18 at 9:28
  • It is not possible to use impdp to import a dumpfile created by exp. You refeferenced th Oracle 8i manual so I assumed it is an import to an 8i database – miracle173 Jan 15 '18 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.