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.

 

 

12.2impdporacle 12.2.partition ORACLE 12C


Related Topics

How to create encrypted tablespace in PDB( oracle 12c)
How to flashback a Pluggable database ( PDB) in oracle 12.2
Rollback database patch in oracle 12c
Perform Flashback in pluggable database(PDB) in oracle 12.2
ENABLE_PARALLEL_DML hint in oracle 12c
Apply patch on oracle 12.2 database ( Release update)
How to run expdp in pluggable database(PDB)
OUTBOUND_DBLINK_PROTOCOLS in oracle 12.2
TRUST_EXISTING_TABLE_PARTITIONS in oracle 12.2 datapump

You May Also Like

Useful DGMGRL commands in oracle dataguard
Install oracle enterprise manager cloud control 12c
Rollback database patch in oracle 12c
How to install postgres database on mac os/linux
Apply database patch from OEM Cloud control
ENABLE_PARALLEL_DML hint in oracle 12c
COMPRESSION in datapump oracle
TRUST_EXISTING_TABLE_PARTITIONS in oracle 12.2 datapump
How to setup dataguard broker configuration (DG broker) in 12c
Find user commits per minute in oracle database

From This Website

How to find weblogic version in oracle weblogic 12
How to create user in MySQL Database
Deinstall Management Agents Oracle 12c cloud control
LOG_CHECKPOINTS_TO_ALERT parameter in oracle
OGG-01028 partial record at sequence extract abended
How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT
Exception in thread -main" java.lang.OutOfMemoryError: GC overhead limit exceeded with bsu.sh
ERROR: permission denied for schema in postgres
How to enable flash recovery area in oracle database
SKIP_CONSTRAINT_ERRORS as DATA_OPTION in impdp