Does Data Pump import only serially into PDBs?

Daniel and I did Web Seminars this week. One of them was about the different migration strategies. We did talk a lot about Data Pump. One attendee mentioned that Data Pump Import does not work parallel into PDBs in Oracle 12.2.0.1 and Oracle 18c. We were skeptical, and today I tried out to check: Does Data Pump import only serially into PDBs?

Very simple test setup

For this test, I take a schema export from an 11.2.0.4 database in our Hands-On Lab, the TPCC user HammerORA uses in the UPGR database.

DIRECTORY=DATA_PUMP_DIR
DUMPFILE=dumpfile.dmp
LOGFILE=logfile.log
SCHEMAS=TPCC
METRICS=YES
EXCLUDE=STATISTICS
PARALLEL=6

And when I call it with:

expdp system/oracle parfile=exp.par

a minute later I have a dumpfile of a bit more than 300MB size.

Import Preparation

Then I’m going to import it into 3 different databases, all of course PDBs.

  • Oracle 12.2.0.1 with the July 2020 RU
  • Oracle 18.8.0
  • Oracle 19.8.0

This is my parameter file for all 3 databases:

DIRECTORY=DATA_PUMP_DIR
DUMPFILE=dumpfile.dmp
LOGFILE=logfile.log
SCHEMAS=TPCC
METRICS=YES
LOGTIME=ALL
PARALLEL=6

And I need to create a few things upfront:

alter session set container=PDB1;
create tablespace TPCCTAB datafile '/u02/oradata/CDB2/pdb1/tpcc01.dbf' size 500M autoextend on online;
create user tpcc identified by tpcc default tablespace TPCCTAB;
grant connect, resource, dba to tpcc;

Afterwards I can kick off the import simply with:

impdp system/oracle@PDB1 parfile=imp.par

Import Log File Comparison

Now let me compare the logs of the 3 different runs and check whether only 1 worker is action.

Oracle 12.2.0.1.0

$ impdp system/oracle@pdb3 parfile=imp.par

Import: Release 12.2.0.1.0 - Production on Thu Oct 15 17:40:19 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
15-OCT-20 17:40:20.739: W-1 Startup took 1 seconds
15-OCT-20 17:40:21.686: W-1 Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
15-OCT-20 17:40:21.953: Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@pdb3 parfile=imp.par 
15-OCT-20 17:40:21.996: W-1 Processing object type SCHEMA_EXPORT/USER
15-OCT-20 17:40:22.244: ORA-31684: Object type USER:"TPCC" already exists

15-OCT-20 17:40:22.279: W-1      Completed 1 USER objects in 0 seconds
15-OCT-20 17:40:22.279: W-1      Completed by worker 1 1 USER objects in 0 seconds
15-OCT-20 17:40:22.288: W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
15-OCT-20 17:40:22.371: W-1      Completed 1 SYSTEM_GRANT objects in 0 seconds
15-OCT-20 17:40:22.371: W-1      Completed by worker 1 1 SYSTEM_GRANT objects in 0 seconds
15-OCT-20 17:40:22.372: W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT
15-OCT-20 17:40:22.454: W-1      Completed 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 17:40:22.454: W-1      Completed by worker 1 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 17:40:22.456: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
15-OCT-20 17:40:22.530: W-1      Completed 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 17:40:22.530: W-1      Completed by worker 1 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 17:40:22.531: W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
15-OCT-20 17:40:22.605: W-1      Completed 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 17:40:22.605: W-1      Completed by worker 1 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 17:40:22.606: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
15-OCT-20 17:40:22.891: W-1      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:40:22.891: W-1      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:40:22.892: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
15-OCT-20 17:40:23.547: W-2 Startup took 3 seconds
15-OCT-20 17:40:23.584: W-5 Startup took 4 seconds
15-OCT-20 17:40:23.612: W-6 Startup took 4 seconds
15-OCT-20 17:40:23.634: W-3 Startup took 3 seconds
15-OCT-20 17:40:23.655: W-4 Startup took 3 seconds
15-OCT-20 17:40:24.681: W-1      Completed 9 TABLE objects in 2 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 1 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 2 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 3 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 4 4 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 5 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 6 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.725: W-2 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
15-OCT-20 17:40:25.555: W-4 . . imported "TPCC"."HISTORY"                            7.444 MB  155395 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.556: W-2 . . imported "TPCC"."ORDERS"                             5.075 MB  155584 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.568: W-1 . . imported "TPCC"."ITEM"                               7.231 MB  100000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.707: W-5 . . imported "TPCC"."CUSTOMER"                           47.97 MB   90000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.708: W-3 . . imported "TPCC"."STOCK"                              88.64 MB  300000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.709: W-2 . . imported "TPCC"."NEW_ORDER"                          397.4 KB   28669 rows in 0 seconds using direct_path
15-OCT-20 17:40:25.749: W-2 . . imported "TPCC"."DISTRICT"                           11.92 KB      30 rows in 0 seconds using direct_path
15-OCT-20 17:40:25.780: W-2 . . imported "TPCC"."WAREHOUSE"                          8.578 KB       3 rows in 0 seconds using direct_path
15-OCT-20 17:40:29.436: W-6 . . imported "TPCC"."ORDER_LINE"                         100.2 MB 1556919 rows in 5 seconds using direct_path
15-OCT-20 17:40:29.466: W-4 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
15-OCT-20 17:40:29.614: W-5      Completed 5 PROCEDURE objects in 0 seconds
15-OCT-20 17:40:29.614: W-5      Completed by worker 1 5 PROCEDURE objects in 0 seconds
15-OCT-20 17:40:29.615: W-5 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
15-OCT-20 17:40:29.844: W-2      Completed 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 17:40:29.844: W-2      Completed by worker 3 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 17:40:29.915: W-4 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
15-OCT-20 17:40:30.524: W-2      Completed 8 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 1 1 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 2 2 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 3 2 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 4 1 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 5 1 INDEX objects in 1 seconds
15-OCT-20 17:40:30.524: W-2      Completed by worker 6 1 INDEX objects in 1 seconds
15-OCT-20 17:40:30.525: W-2 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
15-OCT-20 17:40:30.607: W-6      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:40:30.607: W-6      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:40:30.653: W-4      Completed 9 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 5 seconds
15-OCT-20 17:40:30.743: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Oct 15 17:40:30 2020 elapsed 0 00:00:11

Oracle 18.8.0

$ impdp system/oracle@PDB1 parfile=imp.par

Import: Release 18.0.0.0.0 - Production on Thu Oct 15 17:24:57 2020
Version 18.8.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
15-OCT-20 17:24:59.590: W-1 Startup took 0 seconds
15-OCT-20 17:25:00.412: W-1 Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
15-OCT-20 17:25:00.688: Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@PDB1 parfile=imp.par 
15-OCT-20 17:25:00.734: W-1 Processing object type SCHEMA_EXPORT/USER
15-OCT-20 17:25:00.905: ORA-31684: Object type USER:"TPCC" already exists

15-OCT-20 17:25:00.949: W-1      Completed 1 USER objects in 0 seconds
15-OCT-20 17:25:00.949: W-1      Completed by worker 1 1 USER objects in 0 seconds
15-OCT-20 17:25:00.958: W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
15-OCT-20 17:25:01.065: W-1      Completed 1 SYSTEM_GRANT objects in 1 seconds
15-OCT-20 17:25:01.065: W-1      Completed by worker 1 1 SYSTEM_GRANT objects in 1 seconds
15-OCT-20 17:25:01.067: W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT
15-OCT-20 17:25:01.167: W-1      Completed 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 17:25:01.167: W-1      Completed by worker 1 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 17:25:01.168: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
15-OCT-20 17:25:01.270: W-1      Completed 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 17:25:01.270: W-1      Completed by worker 1 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 17:25:01.272: W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
15-OCT-20 17:25:01.376: W-1      Completed 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 17:25:01.376: W-1      Completed by worker 1 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 17:25:01.377: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
15-OCT-20 17:25:01.679: W-1      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:25:01.679: W-1      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:25:01.680: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
15-OCT-20 17:25:02.415: W-5 Startup took 1 seconds
15-OCT-20 17:25:02.452: W-3 Startup took 1 seconds
15-OCT-20 17:25:02.491: W-4 Startup took 1 seconds
15-OCT-20 17:25:02.524: W-6 Startup took 1 seconds
15-OCT-20 17:25:02.560: W-2 Startup took 1 seconds
15-OCT-20 17:25:03.778: W-1      Completed 9 TABLE objects in 2 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 1 1 TABLE objects in 1 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 2 4 TABLE objects in 1 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 3 1 TABLE objects in 1 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 4 1 TABLE objects in 1 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 5 1 TABLE objects in 0 seconds
15-OCT-20 17:25:03.778: W-1      Completed by worker 6 1 TABLE objects in 1 seconds
15-OCT-20 17:25:03.845: W-5 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
15-OCT-20 17:25:05.204: W-1 . . imported "TPCC"."ITEM"                               7.231 MB  100000 rows in 2 seconds using direct_path
15-OCT-20 17:25:05.211: W-5 . . imported "TPCC"."ORDERS"                             5.075 MB  155584 rows in 2 seconds using direct_path
15-OCT-20 17:25:05.212: W-3 . . imported "TPCC"."CUSTOMER"                           47.97 MB   90000 rows in 2 seconds using direct_path
15-OCT-20 17:25:05.237: W-2 . . imported "TPCC"."HISTORY"                            7.444 MB  155395 rows in 2 seconds using direct_path
15-OCT-20 17:25:05.240: W-4 . . imported "TPCC"."STOCK"                              88.64 MB  300000 rows in 2 seconds using direct_path
15-OCT-20 17:25:05.307: W-5 . . imported "TPCC"."NEW_ORDER"                          397.4 KB   28669 rows in 0 seconds using direct_path
15-OCT-20 17:25:05.350: W-5 . . imported "TPCC"."DISTRICT"                           11.92 KB      30 rows in 0 seconds using direct_path
15-OCT-20 17:25:05.391: W-5 . . imported "TPCC"."WAREHOUSE"                          8.578 KB       3 rows in 0 seconds using direct_path
15-OCT-20 17:25:12.017: W-6 . . imported "TPCC"."ORDER_LINE"                         100.2 MB 1556919 rows in 9.00000000000000000000000000000000000003 seconds using direct_path
15-OCT-20 17:25:12.048: W-1 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
15-OCT-20 17:25:12.218: W-3      Completed 5 PROCEDURE objects in 0 seconds
15-OCT-20 17:25:12.218: W-3      Completed by worker 4 5 PROCEDURE objects in 0 seconds
15-OCT-20 17:25:12.219: W-3 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
15-OCT-20 17:25:12.532: W-5      Completed 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 17:25:12.532: W-5      Completed by worker 2 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 17:25:12.618: W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
15-OCT-20 17:25:13.599: W-5      Completed 8 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 1 1 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 2 2 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 3 1 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 4 1 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 5 2 INDEX objects in 1 seconds
15-OCT-20 17:25:13.599: W-5      Completed by worker 6 1 INDEX objects in 1 seconds
15-OCT-20 17:25:13.600: W-5 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
15-OCT-20 17:25:13.709: W-6      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:25:13.709: W-6      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 17:25:13.764: W-4      Completed 9 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 9 seconds
15-OCT-20 17:25:13.875: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Oct 15 17:25:13 2020 elapsed 0 00:00:15

Oracle 19.8.0

$ impdp system/oracle@pdb1 parfile=imp.par

Import: Release 19.0.0.0.0 - Production on Thu Oct 15 18:18:03 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
15-OCT-20 18:18:05.268: W-1 Startup took 1 seconds
15-OCT-20 18:18:06.233: W-1 Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
15-OCT-20 18:18:06.504: Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/********@pdb1 parfile=imp.par 
15-OCT-20 18:18:06.552: W-1 Processing object type SCHEMA_EXPORT/USER
15-OCT-20 18:18:06.750: ORA-31684: Object type USER:"TPCC" already exists

15-OCT-20 18:18:06.798: W-1      Completed 1 USER objects in 0 seconds
15-OCT-20 18:18:06.798: W-1      Completed by worker 1 1 USER objects in 0 seconds
15-OCT-20 18:18:06.809: W-1 Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
15-OCT-20 18:18:06.918: W-1      Completed 1 SYSTEM_GRANT objects in 0 seconds
15-OCT-20 18:18:06.918: W-1      Completed by worker 1 1 SYSTEM_GRANT objects in 0 seconds
15-OCT-20 18:18:06.919: W-1 Processing object type SCHEMA_EXPORT/ROLE_GRANT
15-OCT-20 18:18:07.025: W-1      Completed 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 18:18:07.025: W-1      Completed by worker 1 2 ROLE_GRANT objects in 0 seconds
15-OCT-20 18:18:07.027: W-1 Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
15-OCT-20 18:18:07.156: W-1      Completed 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 18:18:07.156: W-1      Completed by worker 1 1 DEFAULT_ROLE objects in 0 seconds
15-OCT-20 18:18:07.157: W-1 Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
15-OCT-20 18:18:07.260: W-1      Completed 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 18:18:07.260: W-1      Completed by worker 1 1 TABLESPACE_QUOTA objects in 0 seconds
15-OCT-20 18:18:07.263: W-1 Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
15-OCT-20 18:18:07.601: W-1      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 18:18:07.601: W-1      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 18:18:07.602: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
15-OCT-20 18:18:08.410: W-4 Startup took 1 seconds
15-OCT-20 18:18:08.426: W-2 Startup took 1 seconds
15-OCT-20 18:18:08.448: W-5 Startup took 1 seconds
15-OCT-20 18:18:08.492: W-6 Startup took 1 seconds
15-OCT-20 18:18:08.568: W-3 Startup took 1 seconds
15-OCT-20 18:18:09.684: W-4      Completed 9 TABLE objects in 2 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 1 1 TABLE objects in 1 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 2 1 TABLE objects in 1 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 3 4 TABLE objects in 1 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 4 1 TABLE objects in 0 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 5 1 TABLE objects in 1 seconds
15-OCT-20 18:18:09.684: W-4      Completed by worker 6 1 TABLE objects in 1 seconds
15-OCT-20 18:18:09.731: W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
15-OCT-20 18:18:10.659: W-3 . . imported "TPCC"."HISTORY"                            7.444 MB  155395 rows in 1 seconds using direct_path
15-OCT-20 18:18:10.674: W-4 . . imported "TPCC"."ITEM"                               7.231 MB  100000 rows in 1 seconds using direct_path
15-OCT-20 18:18:10.734: W-1 . . imported "TPCC"."ORDERS"                             5.075 MB  155584 rows in 1 seconds using direct_path
15-OCT-20 18:18:10.843: W-1 . . imported "TPCC"."NEW_ORDER"                          397.4 KB   28669 rows in 0 seconds using direct_path
15-OCT-20 18:18:10.939: W-5 . . imported "TPCC"."CUSTOMER"                           47.97 MB   90000 rows in 1 seconds using direct_path
15-OCT-20 18:18:10.987: W-1 . . imported "TPCC"."DISTRICT"                           11.92 KB      30 rows in 0 seconds using direct_path
15-OCT-20 18:18:10.998: W-2 . . imported "TPCC"."STOCK"                              88.64 MB  300000 rows in 1 seconds using direct_path
15-OCT-20 18:18:11.031: W-1 . . imported "TPCC"."WAREHOUSE"                          8.578 KB       3 rows in 1 seconds using direct_path
15-OCT-20 18:18:15.068: W-6 . . imported "TPCC"."ORDER_LINE"                         100.2 MB 1556919 rows in 6 seconds using direct_path
15-OCT-20 18:18:15.107: W-3 Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
15-OCT-20 18:18:15.267: W-5      Completed 5 PROCEDURE objects in 0 seconds
15-OCT-20 18:18:15.267: W-5      Completed by worker 4 5 PROCEDURE objects in 0 seconds
15-OCT-20 18:18:15.268: W-5 Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
15-OCT-20 18:18:15.546: W-1      Completed 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 18:18:15.546: W-1      Completed by worker 2 5 ALTER_PROCEDURE objects in 0 seconds
15-OCT-20 18:18:15.651: W-3 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
15-OCT-20 18:18:16.369: W-1      Completed 8 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 1 2 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 2 2 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 3 1 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 4 1 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 5 1 INDEX objects in 1 seconds
15-OCT-20 18:18:16.369: W-1      Completed by worker 6 1 INDEX objects in 1 seconds
15-OCT-20 18:18:16.370: W-1 Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
15-OCT-20 18:18:16.476: W-6      Completed 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 18:18:16.476: W-6      Completed by worker 1 1 PROCACT_SCHEMA objects in 0 seconds
15-OCT-20 18:18:16.541: W-3      Completed 9 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 6 seconds
15-OCT-20 18:18:16.666: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Thu Oct 15 18:18:16 2020 elapsed 0 00:00:13

Conclusion

In all 3 cases, I see that 6 workers have been started. This is intended and expected as I used PARALLEL=6 in the parameter file.

And I see also according to the W-n code after the time stamp, that already in Oracle 12.2.0.1 the 6 workers distribute the work, and none of them is just having a great time doing nothing. The HISTORY table data gets imported by W-4, W-2 takes the ORDERS, the NEW_ORDER, the DISTRICT and the WAREHOUSE tables.

15-OCT-20 17:40:23.547: W-2 Startup took 3 seconds
15-OCT-20 17:40:23.584: W-5 Startup took 4 seconds
15-OCT-20 17:40:23.612: W-6 Startup took 4 seconds
15-OCT-20 17:40:23.634: W-3 Startup took 3 seconds
15-OCT-20 17:40:23.655: W-4 Startup took 3 seconds
15-OCT-20 17:40:24.681: W-1      Completed 9 TABLE objects in 2 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 1 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 2 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 3 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 4 4 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 5 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.681: W-1      Completed by worker 6 1 TABLE objects in 1 seconds
15-OCT-20 17:40:24.725: W-2 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
15-OCT-20 17:40:25.555: W-4 . . imported "TPCC"."HISTORY"                            7.444 MB  155395 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.556: W-2 . . imported "TPCC"."ORDERS"                             5.075 MB  155584 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.568: W-1 . . imported "TPCC"."ITEM"                               7.231 MB  100000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.707: W-5 . . imported "TPCC"."CUSTOMER"                           47.97 MB   90000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.708: W-3 . . imported "TPCC"."STOCK"                              88.64 MB  300000 rows in 1 seconds using direct_path
15-OCT-20 17:40:25.709: W-2 . . imported "TPCC"."NEW_ORDER"                          397.4 KB   28669 rows in 0 seconds using direct_path
15-OCT-20 17:40:25.749: W-2 . . imported "TPCC"."DISTRICT"                           11.92 KB      30 rows in 0 seconds using direct_path
15-OCT-20 17:40:25.780: W-2 . . imported "TPCC"."WAREHOUSE"                          8.578 KB       3 rows in 0 seconds using direct_path
15-OCT-20 17:40:29.436: W-6 . . imported "TPCC"."ORDER_LINE"                         100.2 MB 1556919 rows in 5 seconds using direct_path

The other logs looks more or less similar.

I compared it also with the logs of a 12.2.0.1 non-CDB database:

15-OCT-20 18:43:52.927: W-6 Startup took 3 seconds
15-OCT-20 18:43:52.963: W-2 Startup took 3 seconds
15-OCT-20 18:43:52.976: W-5 Startup took 3 seconds
15-OCT-20 18:43:52.994: W-3 Startup took 3 seconds
15-OCT-20 18:43:53.004: W-4 Startup took 3 seconds
15-OCT-20 18:43:53.970: W-1      Completed 9 TABLE objects in 1 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 1 1 TABLE objects in 1 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 2 1 TABLE objects in 1 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 3 1 TABLE objects in 0 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 4 4 TABLE objects in 0 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 5 1 TABLE objects in 1 seconds
15-OCT-20 18:43:53.970: W-1      Completed by worker 6 1 TABLE objects in 1 seconds
15-OCT-20 18:43:54.018: W-6 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
15-OCT-20 18:43:54.693: W-6 . . imported "TPCC"."ORDERS"                             5.075 MB  155584 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.698: W-4 . . imported "TPCC"."HISTORY"                            7.444 MB  155395 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.700: W-1 . . imported "TPCC"."ITEM"                               7.231 MB  100000 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.799: W-6 . . imported "TPCC"."NEW_ORDER"                          397.4 KB   28669 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.856: W-3 . . imported "TPCC"."CUSTOMER"                           47.97 MB   90000 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.869: W-6 . . imported "TPCC"."DISTRICT"                           11.92 KB      30 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.917: W-6 . . imported "TPCC"."WAREHOUSE"                          8.578 KB       3 rows in 0 seconds using direct_path
15-OCT-20 18:43:54.985: W-5 . . imported "TPCC"."STOCK"                              88.64 MB  300000 rows in 0 seconds using direct_path
15-OCT-20 18:43:59.097: W-2 . . imported "TPCC"."ORDER_LINE"                         100.2 MB 1556919 rows in 5 seconds using direct_path

But I don’t see any difference, neither between the different PDB versions nor compared to a non-CDB.

Hence, no, the assumption was not correct.

In Oracle Database 12.2.0.1, 18c and 19c you can import with parallel workers into PDBs as well. I don’t see any restriction or any other reason why the import into a PDB should not happen in parallel. And it does also in Oracle 12.1.0 in case somebody is using it.

In case you’ve asked yourself why you don’t see parallel workers for the Meta Data part, this is easy to explain. There is not enough information to import. When you’d have more than 80 users to import, then you would see parallel workers in the meta data import part as well. The meta data is kept in XML in the dump file – and one file contains maximum of 80 users.

–Mike

Share this: