TRUST_EXISTING_TABLE_PARTITIONS is a new option for the parameter data_option in the impdp utility of Oracle 12.2.

Previously, If we are loading data to an existing partitioned table using impdp, then despite mentioning parallel option, partitions were getting loaded one by one, Which slowdown the import process.

 

As per Oracle document:

TRUST_EXISTING_TABLE_PARTITIONS — tells Data Pump to load partition data in parallel into existing tables. You should use this option when you are using Data Pump to create the table from the definition in the export database before the table data import is started. This is done as part of a migration when the metadata is static and can be moved before the databases are taken off line in order to migrate the data. Moving the metadata separately minimizes downtime. If you use this option and if other attributes of the database are the same (for example, character set), then the data from the export database goes to the same partitions in the import database.

 

With this new option, partitions will be loaded parallelly, when importing to existing partitions.

Lets test both the scenarios (with and without TRUST_EXISTING_TABLE_PARTITIONS option)

1. Without TRUST_EXISTING_TABLE_PARTITIONS option:(DEFAULT OPTION)

Here we are appending data to an existing partitioned table:

cat impdp_without.par

impdp dumpfile=test1_%U.dmp
logfile=imp_test1.log
directory=EXPDP
table_exists_action=APPEND
parallel=8

impdp parfile=impdp_without.par

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in AR8ISO8859P6 character set and UTF8 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA parfile=impdp_without.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "DBATEST"."CLASS_CON_ATTRIBUTES" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
^C

While the job is running, press control + c, and check the job status:

Import> status

Job: SYS_IMPORT_FULL_01
  Operation: IMPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 8
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /export/home/oracle/test1_%u.dmp
  Dump File: /export/home/oracle/test1_01.dmp
  Dump File: /export/home/oracle/test1_02.dmp
  Dump File: /export/home/oracle/test1_03.dmp
  Dump File: /export/home/oracle/test1_04.dmp
  Dump File: /export/home/oracle/test1_05.dmp
  Dump File: /export/home/oracle/test1_06.dmp
  Dump File: /export/home/oracle/test1_07.dmp
  Dump File: /export/home/oracle/test1_08.dmp

Worker 1 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:23:45
  Object status at: Monday, 02 October, 2017 12:23:45
  Process Name: DW00
  State: EXECUTING ----------------------------->>>>>>>>>>>>  ACTIVE WORKER PROCESS
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3348
  Completed Objects: 1
  Completed Bytes: 111,758,872
  Worker Parallelism: 1

Worker 2 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW01
  State: WORK WAITING --- > > waiting 

Worker 3 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW02
  State: WORK WAITING ---- >>> waiting 

Worker 4 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW03
  State: WORK WAITING  --->> waiting 

Worker 5 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW04
  State: WORK WAITING --- waiting 

Worker 6 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW05
  State: WORK WAITING -- waiting 

Worker 7 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW06
  State: WORK WAITING -- waiting 

Worker 8 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:23:
  Process Name: DW07
  State: WORK WAITING

  

What we see is, only one worker process is in EXECUTING state and rest are in WORK WAITING. i.e it is not using PARALLEL for import data.

This import took around 1 min 20 seconds to complete.

With TRUST_EXISTING_TABLE_PARTITIONS 

cat impdp_with.par

impdp dumpfile=test1_%U.dmp 
logfile=imp_test1.log 
directory=EXPDP 
table_exists_action=APPEND 
data_options=TRUST_EXISTING_TABLE_PARTITIONS   
parallel=8


impdp parfile=impdp_without.par

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in AR8ISO8859P6 character set and UTF8 NCHAR character set
Warning: possible data loss in character set conversions
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_with.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "DBATEST"."CLASS_CON_ATTRIBUTES" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
^C

Check the import job status

Import> status

Job: SYS_IMPORT_FULL_01
  Operation: IMPORT
  Mode: FULL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 8
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /export/home/oracle/test1_%u.dmp
  Dump File: /export/home/oracle/test1_01.dmp
  Dump File: /export/home/oracle/test1_02.dmp
  Dump File: /export/home/oracle/test1_03.dmp
  Dump File: /export/home/oracle/test1_04.dmp
  Dump File: /export/home/oracle/test1_05.dmp
  Dump File: /export/home/oracle/test1_06.dmp
  Dump File: /export/home/oracle/test1_07.dmp
  Dump File: /export/home/oracle/test1_08.dmp

Worker 1 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:40
  Object status at: Monday, 02 October, 2017 12:21:40
  Process Name: DW00
  State: EXECUTING
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3348
  Completed Objects: 1
  Completed Bytes: 111,758,872
  Worker Parallelism: 1

Worker 2 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:41
  Object status at: Monday, 02 October, 2017 12:21:41
  Process Name: DW01
  State: EXECUTING
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3564
  Completed Objects: 1
  Completed Bytes: 10,162,624
  Worker Parallelism: 1

Worker 3 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:41
  Object status at: Monday, 02 October, 2017 12:21:41
  Process Name: DW02
  State: EXECUTING
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3509
  Completed Objects: 1
  Completed Bytes: 16,423,720
  Worker Parallelism: 1

Worker 4 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Wednesday, 00 Sat, 0000 0:00:00
  Object status at: Monday, 02 October, 2017 12:21:
  Process Name: DW03
  State: WORK WAITING

Worker 5 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:41
  Object status at: Monday, 02 October, 2017 12:21:41
  Process Name: DW04
  State: EXECUTING
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3348
  Completed Objects: 1
  Completed Bytes: 12,713,912
  Worker Parallelism: 1

Worker 6 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:41
  Object status at: Monday, 02 October, 2017 12:21:41
  Process Name: DW05
  State: EXECUTING --- >>> 
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3348
  Completed Objects: 1
  Completed Bytes: 9,416
  Worker Parallelism: 1

Worker 7 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:42
  Object status at: Monday, 02 October, 2017 12:21:42
  Process Name: DW06
  State: EXECUTING ------ >>> 
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3348
  Completed Objects: 1
  Completed Bytes: 13,768
  Worker Parallelism: 1

Worker 8 Status:
  Instance ID: 1
  Instance name: POCDB
  Host name: sec60-1
  Object start time: Monday, 02 October, 2017 12:21:41
  Object status at: Monday, 02 October, 2017 12:21:41
  Process Name: DW07
  State: EXECUTING ---------------->>>> 
  Object Schema: WMBREL
  Object Name: STC_BREL_ATTRIBUTES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Partition: SYS_P3363
  Completed Objects: 1
  Completed Bytes: 8,424
  Worker Parallelism: 1
  

Now if we see, all the 8 worker process are in EXECUTING STATE.i.e partitions are getting loaded in PARALLEL.

And import took only 19 seconds. 🙂

This option really saves a lot of time, if the requirement is to APPEND data to an existing partition table.