How to Manage the resource usage with concurrent job limit for each pluggable database using the Oracle Datapump in Oracle 19C
Posted by Mir Sayeed Hassan on March 9th, 2025
How to Manage the resource usage with concurrent job limit for each pluggable database using the Oracle Datapump in Oracle 19C
Brief: The parameter “max_datapump_jobs_per_pdb” is controls the maximum number of concurrent session in Data Pump jobs for each pluggable database.
Impact on this parameter: If you set this parameter to high value it may leads the datapump jobs consume more resources and also low value my restrict the users to executing jobs.
Usage of resources: The major resource of datapump utilize in SGA of the DB., we should maintain an balance b/w resource usage and job execution.
Check the status of database
SYS> select instance_name, open_mode, version from V$database, v$instance; INSTANCE_NAME OPEN_MODE VERSION ------------------------------------------- ora19cdb READ WRITE 19.0.0.0.0
Check the PDBs in this database
SYS> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------------ 2 PDB$SEED READ ONLY NO 4 PDB_TEST1 READ WRITE NO
Check the default value of this parameter “max_datapump_jobs_per_pdb” ” in CDB & PDB DBs
In Container Database (CDB)
SYS> show con_name CON_NAME --------- CDB$ROOT
SYS> show parameter max_datapump_jobs_per_pdb NAME TYPE VALUE ----------------------------------------------- max_datapump_jobs_per_pdb string 100
Note: The default value of this parameter is “100” which is generally more., set the value as per the requirement of the organization.
The parameter need to be set on container database itself & it will apply for all the pluggable database
Set it on Container database.
SYS> show con_name CON_NAME --------- CDB$ROOT
SYS> alter system set max_datapump_jobs_per_pdb=1; System altered.
Verify
SYS> show parameter max_datapump_jobs_per_pdb; NAME TYPE VALUE -------------------------------------------- max_datapump_jobs_per_pdb string 1
Connect to PDB & Check
SYS> alter session set container=PDB_TEST1; Session altered.
SYS> show parameter max_datapump_jobs_per_pdb; NAME TYPE VALUE ---------------------------------------------- max_datapump_jobs_per_pdb string 1
1st Session: export the schema “mir”
[oracle@testdb19c dump_bkp]$ expdp system@PDB_TEST1 directory=dump_bkp1 dumpfile=mir_1_09mar25.dmp logfile=mir_1_09mar25.log schemas=mir Export: Release 19.0.0.0.0 - Production on Sun Mar 9 08:28:46 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Password: ************** Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@PDB_TEST1 directory=dump_bkp1 dumpfile=mir_1_09mar25.dmp logfile=mir_1_09mar25.log schemas=mir Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE . . exported "MIR"."TEST1" 9.666 MB 73300 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u02/backup/dump_bkp1/mir_1_09mar25.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Mar 9 08:29:20 2025 elapsed 0 00:00:31
2nd Session: export the schema “mir”
[oracle@testdb19c dump_bkp]$ expdp system@PDB_TEST1 directory=dump_bkp1 dumpfile=mir_2_09mar25.dmp logfile=mir_2_09mar25.log schemas=mir Export: Release 19.0.0.0.0 - Production on Sun Mar 9 08:28:51 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Password: ************* 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_SCHEMA_02 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 (1) 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