impdp one dumpfile to multiple tablespaces

Question

In my Oracle 11.2 I have a schema MAIN with 500 tables distributed on 5 tablespaces. 100 tables in TBS1, 100 in TBS2 and so on... Default for user MAIN is TBS1.

Sometimes for some reason i have to export the schema MAIN

expdp \'/ as sysdba\' schemas=main dumpfile=main.dmp directory=dpdumps

and import it back to the same tablespaces

impdp \'/ as sysdba\' directory=dpdumps dumpfile=main.dmp version=11.2

but after the import i have ALL the objects in one default TBS1

How can i perform impdp in order to get the tables to the original tablespaces as they were before export? Where i'm wrong?

Thank you.

Those are the entire command lines you use (aside from spelling `schemas` wrong)? You dob't have a `TRANSFORM` argument, say, or a parameter file that's doing anything extra? — Alex Poole, Apr 07 '16 at 16:07

score 0 · Answer 1 · answered Aug 12 '20 at 11:12

I honestly don't know what you are actually doing, but that is not how datapump works. If the tables were defined with different tablespaces, the import will keep the original specification unless a TRANSFORMor REMAP_TABLESPACE is specified. Otherwise, it will work as expected

Test case

SQL> create user my_test identified by "Oracle_1" ;

User created.

SQL> alter user my_test quota unlimited on tbrepdata  ;

User altered.

SQL> alter user my_test quota unlimited on tbhisdata ;

User altered.

SQL> grant create table to my_test ;

Grant succeeded.

SQL> create table my_test.test_one_tbs ( c1 number ) tablespace tbrepdata ;

Table created.

SQL> create table my_test.test_two_tbs ( c1 number ) tablespace tbhisdata ;

Table created.

SQL> declare
  2  begin
  3  for i in 1 .. 1000
  4  loop
  5  insert into my_test.test_one_tbs values ( i );
  6  insert into my_test.test_two_tbs values ( i );
  7  end loop;
  8  commit ;
  9* end;
  /

PL/SQL procedure successfully completed.

SQL> select count(*) from my_test.test_one_tbs ;

  COUNT(*)
----------
      1000

SQL> select count(*) from my_test.test_two_tbs ;

  COUNT(*)
----------
      1000

SQL> host expdp directory=DIR_DATAPUMP_EXPORT dumpfile=mytest.dmp schemas=my_test

..........
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "MY_TEST"."TEST_ONE_TBS"                    12.76 KB    1000 rows
. . exported "MY_TEST"."TEST_TWO_TBS"                    12.76 KB    1000 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /orabatch/ftpcpl/export/mytest.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 12 13:05:23 2020 elapsed 0 00:00:50


SQL> drop table my_test.test_one_tbs purge;

Table dropped.

SQL> drop table my_test.test_two_tbs purge ;

Table dropped.

SQL> host impdp directory=DIR_DATAPUMP_EXPORT dumpfile=mytest.dmp

......
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MY_TEST"."TEST_ONE_TBS"                    12.76 KB    1000 rows
. . imported "MY_TEST"."TEST_TWO_TBS"                    12.76 KB    1000 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Wed Aug 12 13:06:37 2020 elapsed 0 00:00:13


SQL> select tablespace_name , segment_name from dba_segments where owner = 'MY_TEST' ;

TABLESPACE_NAME
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
TBHISDATA
TEST_TWO_TBS

TBREPDATA
TEST_ONE_TBS


SQL>

impdp one dumpfile to multiple tablespaces

1 Answers1