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.
