Tuesday, October 7, 2014

To Change Parallelism for a data pump job which is current running


To Change Parallelism for data pump job which is current running

To find the running jobs

SQL> select owner_name, job_name from dba_datapump_jobs;

OWNER_NAME                 JOB_NAME
------------------------------------------------------------------------------
SYSTEM                     GG1


$ expdp attach=SYSTEM.GG1

Export: Release 11.2.0.3.0 - Production on Tue Oct 7 10:47:53 2014

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

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Job: GG1
  Owner: SYSTEM                        
  Operation: EXPORT                        
  Creator Privs: TRUE                          
  GUID: 04D26AD77698596AE0534F98340AEADE
  Start Time: Tuesday, 07 October, 2014 9:59:52
  Mode: TABLE                         
  Instance: orq738a2
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** parfile=exp.par        
     INCLUDE_METADATA      0
  State: EXECUTING                     
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /s01/oradata1/export/GG_07102014.dmp
    bytes written: 4,096
 
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: GG_LEARNING
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 101
  Completed Rows: 27,738
  Worker Parallelism: 1


To Change the Parallelism

Export> parallel=10

After changing the  Parallelism I could see that the value has changed.
To check the status after the existing table export is completed; So please wait for some more time.
 Export> status
Export> status

Job: GG1
  Operation: EXPORT                        
  Mode: TABLE                         
  State: EXECUTING                     
  Bytes Processed: 5,866,922,168
  Percent Done: 18
  Current Parallelism: 10
  Job Error Count: 0
  Dump File: /s01/oradata1/export/GG_07102014.dmp
    bytes written: 5,866,930,176
 
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: GG_STRUCTURES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 2
  Total Objects: 101
  Completed Rows: 18,758
  Worker Parallelism: 1
 
Worker 2 Status:
  Process Name: DW01
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: SYN_GG_SCHEMES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 101
  Worker Parallelism: 1
 
Worker 3 Status:
  Process Name: DW00
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: GG_AUTHORS
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 101
  Worker Parallelism: 1
 
Worker 4 Status:
  Process Name: DW03
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: GG_ABSTRACTS
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 101
  Worker Parallelism: 1
 
Worker 5 Status:
  Process Name: DW01
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: GG_DDL_NEWS_SUBSECTIONS
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 101
  Worker Parallelism: 1
 
Worker 6 Status:
  Process Name: DW05
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: GG_TEXTS
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 101
  Worker Parallelism: 1
 
Worker 7 Status:
  Process Name: DW02
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: GG_DDN_NEWS
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 101
  Worker Parallelism: 1
 
Worker 8 Status:
  Process Name: DW07
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: GG_SEQUENCES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 101
  Worker Parallelism: 1
 
Worker 9 Status:
  Process Name: DW03
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: GG_REF_REFERENCES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 101
  Worker Parallelism: 1
 
Worker 10 Status:
  Process Name: DW09
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: GG_STRUCTURES
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 101
  Worker Parallelism: 1

Export>

To See the status of running job

$ expdp attach=SYSTEM.GG1

Export: Release 11.2.0.3.0 - Production on Tue Oct 7 14:26:04 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Job: GG1
  Owner: SYSTEM                        
  Operation: EXPORT                         
  Creator Privs: TRUE                          
  GUID: 04D26AD77698596AE0534F98340AEADE
  Start Time: Tuesday, 07 October, 2014 9:59:52
  Mode: TABLE                         
  Instance: orq738a2
  Max Parallelism: 10
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** parfile=exp.par        
     INCLUDE_METADATA      0
  State: EXECUTING                     
  Bytes Processed: 0
  Current Parallelism: 10
  Job Error Count: 0
  Dump File: /s01/oradata1/export/GG_07102014.dmp
    bytes written: 4,096
 
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                     
  Object Schema: ORAGGS
  Object Name: GG_LEARNING
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 101
  Completed Rows: 328,461
  Worker Parallelism: 1

Export>

To See the Command prompt

select owner_name,JOB_NAME,OPERATION,JOB_MODE,STATE,DEGREE from dba_datapump_jobs;

SQL> desc dba_datapump_jobs
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER_NAME                                         VARCHAR2(30)
 JOB_NAME                                           VARCHAR2(30)
 OPERATION                                          VARCHAR2(90)
 JOB_MODE                                           VARCHAR2(90)
 STATE                                              VARCHAR2(30)
 DEGREE                                             NUMBER
 ATTACHED_SESSIONS                                  NUMBER

 DATAPUMP_SESSIONS                                  NUMBER
Posted by at

7 comments:

  1. UnknownApril 14, 2017 at 12:23 PM

    thanks for tip -- helpful knowledge

    ReplyDelete
  2. UnknownNovember 27, 2017 at 9:17 PM

    i have done practically in my laptop and worked as expected.thanks for sharing the knowledge.

    ReplyDelete
  3. EricMay 2, 2018 at 3:34 AM

    Thanks for the knowledge share. I am using the tips to monitor my export jobs.

    ReplyDelete
  4. NishantMarch 13, 2019 at 12:59 PM

    Hi - Even though we are changing the parallelism to any number , the job does not pick up the parallel , because worker parallelism remains the same ,Do you know how can we change worker parallelism ?
    You can see below I changed the parallelism to 16 but still in the insert query the parallel is 1 .

    Import> status

    Job: SYS_IMPORT_FULL_01
    Operation: IMPORT
    Mode: FULL
    State: EXECUTING
    Bytes Processed: 0
    Current Parallelism: 16
    Job Error Count: 0
    Dump File: /u01/app/oracle/dba_folders/nishant/INC8247370/expdp_SLMSDATA_SLMSPRD1.dmp

    Worker 1 Status:
    Process Name: DW00
    State: EXECUTING
    Object Schema: SLMSDATA
    Object Name: CENTRALRECEIVING
    Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
    Completed Objects: 1
    Total Objects: 13
    Completed Rows: 5,105,071
    Completed Bytes: 3,353,843,944
    Percent Done: 45
    Worker Parallelism: 1
    INSERT /*+ APPEND PARALLEL("CENTRALRECEIVING",1)+*/ INTO RELATIONAL("SLMSDATA"."CENTRALRECEIVING" NOT XMLTYPE) ("ORIGREC", "ORDERFIL
    LERNUMBER", "BILLABLE", "BIRTH_DATE", "CASENUMBER", "CNSOTHER", "CREATEDBY", "DATAENTRYSTATUS", "DATE_RECEIVED", "DISPDATAENTRYSTATU
    S", "DISPSTS", "EXTERNAL_ID", "FEVERMAXTEMP", "FIRST_NAME", "FOLDERNO", "HISCOLLECTED", "HISSTATUS", "INSURER", "LAST_NAME", "LOCATI
    ON_FOREIGN", "LOCATION_USA", "MIDDLE_INITIAL", "MISCSIGNOTHER", "MOTHER_MAIDEN", "NAMINGCONVENTION", "ORIGSTS", "OWNER_ADDRESS", "OW
    NER_CITY", "OWNER_COUNTY", "OWNER_STATE", "OWNER_ZIP", "PATIENT_PHONE", "PATIENTRACE", "PID", "RASCLIENTID", "RASPROJECTNO", "SEX",
    "SIGNS_CH", "SIGNS_FE", "SP_CODE", "STATEOFILLNESSOTHER", "STATUS", "SUBMITTED_ON", "SUSPSOURCE", "USRNAM", "PRINTFLAG", "OWNER_COUN
    TRY", "SSN", "HOSPITALID", "PATIENT_AGE", "PATIENT_AGEUNITS", "DATE_COLLECTED", "CLINICAL_HISTORY", "CLINICAL_HISTORY_FLAG", "SPEC_S
    OURCE", "ORIGINATORID", "DEPT", "PANEL_LIST", "PATIENT_MOTHER", "PATIENT_MOT

    ReplyDelete
    Replies
    1. SatheeshMay 3, 2019 at 1:44 PM

      After changing need to STOP_JOB and START_JOB then CONTINUE_CLIENT this will reflect in Parallelism

      Delete
    2. UnknownDecember 15, 2019 at 3:06 AM

      after stop_job and start_job with continue_client also not picking up the parallel

      Import> start_job

      Import> CONTINUE_CLIENT
      Job SYS_IMPORT_SCHEMA_01 has been reopened at Sun Dec 15 03:56:27 2019
      Restarting "SYS"."SYS_IMPORT_SCH


      Import> status

      Job: SYS_IMPORT_SCHEMA_01
      Operation: IMPORT
      Mode: SCHEMA
      State: EXECUTING
      Bytes Processed: 52,114,316,400
      Percent Done: 82
      Current Parallelism: 8
      Job Error Count: 0
      Dump File: /tam_dba/CANON_TAM_DBA/HYPPRD/HYPPRD_before_DR.dmp

      Worker 1 Status:
      Instance ID: 1
      Instance name: HYPPRD_1
      Host name: plprdodb101
      Process Name: DW00
      State: EXECUTING
      Object Schema: HYP_FDMEE
      Object Name: SNP_POP
      Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
      Completed Objects: 6
      Total Objects: 419
      Completed Bytes: 24,208
      Worker Parallelism: 1

      Delete
  5. KarikalvalavanOctober 28, 2019 at 6:24 PM

    kukugaga nagakaka guka gukum

    ReplyDelete

Subscribe to: Post Comments (Atom)