Oracle DBA

MAX_DATAPUMP_JOBS_PER_PDB parameter controls the maximum number of concurrent Oracle Data Pump jobs allowed per Pluggable Database (PDB), enabling database administrators (DBAs) to better manage and govern Oracle Data Pump resource usage.

Key Details

• Default Behavior: The default setting may not suit all databases. DBAs should evaluate if the default value meets the specific needs of their database environments.
• AUTO Setting: When set to AUTO, Oracle Data Pump dynamically determines this parameter’s value as 50% of the SESSIONS initialization parameter.
• Impact of Values:
  • High Values: Setting this parameter too high may lead Oracle Data Pump to consume excessive system resources.
  • Low Values: A value that is too low may restrict users from executing necessary Data Pump jobs, potentially impacting workload efficiency.

Resource Utilization

The primary resource Oracle Data Pump utilizes is the Shared Pool within the System Global Area (SGA) of the database. Parallel jobs increase the session count, and depending on the workload, may also increase the number of parallel query (PQ) slaves used. Proper tuning of this parameter is essential to maintain an optimal balance between resource usage and job execution capacity.

Parameter change

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 ODBAPDB READ WRITE NO

[oracle@odbadb ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 29 21:55:48 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show parameter MAX_DATAPUMP_JOBS_PER_PDB

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_datapump_jobs_per_pdb string 100

- for Multitenant, in CDB$ROOT:

SQL> alter system set MAX_DATAPUMP_JOBS_PER_PDB=2 container=all;
System altered.

- for non-Multitenant:-

SQL> alter system set MAX_DATAPUMP_JOBS_PER_PDB=2;
System altered.

SQL> show parameter MAX_DATAPUMP_JOBS_PER_PDB

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_datapump_jobs_per_pdb string    2

First Session running Export Data Pump

[oracle@odbadb ~]$ export ORACLE_SID=odba
[oracle@odbadb ~]$ expdp system/oracle@odbapdb directory=DATA_PUMP_DIR dumpfile=uat1.dmp full=yes

Export: Release 19.0.0.0.0 – Production on Sun Sep 29 22:08:02 2019
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/********@odbapdb directory=DATA_PUMP_DIR dumpfile=data_dump_01.dmp full=yes
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Session continue to run………

Second Session running Export Data Pump

[oracle@odbadb ~]$ export ORACLE_SID=odba
[oracle@odbadb ~]$ expdp system/oracle@odbapdb directory=DATA_PUMP_DIR dumpfile=uat1.dmp full=yes

Export: Release 19.0.0.0.0 – Production on Sun Sep 29 22:08:02 2019
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/********@odbapdb directory=DATA_PUMP_DIR dumpfile=data_dump_01.dmp full=yes
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Session continue to run………

Initiating 3rd Session of Export Data Pump

[oracle@odbadb ~]$ export ORACLE_SID=odba
[oracle@odbadb ~]$ expdp system/oracle@odbapdb directory=DATA_PUMP_DIR dumpfile=uat1.dmp full=yes

Export: Release 19.0.0.0.0 – Production on Sun Sep 29 22:08:02 2019
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_FULL_03 for user SYSTEM
ORA-06512: at “SYS.KUPV$FT”, line 1142
ORA-06512: at “SYS.KUPV$FT”, line 1744
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT_INT”, line 1099
ORA-39391: maximum number of Data Pump jobs (2) exceeded
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPV$FT_INT”, line 969
ORA-06512: at “SYS.KUPV$FT”, line 1646
ORA-06512: at “SYS.KUPV$FT”, line 1103