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

2 comments:

  1. thanks for tip -- helpful knowledge

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

    ReplyDelete