Importing Partitioned tables tips in oracle

One of the neatest new features that data pump offers is the ability to manage partition implementation during the import process. The source table had to have been partitioned when exported. Then during the import, there are three options: NONE, DEPARTITION and MERGE. NONE means to import the structure exactly the same as it was in the source database, therefore, partitioned if it was so.

DEPARTITION means to create a separate table for every partition and sub-partition. This might be useful when attempting to backwards port tables as non-partitioned for deployment in a development environment, i.e. developer sandbox. The table naming scheme is as follows: table_name_sys_pxxx or table_name_sys_subpxxx for partitions and sub-partitions, respectively. This is just table_name || parititon_name. Here is an example of a schema with fairly complex partitioning being imported with each of these three options.

C$\> impdp bert/bert directory=data_pump_dir dumpfile=bmf.dmp remap_schema=BMF:BMF2 partition_options=none

C$\> impdp bert/bert directory=data_pump_dir dumpfile=bmf.dmp remap_schema=BMF:BMF3 partition_options=departition

C$\> impdp bert/bert directory=data_pump_dir dumpfile=bmf.dmp remap_schema=BMF:BMF4 partition_options=merge

Note that schema BMF2 has all the exact same counts as BMF for tables vs. partitions vs. subpartitions. So the NONE option did, in fact, import it exactly the same. Now look at BMF, which had been imported with the DEPARTITION option. It has 1387 tables! Finally, BMF4 imported with MERGE has just the eight tables and no partitions or subpartitions.

SQL>  select owner, count(*) from dba_tables where owner like 'BMF%' group by owner order by 1;

BMF                                     8

BMF2                                    8

BMF3                                 1387

BMF4                                    8

SQL>  select table_owner, count(*) from dba_tab_partitions where table_owner like 'BMF%' group by table_owner order by 1;

BMF                                   194

BMF2                                  194

SQL>  select table_owner, count(*) from dba_tab_subpartitions where table_owner like 'BMF%' group by table_owner order by 1;

BMF                                  1360

BMF2                                 1360

Import Remapping Options

Often during the import process, something may need to be loaded in the target differently than it was in the source. Back in the old export days, there were thefromuser and touser parameters to load objects into a different schema. And while that was handy, it was never quite enough. Data pump has addressed this need by adding a plethora of remapping options. Look back at the prior section where there was the partition example. Was the remap_schema option being used to load into BMF2, BMF3 and BMF4 noticed? That is pretty much the same as the fromuser andtouser concept. But there is more. Do another BMF schema remap where there are the objects to be created in a different tablespace as well. Here is how it is done:

C:\> impdp bert/bert directory=data_pump_dir dumpfile=bmf.dmp remap_schema=BMF:BMF5 remap_tablespace=users:accounting partition_options=none

If one wanted to rename tables during the import, there is the remap_table parameter. The most powerful remapping concept is remap_data. With this, a table can be loaded and the values can be changed for columns using a function that returns the same data type as that column. The function itself can be practically unlimited, except not permitted to issue either COMMIT and/or ROLLBACK commands. A strong argument could be made that this makes the Data Pump a sort of ETL (Extract, Translate and Load) toolset now because this remapping function can handle the translate portion.