MAX_DATAPUMP_JOBS_PER_PDB
Description:-
- It determines the maximum number of concurrent Oracle Data Pump jobs per PDB.
- DBA can more easily govern Oracle Data Pump resource utilization
- The default value will not work for all databases. Database administrators will have to determine if the default value works well for their database.
- When this parameter has a value of AUTO, Oracle Data Pump will derive its actual value to be 50% of the SESSIONS initialization parameter.
- A value that is too large could cause Oracle Data Pump to consume too many system resources, while a value that is too small could prevent users from performing their Oracle Data Pump tasks.
- The main resource Oracle Data Pump uses is shared pool in the System Global Area (SGA) for the database. Parallel jobs increase the number of sessions and, depending on the job, the number of PQ slaves used.
Demo:-
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 DBWRPDB READ WRITE NO
parameter change:-
[oracle@dev19c ~]$ 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
Testing on Datapump
- two DataPump jobs are started and running:
1st session
[oracle@dev19c ~]$ export ORACLE_SID=dbwr
[oracle@dev19c ~]$ expdp system/oracle@dbwrpdb 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/********@dbwrpdb directory=DATA_PUMP_DIR dumpfile=uat1.dmp full=yes
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
—–> still running
2nd Session
[oracle@dev19c ~]$ export ORACLE_SID=dbwr
[oracle@dev19c ~]$ expdp system/oracle@dbwrpdb directory=DATA_PUMP_DIR dumpfile=dev1.dmp full=yes
Export: Release 19.0.0.0.0 – Production on Sun Sep 29 22:08:05 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_02″: system/********@dbwrpdb directory=DATA_PUMP_DIR dumpfile=dev1.dmp full=yes
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
—–> still running
3rd session
[oracle@dev19c ~]$ export ORACLE_SID=dbwr
[oracle@dev19c ~]$ expdp system/oracle@dbwrpdb directory=DATA_PUMP_DIR dumpfile=test1.dmp full=yes
Export: Release 19.0.0.0.0 – Production on Sun Sep 29 22:08:07 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
Join with me:-
Telegram:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba
